This article will explain in the most basic terms how you can use the JQuery API to insert form values to a MySQL database using no form actions, just JQuery. Pretty cool stuff.
I wanted to create a CSS/Ajax powered database management system and the code you see here is the very streamlined version of this effort.
Download all the files that I refer to in this tutorial.
Let’s begin.
You will need a copy of jQuery running on your server in order for this tutorial to work. Check the link above.
You will need to create a MySQL database called ‘name’ first then use the SQL below to create the table called ‘names’ using the provided SQL:
DROP TABLE IF EXISTS `name`.`names`;
CREATE TABLE `name`.`names` (
`nameID` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) NOT NULL,
`ts` varchar(45) NOT NULL,
PRIMARY KEY (`nameID`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;
Next, create a PHP file to connect to your new database. NOTICE: This has not been proven to be safe from SQL injection.
ndb.php
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_ndb = "localhost";
$database_ndb = "name";
$username_ndb = "root";
$password_ndb = "";
$ndb = mysql_pconnect($hostname_ndb, $username_ndb, $password_ndb) or trigger_error(mysql_error(),E_USER_ERROR);
?>
To start off, we create a class which will define the properties and the method of the ‘name’ class. In this case, the method simply echos the users name and the timestamp when the class is invoked.
class.name.php
<?php
class name {
public $name;
public $ts;
public function showName(){
echo " my name is: $this->name, and I like it" . "<br>";
echo " and I added my name at this time: $this->ts";
}
}
?>
Instead of using $name, we use $this->name within the method. This is how properties are passed through a method when using PHP classes. This starts to make sense very shortly, I promise.
Next, create a PHP 5 insert class:
class.db.php
<?php
class db {
public $insertSQL;
public function insert() {
include_once("../Connections/ndb.php");
mysql_select_db($database_ndb, $ndb);
$query_rs = $this->insertSQL;
$rs = mysql_query($query_rs, $ndb) or die(mysql_error());
}
}
?>
The purpose of this class is to grab the SQL from the implementation PHP file – which I will show you next. Call me crazy but I usually save an implementation file with a prefix of ‘i’.
i.Name.php
<?
include_once('../classes/class.name.php');
include_once('../classes/class.db.php');
$name = $_POST['name'];
$ts = $_POST['ts'];
$upName = $_POST['upName'];
$ts2 = $_POST['ts2'];
$insertDB = new db;
$insertDB->insertSQL = "INSERT INTO names VALUES ('', '$name', '$ts')";
$insertDB->insert();
$myName = new name;
$myName->name = $name;
$myName->ts = $ts;
echo $myName->showName();
?>
What this implementation file does is rather simple. It grabs the values from the JQuery script, instantiates the object from the name class and defines the values for the SQL. It then does the most important thing of all. It echos the results back which then gets picked up by the JQuery script below. See if you can find out how it does it:
jgo.php
$(document).ready(function(){
//add a name
$("a.sendName").click(function(){
var name = $("#name").val(); // getting values from the ID --id="message" -- name of the form fields
var ts = $("#ts").val();
$.post("implement/i.Name.php", { name: name, ts: ts }, //this is JSON that gets posted
function(data){
$("#loadName").html(data).slideDown("slow"); // send this data from the PHP echo into the #screen DIV
$("#update").fadeIn("slow");
});
});
}); //end DOC
The JQuery’s job here is to collect the variables from the HTML markup. Why we do not need a form here on the markup is because JQuery ‘listens’ for the click event to fire on the “a.sendName” css class, once it does, it passes the markup values to this JQuery script. Gotta love that. It then assigns the values to a JSON string which then gets sent to the implementation PHP file “i.name.php”. The implementation file echos out the results. The “.html” method in the JQuery above parse the echo and displays this into the “#loadName” DIV on the markup. I have to say, I like this and you should to 
.
Here is the markup:
name.php
<!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" />
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/jgo.js"></script>
<title>Untitled Document</title>
</head>
<body>
What is your Name<br />
<input name="name" type="text" id="name" size="22" />
<input name="ts" id="ts" type="hidden" value="<?php echo date('l jS \of F Y h:i:s A');?>" /><br />
<a href="#" class="sendName">add my name</a>
<div id="loadName" style="display:none"></div><br />
</div>
</body>
</html>
The code and the article was written by Dana Martinelli
Popularity: 8% [?]
This is a very useful tutorial, but you really should cleanse the data collected from the post in the php. The client can never be trusted, and people who see this code might use it in a production system without knowing that they are vulnerable.
Thanks for this very simple tutorial. The best tutorial for ajax insert data.