insert using query ajax

petewinter
petewinter used Ask the Experts™
on
Can you please have a look at my code attached.

I am trying to use jquery and AJAX to insert content into a database.

It works in firefox, but it must be wrong as the page reloads and I can see the url parameter which is incorrect.

Can you please let me what I have done wrong? Please send me back the corrected code. Thanks
<?php require_once('../Connections/conn_cwes.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

if(isset($_POST["add"])) {
  $insertSQL = sprintf("INSERT INTO products2 (product_name) VALUES (%s)",
                       GetSQLValueString($_POST["product_name"], "text"));  
  mysql_select_db($database_conn_cwes, $conn_cwes);
  $Result1 = mysql_query($insertSQL, $conn_cwes) or die(mysql_error());
}

mysql_select_db($database_conn_cwes, $conn_cwes);
$query_rs_products = "SELECT * FROM products2 ORDER BY product_name ASC";
$rs_products = mysql_query($query_rs_products, $conn_cwes) or die(mysql_error());
$row_rs_products = mysql_fetch_assoc($rs_products);
$totalRows_rs_products = mysql_num_rows($rs_products);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>CWE Solution - Product Selector</title>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>

<script type="text/javascript">

//Add new post
$(document).ready(function() {
	var product_name = $("#product_name");
	$("#Form1").submit(function(){
			$.ajax({
				type: "POST",
				url: 'change.php',
				data: "product_name="+product_name.val()+"&add=true",
			});
	})
})

</script>

</head>

<body>


<h1>Printer Selector</h1>


<?php do { ?>
<div class="record" id="record-<?php echo $row_rs_products['id']; ?>"><?php echo $row_rs_products['product_name']; ?></div>
<?php } while ($row_rs_products = mysql_fetch_assoc($rs_products)); ?>

<br /><br />

<form id="Form1">
  Product Name:
  <input type="text" name="product_name" id="product_name" />
  <input name="submit" type="submit" value="add">
</form>

</body>
</html>
<?php

mysql_free_result($rs_products);

?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
replace line 61 :       $("#Form1").submit(function(){
by :       $("#Form1").submit(function(e){ e.preventDefault();

OR

replace line 67 :       })
by : return false;       })

ressources :
http://api.jquery.com/event.preventDefault/
http://api.jquery.com/submit/

Author

Commented:
Thanks leakim971, but that has stopped the insert into the database working?
leakim971Multitechnician
Top Expert 2014

Commented:
sure? check your database, not the current page
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yep. See the page: http://www.cwesolutions.co.uk/printers/add.php

What you enter should appear in the list? Any idea's what I am doing wrong?
Top Expert 2011

Commented:
When you submit the form, you are running an AJAX post, which is fine, but you are not breaking out of the submit process, so you, essentially, are submitting the information twice - once with the AJAX call and then continuing the form submit process which is "get" (which places the params into the URL).

Since this is all happening independent of user interaction, your shouldn't even need a form submit... just run the AJAX.  You probably want to add a "success:" clause to your AJAX to tell the page what to do after the information is submitted... perhaps a success message for the user?

$(document).ready(function() {
	var product_name = $("#product_name");

			$.ajax({
				type: "POST",
				url: 'change.php',
				data: "product_name="+product_name.val()+"&add=true",
                                success: function() {  
                                     $('#itWorked').html("<div id='message'></div>");  
                                     $('#message').html("<h2>Data Submitted!</h2>");  
                                 });  
			});
                        return false;
});

Open in new window

Author

Commented:
Actually sorry your right:

This line was incorrect:

url: 'add.php',


Any idea's why the list is not updating?
Top Expert 2011
Commented:
disregard the comment about not needing the submit... I misinterpreted - thought you were running it automatically on page load.

Just abort the actual form submission process by adding the return false after the .ajax function and you should be good. (leakim's "preventDefault" suggestion should work too)
leakim971Multitechnician
Top Expert 2014
Commented:
>Yep. See the page: http://www.cwesolutions.co.uk/printers/add.php
>What you enter should appear in the list? Any idea's what I am doing wrong?

It work, if you refresh the page, you will see a new line (true)
As I said, check your database, not the current page.

You should separate the script loading the page and the script doing things in the database.
Else you're a bit away the ajax logic. Currently you want to reload the current page update instead update the DIV with the new data.

Author

Commented:
Thanks. I understand now
leakim971Multitechnician
Top Expert 2014

Commented:
>Any idea's why the list is not updating?

the list is not updating because you don't update it. your previous script reload the page. Ajax let you do thing WITHOUT reloading the page.
so the first thing is to stop the reloading of the page. two ways (my firts POST) :
1) we add e.preventDefault at the beginning of the submit event
or
2) we add return false; at the end

I prefer the first one. It's safe do do what we want ASAP.

now you want UPDATE the list, the body of your page. If you follow the Ajax logic and create a second script (or split your script with an if/else to have two scripts in one) you return an error code OR if everything is alright the data inserted in the database. Once the ajax callback function receive the data inserted you append the body (or a div) $("body").append(dataInserted).

That's the way you should use Ajax IMHO.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial