SIMPLE AJAX EXAMPLES

In this article I don't want to show you the history of AJAX and discuss its pros and cons, but only focus on how to create a basic working AJAX - PHP communication.

The only important thing at the moment is that AJAX uses JavaScript so it need to be enabled in your browser to successfully complete this tutorial.

To demonstrate the AJAX PHP connection we will create a very simple form with 2 input fields. In the first field you can type any text and we will send this text to our PHP script which will convert it to uppercase and sends it back to us. At the end we will put the result into the second input field. ( The example maybe not very useful but I think it is acceptable at this level. )

So let's list what we need to do:

* Listen on key-press event on the input field.
* In case of key-press send a message to the PHP script on the server.
* Process the input with PHP and send back the result.
* Capture the returning data and display it.

Our html code is very simple it looks like this:

Code:

1.
2.
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3.

4.

5.

6.

7.

8.

9.

10.

11.

12.
Input text:
13.
Output text:
14.

15.

16.


Javascript F1

As you can see there is a doWork() function which is called in every case when a key is up (a key was pressed). Of course you can use any other supported events if you want.

But what is this doWork() and how we can send messages to the server script? On the next page you will find the answers.

Step 2 - Sending data to PHP with Ajax

Ajax PHP tutorial

Before the explanation of the doWork() function we first need to learn a more important thing. To make a communication between the client and the server the client code needs to create a so called XMLHttpRequest object. This object will be responsible for AJAX PHP communication.

However creating this object is bit triky as the browser implement it various ways. If you don't want to support the quite old browsers we can do it as follows:

Code:

1.
// Get the HTTP Object
2.
function getHTTPObject(){
3.
if (window.ActiveXObject)
4.
return new ActiveXObject("Microsoft.XMLHTTP");
5.
else if (window.XMLHttpRequest)
6.
return new XMLHttpRequest();
7.
else {
8.
alert("Your browser does not support AJAX.");
9.
return null;
10.
}
11.
}

Javascript F1

Ok, now we have the XMLHttpRequest object, so it's time to implement the business logic inside the doWork() function.

First of all we need to get a valid XMLHttpRequest object. If we have it then we can send the value of the inputText field to the server script. We do this by composing an URL with parameter, so in the PHP script we can use the $_GET super-global array to catch the data. As next step we call the send() function of the XMLHttpRequest object which will send our request to the server. At the moment our doWork() function looks like this:

Code:

1.
// Implement business logic
2.
function doWork(){
3.
httpObject = getHTTPObject();
4.
if (httpObject != null) {
5.
httpObject.open("GET", "upperCase.php?inputText="
6.
+document.getElementById('inputText').value, true);
7.
httpObject.send(null);
8.

9.
}
10.
}

Javascript F1

It's nice but how we can catch the response from the server? To do this we need to use an other special property of the XMLHttpRequest object. We can assign a function to this parameter and this function will be called if the state of the object was changed. The final code is the following:

Code:

1.
// Implement business logic
2.
function doWork(){
3.
httpObject = getHTTPObject();
4.
if (httpObject != null) {
5.
httpObject.open("GET", "upperCase.php?inputText="
6.
+document.getElementById('inputText').value, true);
7.
httpObject.send(null);
8.
httpObject.onreadystatechange = setOutput;
9.
}
10.
}

Javascript F1

The last step on client side is to implement the setOutput() function which will change the value of our second field. The only interesting thing in this function that we need to check the actual state of the XMLHttpRequest object. We need to change the field value only if the state is complete. The readyState property can have the following values:

* 0 = uninitialized
* 1 = loading
* 2 = loaded
* 3 = interactive
* 4 = complete

So the setOutput() looks like this:

Code:

1.
// Change the value of the outputText field
2.
function setOutput(){
3.
if(httpObject.readyState == 4){
4.
document.getElementById('outputText').value
5.
= httpObject.responseText;
6.
}
7.

8.
}

Javascript F1

Now the client side is ready let's implement the server side.

Step 3 - PHP code and the complete AJAX example

Ajax PHP tutorial


Implementing the server side functionality is very simple compared to the client side. In the PHP code we just need to check the $_GET super-global array. Afterwards convert it to uppercase and echo the result. So the PHP code is this:

Code:

1.
2.
if (isset($_GET['inputText']))
3.
echo strtoupper($_GET['inputText']);
4.
?>

Javascript F1

That's really short, isn't it?

At least you can find the complete client and server code below.

Client:

Code:

1.
2.
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3.

4.

5.

6.

7.

8.

9.

10.

11.

46.

47.

48.
Input text:
49.
Output text:
50.

51.

52.


Javascript F1

Server:

Code:

1.
2.
if (isset($_GET['inputText']))
3.
echo strtoupper($_GET['inputText']);
4.
?>

Read more...

AJAX EXAMPLES

Example 1: Ajax with innerHTML

For a simple innerHTML-based Ajax example, we’ll create a quasi-functional address book application. We’ll start with the XHTML page (line wraps marked » —Ed.):

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
xml:lang="en" lang="en">

Simple Ajax Address Book

Please Choose a Person

Choose Someone

Bob Smith
Janet Jones

value="Get the Address" />

As you can see, we have a simple form with a select, from which to choose a person. Again, we are providing a fallback action for the form, in case our JavaScript cannot run. Below the form, we have a simple pre element that will be displaying the address information from the database.

And now for the JavaScript. Basically, we will be commandeering the select and using its onchange event handler to trigger an XMLHttpRequest() call to obtain the address information for the selected individual. The server will be returning this information as a string like this:

Bob Smith
123 School Street
Anytown, NY 12345

We will take this return as a string and dump it into the pre element using innerHTML. Take a look at the code (line wraps marked » —Ed.):

var addressBook = {
myConn: false, // the XMLHttpRequest
body: false, // the body element
target: false, // the target container
loader: false, // the loader
init: function(controlId, sbmtBtnId, targetId){
/* init() takes three arguments:
* the id of the controller (select)
* the id of the submit button
* the id of the target container */
// test for methods & elements
if(!document.getElementById ||
!document.getElementsByTagName ||
!document.getElementById(controlId) ||
!document.getElementById(sbmtBtnId) ||
!document.getElementById(targetId)) return;
// set and test XHConn, quitting silently if it fails
addressBook.myConn = new XHConn();
if(!addressBook.myConn) return;
// get the body
addressBook.body = document.getElementsByTagName('body')[0];
// get the controller
var control = document.getElementById(controlId);
// get the submit button
var sbmtBtn = document.getElementById(sbmtBtnId);
// remove the submit button
sbmtBtn.parentNode.removeChild(sbmtBtn);
// get the target
addressBook.target = document.getElementById(targetId);
// add the onchange event to the controller,
addressBook.addEvent(control,
'change',
function(){
if(this.value != ''){
/* if there's a value,
trigger getAddress */
addressBook.getAddress(this.value);
} else {
// otherwise empty the target
addressBook.target.innerHTML = '';
}
});
},
getAddress: function(id){ // the Ajax call
// let's let the user know something is happening (see below)
addressBook.buildLoader();
/* this is the function that is run
once the Ajax call completes */
var fnWhenDone = function(oXML) {
// get rid of the loader
addressBook.killLoader();
// insert the returned address information into the target
addressBook.target.innerHTML = oXML.responseText;
};
// use XHConn's connect method
addressBook.myConn.connect('index.php', 'POST',
'id='+id, fnWhenDone);
},
buildLoader: function(){ // builds a loader
// create a new div
addressBook.loader = document.createElement('div');
// give it some style
addressBook.loader.style.position = 'absolute';
addressBook.loader.style.top = '50%';
addressBook.loader.style.left = '50%';
addressBook.loader.style.width = '300px';
addressBook.loader.style.lineHeight = '100px';
addressBook.loader.style.margin = '-50px 0 0 - 150px';
addressBook.loader.style.textAlign = 'center';
addressBook.loader.style.border = '1px solid #870108';
addressBook.loader.style.background = '#fff';
// give it some text
addressBook.loader.appendChild( »
document.createTextNode( »
'Loading Data, please wait\u2026'));
// append it to the body
addressBook.body.appendChild(addressBook.loader);
},
killLoader: function(){ // kills the loader
// remove the loader form the body
addressBook.body.removeChild(addressBook.loader);
},
addEvent: function(obj, type, fn){ // the add event function
if (obj.addEventListener) »
obj.addEventListener(type, fn, false);
else if (obj.attachEvent) {
obj["e"+type+fn] = fn;
obj[type+fn] = function() {
obj["e"+type+fn](window.event);
};
obj.attachEvent("on"+type, obj[type+fn]);
}
}
};
/* run the init() method on page load, passing it
the required arguments */
addressBook.addEvent(window, 'load',
function(){
addressBook.init('person',
'submit',
'address');
});

See this script in action.
Example 2: Ajax with Nodes

Let’s alter the example, and instead of returning a string from the server, this time, make it XML:



Bob
Smith

123 School Street
Anytown
NY

12345

The XHTML page remains the same, but we need to make some minor adjustments to the JavaScript. To highlight the differences, I will touch on each change individually.

The first change, to the onchange event handler of the select, is pretty simple (line wraps marked » —Ed.):

…
addressBook.addEvent(addressBook.control,
'change',
function(){
if(this.value != ''){
addressBook.getAddress(this.value);
} else {
addressBook.target.removeChild( »
addressBook.target.firstChild);
}
});
…

Instead of setting the content of the target to empty using innerHTML, the DOM is removing the node that is the target’s first child.

Next up is the getAddress() method (line wraps marked » —Ed.):

…
getAddress: function(id){
addressBook.buildLoader();
var fnWhenDone = function(oXML) {
addressBook.killLoader();
if(addressBook.target.hasChildNodes()){
addressBook.target.removeChild( »
addressBook.target.firstChild);
}
xml = oXML.responseXML;
var name = addressBook.getNodeValue(xml, 'first')+' '+
addressBook.getNodeValue(xml, 'last');
var address = addressBook.getNodeValue(xml, 'street');
var csz = addressBook.getNodeValue(xml, 'city')+', '+
addressBook.getNodeValue(xml, 'state')+' '+
addressBook.getNodeValue(xml, 'zip');
var txt = document.createTextNode(name + "\ n" +
address + "\n" + csz);
addressBook.target.appendChild(txt);
};
addressBook.myConn.connect('getAddress.php', 'POST',
'id=' + id, fnWhenDone);
},
…

As we are working with XML, we can use the responseXML property to get the return from the server as a node tree. Then we can traverse that tree, collecting the tidbits of information we need. In this example, we added a new method (getNodeValue()) that makes working with XML returns easier:

…
getNodeValue: function(tree, el){
return tree.getElementsByTagName(el)[0].firstChild.nodeValue;
},
…

This method takes two arguments: the node tree (tree) and the element (el) whose content is wanted. It returns the nodeValue of the firstChild of the first el within tree or, in other words, the text value of the node requested from the node tree.

Once we have collected all of the requested contents from the XML, the text string is rebuilt and generated with the DOM before being appended to the target. The end result can be seen here.

Read more...

MYSQL AND PHP

Connecting to MySQL.

Before we can do anything with MySQL we need to be able to connect to the server. Here we will demonstrate 3 ways of doing this with PHP mysql, mysqli and PDO. First with the standard mysql connection.

Connecting with mysql.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully';

/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


Before we move on with the other extensions, lets look briefly at what has happened above. We have begun by setting some variables for our database, these are the hostname, username, and password. The next line of code shows the use of mysql_connect() function to connect to the MySQL server. Lets look at this line further.

$link = @mysql_connect($hostname, $username, $password);

We have created a mysql link resource variable called $link. We see also here the use of the @ symbol to suppress and errors that may arise from a failure to connect, more on this later..., After we use mysql_connect() we check with the is_resource() function that the link variable is indeed a valid resource, and if so, we can continue with our code, else, an error is printed saying Unable to connect. If the connection is valid, then a message is printed to say so, and the database link is closed with the mysql_close() function. The database connection should cease at the end of the script, but with longer running scripts this may be a waste of valuable resources, so we free up some room with mysql_close(). Now, lets see the same thing with mysqli..

Connecting with mysqli.

This could simply be done in the same manner as mysql_connect(), but mysqli offers us an Object Oriented (OO) interface to MySQL so it would be futile to do things "the old way". Lets see how we go.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** create a new mysqli object ***/
$mysqli = @new mysqli($hostname, $username, $password);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully';

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Here we see the creation of a new mysqli object which passes the hostname, username, and password to the constructor which makes the connection for us. We can then use this new object for other functionality that we will soon use. We have also suppressed error_reporting with the @ symbol once more, this is because a warning will be produced if the connection fails. We have also tested for an error condition (more on errors later) to see if the connection is valid, and if so, it tells us. Now, lets move onto PDO.

Connecting with PDO

PHP has spawned an amazingly neat idea to standardize database connections. This is PDO (Php Data Objects). A full Introduction to PDO is available and is well worth reading. Here we will use some of the concepts from this article to use with mysql. So, lets get connected



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpass';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database';
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


In the above code, several variations have taken place. Like the other connections we have supplied a hostname, username, and password. PDO demands that we also have a valid database to connect to. If no database is specified an exception is thrown. In the example above we have used the default mysql database to connect to. The database named mysql contains the tables of users, databases and other vital information. YOU HAVE BEEN WARNED! We use the catch{ } block to catch any thrown exceptions from the try{ } block. Another variation is that we have used root as the username.
Is this a bad idea?
In most circumstances yes, however, here we wish to demonstrate some administrative tasks such as creating users and creating databases. Most MySQL servers, by default, will not allow this to be done by a user other than the root super user. In the following section we will be creating a database, so root access is required.

Creating a Database.

Now we have seen how to connect to a database server, it is just a small step to create a database. You will need to be a user with CREATE privileges to do this. Usually this will be the user root, so thats what we will be using here. Once again we will use the three different PHP mysql extenstions to do this, beginning with the mysql extenstion.

Create with mysql.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** sql to create a database ***/
$sql = 'CREATE DATABASE periodic_table';

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
echo 'Database created successfully
';
}
else
{
echo 'Unable to create database:
' . $sql .'
' . mysql_error();
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


In the above code the we have used the function mysql_query() to run the SQL statement
CREATE DATABASE periodic_table
You may ask why we created the variable $sql when it would have been just as easy, and used less code to embed the sql query as an arguement for the mysql_query() function like this:


mysql_query("CREATE DATABASE periodic_table")


We created the variable $sql for possible debugging purposes. Should we encounter an error within our sql statement, the error checking will print a message, including the sql statement. Eg: should we run this script twice, we would see an error something like this..

Connected successfully
Unable to create database:
CREATE DATABASE periodic_table
Cannot create database periodic_table; database exists

Create with mysqli.

The mysqli connection provides a smooth object oriented approach for same functionality. Using the connection code from the earlier connect script we can simply use the created mysqli object to run queries.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

/*** create a new mysqli object ***/
$mysqli = @new mysqli($hostname, $username, $password);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** our sql statement ***/
$sql = 'CREATE DATABASE periodic_table';

if($mysqli->query($sql) === TRUE)
{
echo 'Database created successfully
';
}
else
{
echo $sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Should we run this code twice, we would again get an get an error like the following

Connected Successfully
CREATE DATABASE periodic_table
Cannotcreate database periodic_table; database exists

You can see from the message, as in the previous example, we have a message to tell us that we have connected successfully, then the SQL query used followed by a message from the database itself telling us the exact nature of the error.

Create with PDO

Not to be out-done in the Object Oriented approach, PDO makes the code base somewhat cleaner. Using the code from the initial connection we can use the provided DSN object for our query.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** our sql statement ***/
$sql = 'CREATE DATABASE periodic_table';

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** echo a message to say the database was created ***/
echo 'Database created successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


As you see, PDO provides a nice exception class to handle any problems that may arise in our database queries. If an exception is thrown within the try{ } block, no further code within the try{ } block is executed and the flows directly to the first catch(){ } block. In the catch block above we echo the SQL statement and any error message that may be generated by the database.

This query is not one you will be running often but is needed in order to show how to create database tables. Below is the SQL statement needed to create the a table and fields within your newly created database.

Creating a MySQL user

Now a database has been created, a user other than the root user needs to be able to work with it. This means a MySQL user, not a system user, needs to be created and given the appropriate permissions to be able to work with data. We will create a user with all permissions on the periodic_table database. If you want real security, you can limit the permissions a user has to just SELECT, thus limitting possible injection attacks. The topic of PHP/MySQL security will be covered in another article. For now, we will GRANT ALL privileges to the new user, except GRANT. Once again we need to have root access to mysql to do this. The code itself should look very familiar if you have read the previous section as it is almost identical. The same principles and connections apply, the only thing that changes is the SQL statement itself.

Create a user with mysql.

As mentioned, this is identical to creating a database, the only change is the SQL statement, we have reproduced the code here for the benifit of familiarity.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** sql to create a user ***/
$sql = "GRANT ALL ON periodic_table.* TO username@localhost IDENTIFIED BY 'password'";

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
echo 'New user created successfully
';
}
else
{
echo 'Unable to create user:
' . $sql .'
' . mysql_error();
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


Create a user with mysqli.

Here also there are no changes except for the SQL. All the code remains as it was to create a database.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

/*** create a new mysqli object ***/
$mysqli = @new mysqli($hostname, $username, $password);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** sql to create a user ***/
$sql = "GRANT ALL ON periodic_table.* TO username@localhost IDENTIFIED BY 'password'";

if($mysqli->query($sql) === TRUE)
{
echo 'New user created successfully
';
}
else
{
echo $sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Create a user with PDO

And you guessed it, no changes here either from the CREATE DATABASE example above. Everything remains the same except for the SQL statement itself, the new user is created, and if there is a problem an exception is thrown.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** sql to create a user ***/
$sql = "GRANT ALL ON periodic_table.* TO username@localhost IDENTIFIED BY 'password'";

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** echo a message to say the database was created ***/
echo 'Database created successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


Creating a Table.

Now that a database has been created, and a database user other than root exists, we are able to connect to the database with our shiny new username and password. A database it really needs some data in it for it to be functional. For the purposes of this tutorial we are using the periodic table of elements, (we can all recite those from school, right?) so in our periodic_table database an appropriate table name would be elements. Lets first look at the SQL need to create such a table.


CREATE TABLE elements (
atomicnumber tinyint(3) NOT NULL default '0',
latin varchar(20) NOT NULL default '',
english varchar(20) NOT NULL default '',
abbr char(3) NOT NULL default '',
PRIMARY KEY (atomicnumber)
)

Once again we can use code similar to that which we have already used to create a database and to create a new MySQL user. The same process is repeated here with the difference that we no longer need root access to be able to interface with our new database. The user "username" can be used and this is recommended practice. Never use root if you do not need it. Our new user has all the privileges needed to carry out any interactions with the database in this tutorial. With that in mind, lets get to table creation, first with the mysql extension functions.

Create table with mysql.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** select the database we wish to use ***/
if(mysql_select_db("periodic_table", $link) === TRUE)
{
/*** sql to create a new table ***/
$sql = "CREATE TABLE elements (
atomicnumber tinyint(3) NOT NULL default '0',
latin varchar(20) NOT NULL default '',
english varchar(20) NOT NULL default '',
abbr char(3) NOT NULL default '',
PRIMARY KEY (atomicnumber)
)";

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
echo 'New table created successfully
';
}
else
{
echo 'Unable to create table:
' . $sql .'
' . mysql_error();
}
}
/*** if we are unable to select the database show an error ***/
else
{
echo 'Unable to select database';
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


Lets go over what we have done here. We have used the CREATE TABLE statement to create a table called elements
Following that we have 4 fields that will contain the data itself. They are:
atomicnumber
latin
english
abbr
These fields will contain different data types and so have different attributes. The atomicnumber field has a type of tinyint or tiny interger and its maximum length is 3 with a default value of zero. More on types can be found in the MySQL documentation and is recommended reading.

Creating a Table with mysqli.

As with the script above, we no longer need to use the root username and password for creating our table. An extra parameter is added to our class instantiation that contains the name of the default database to use for our script. We assign this to a variable at the top of our script with the other variables.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** sql to create a new table ***/
$sql = "CREATE TABLE elements (
atomicnumber tinyint(3) NOT NULL default '0',
latin varchar(20) NOT NULL default '',
english varchar(20) NOT NULL default '',
abbr char(3) NOT NULL default '',
PRIMARY KEY (atomicnumber)
)";


if($mysqli->query($sql) === TRUE)
{
echo 'New table created successfully
';
}
else
{
echo $sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Creating a Table with PDO

The use of PDO will significantly cut down our code. Much of the savings comes from the use of exceptions rather than constantly checking for error conditions. As with the previous table creation examples we are now using a non-root username/password pair. We have also created a new variable called $dbname. This variable holds the name of the database we wish to connect to and is used when we instantiate a new PDO object like this:


$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
?>


With this in mind, our table creation script using PDO is as follows.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "CREATE TABLE elements (
atomicnumber tinyint(3) NOT NULL default '0',
latin varchar(20) NOT NULL default '',
english varchar(20) NOT NULL default '',
abbr char(3) NOT NULL default '',
PRIMARY KEY (atomicnumber)
)";

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** echo a message to say the database was created ***/
echo 'Table created successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


You should now have a database called "periodic" with a table named periodic within it. This table is has 4 fields and it is these that will contain the real data we wish to use. First we must INSERT some data into the tables.

INSERT data into MySQL

This will likely be the second most frequent task done with the database. All the data in the database needs to come from somewhere and it is done using the INSERT statement. The process is similar to what we have been doing with other SQL statements and should be familiar by now, so lets get to it...

INSERT data with mysql.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** select the database we wish to use ***/
if(mysql_select_db("periodic_table", $link) === TRUE)
{
/*** sql to INSERT a new record ***/
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES ( 1, 'HYDROGENIUM', 'Hydrogen', 'H')";

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
echo 'New record created successfully
';
}
else
{
echo 'Unable to INSERT data:
' . $sql .'
' . mysql_error();
}
}
/*** if we are unable to select the database show an error ***/
else
{
echo 'Unable to select database';
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


Now you should be getting in the swing of sending SQL to the database. It goes much the same for mysqli and PDO. We connect to the database server, then select the appropriate database, and issue commands. Lets continue with our mysqli example.

INSERT data with mysqli



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** sql to INSERT a new record ***/
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES ( 1, 'HYDROGENIUM', 'Hydrogen', 'H')";


if($mysqli->query($sql) === TRUE)
{
echo 'New record created successfully
';
}
else
{
echo $sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Once again we have used the same code with just a different SQL statement. The process remains the same for commands that do not return a value or values.

INSERT data with PDO

Not to be out classed, PDO maintains a compact code base for perfoming the same task. Here we see the creation of the same record as above. Again we use the same script as creating the database and table. The only thing that changes is the SQL statement.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** sql to INSERT a new record ***/
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES ( 1, 'HYDROGENIUM', 'Hydrogen', 'H')";

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** echo a message to say the database was created ***/
echo 'Recored created successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


INSERT multiple records

In the previous section we can see how to INSERT a record into a database. This works fine for single records, but what if we have over one hundred records. The periodic table of elements holds one hundred and nine known elements. We need a method to INSERT all these records without needing one hundred and nine database queries. This would be a much to heavy drain on resources and time conusumin and time conusuming. The solution comes in several forms, depending on the extension you are using. We will step through each extension and see how the various INSERT methods compares. Lets get started with the mysql extention. MySQL provides a simple query method of handling multiple INSERTs.

INSERT multiple records with mysql



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** select the database we wish to use ***/
if(mysql_select_db("periodic_table", $link) === TRUE)
{
/*** sql to INSERT multiple new records ***/
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES
(1, 'HYDROGENIUM', 'Hydrogen', 'H'),
(2, 'HELIUM', 'Helium', 'He'),
(3, 'LITHIUM', 'Lithium', 'Li'),
(4, 'Beryllium', 'Beryllium', 'Be'),
(5, 'Borum', 'Boron', 'B'),
(6, 'Carboneum', 'Carbon', 'C'),
(7, 'Nitrogenium', 'Nitrogen', 'N'),
(8, 'Oxygenium', 'Oxygen', 'O'),
(9, 'Fluorum', 'Fluorine', 'F'),
(10, 'Neon', 'Neon', 'Ne'),
(11, 'Natrium', 'Sodium', 'Na'),
(12, 'Magnesium', 'Magnesium', 'Mg'),
(13, 'aluminium', 'Aluminum', 'Al'),
(14, 'Silicium', 'Silicon', 'Si'),
(15, 'Phosphorus', 'Phosphorus', 'P'),
(16, 'Sulphur', 'Sulphur', 'S'),
(17, 'Chlorum', 'Chlorine', 'Cl'),
(18, 'Argon', 'argon', 'Ar'),
(19, 'Kalium', 'Potassium', 'K'),
(20, 'Calcium', 'Calcium', 'Ca'),
(21, 'Scandium', 'Scandium', 'Sc'),
(22, 'Titanium', 'Titanium', 'Ti'),
(23, 'Vanadium', 'Vanadium', 'V'),
(24, 'Chromium', 'Chromium', 'Cr'),
(25, 'Manganum', 'Manganese', 'Mn'),
(26, 'Ferrum', 'Iron', 'Fe'),
(27, 'Cobaltum', 'Cobalt', 'Co'),
(28, 'Niccolum', 'Nickel', 'Ni'),
(29, 'Cuprum', 'Copper', 'Cu'),
(30, 'Zincum', 'Zinc', 'Zn'),
(31, 'Gallium', 'Gallium', 'Ga'),
(32, 'Germanium', 'Germanium', 'Ge'),
(33, 'Arsenicum', 'Arsenic', 'As'),
(34, 'Selenium', 'Selenium', 'Se'),
(35, 'Bromum', 'Bromine', 'Br'),
(36, 'Krypton', 'Krypton', 'Kr'),
(37, 'Rubidium', 'Rubidium', 'Rb'),
(38, 'Strontium', 'Strontium', 'Sr'),
(39, 'Yttrium', 'Yttrium', 'Y'),
(40, 'Zirkonium', 'Zirkonium', 'Zr'),
(41, 'Niobium', 'Niobium', 'Nb'),
(42, 'Molybdaenum', 'Molybdaenum', 'Mo'),
(43, 'Technetium', 'Technetium', 'Tc'),
(44, 'Ruthenium', 'Ruthenium', 'Ru'),
(45, 'Rhodium', 'Rhodium', 'Rh'),
(46, 'Palladium', 'Palladium', 'Pd'),
(47, 'Argentum', 'Silver', 'Ag'),
(48, 'Cadmium', 'Cadmium', 'Cd'),
(49, 'Indium', 'Indium', ' In'),
(50, 'Stannum', 'Tin', 'Sn'),
(51, 'Stibium', 'Antimony', 'Sb'),
(52, 'Tellurium', 'Tellurium', 'Te'),
(53, 'Iodum', 'Iodine', 'I'),
(54, 'Xenon', 'Xenon', 'Xe'),
(55, 'Caesium', 'Cesium', 'Cs'),
(56, 'Baryum', 'Barium', 'Ba'),
(57, 'Lanthanum', 'Lanthanum', 'La'),
(58, 'Cerium', 'Cerium', 'Ce'),
(59, 'Praseodymium', 'Praseodymium', 'Pr'),
(60, 'Neodymium', 'Neodymium', 'Nd'),
(61, 'Promethium', 'Promethium', 'Pm'),
(62, 'Samarium', 'Samarium', 'Sm'),
(63, 'Europium', 'Europium', 'Eu'),
(64, 'Gadolinium', 'Gadolinium', 'Gd'),
(65, 'Terbium', 'Terbium', 'Tb'),
(66, 'Dysprosium', 'Dysprosium', 'Dy'),
(67, 'Holmium', 'Holmium', 'Ho'),
(68, 'Erbium', 'Erbium', 'Er'),
(69, 'Thulium', 'Thulium', 'Tm'),
(70, 'Ytterbium', 'Ytterbium', 'Yb'),
(71, 'Lutetium', 'Lutetium', 'Lu'),
(72, 'Hafnium', 'Hafnium', 'Hf'),
(73, 'Tantalum', 'Tantalum', 'Ta'),
(74, 'Wolframium', 'Tungsten', 'W'),
(75, 'Rhenium', 'Rhenium', 'Re'),
(76, 'Osmium', 'Osmium', 'Os'),
(77, 'Iridium', 'Iridium', 'Ir'),
(78, 'Platinum', 'Platinum', 'Pt'),
(79, 'Aurum', 'Gold', 'Au'),
(80, 'Mercury', 'Hydrargyrum', 'Hg'),
(81, 'Thallium', 'Thallium', 'Tl'),
(82, 'Plumbum', 'Lead', 'Pb'),
(83, 'Bismuthum', 'Bismuth', 'Bi'),
(84, 'Polonium', 'Polonium', 'Po'),
(85, 'Astatium', 'Astatine', 'At'),
(86, 'Radon', 'Radon', 'Rn'),
(87, 'Francium', 'Francium', 'Fr'),
(88, 'Radium', 'Radium', 'Ra'),
(89, 'Actinium', 'Actinium', 'Ac'),
(90, 'Thorium', 'Thorium', 'Th'),
(91, 'Protactinium', 'Protactinium', 'Pa'),
(92, 'Uranium', 'Uranium', 'U'),
(93, 'Neptunium', 'Neptunium', 'Np'),
(94, 'Plutonium', 'Plutonium', 'Pu'),
(95, 'Americium', 'Americium', 'Am'),
(96, 'Curium', 'Curium', 'Cm'),
(97, 'Berkelium', 'Berkelium', 'Bk'),
(98, 'Californium', 'Californium', 'Cf'),
(99, 'Einsteinium', 'Einsteinium', 'Es'),
(100, 'Fermium', 'Fermium', 'Fm'),
(101, 'Mendelevium', 'Mendelevium', 'Md'),
(102, 'Nobelium', 'Nobelium', 'No'),
(103, 'Lawrencium', 'Lawrencium', 'Lr'),
(104, 'Rutherfordium', 'Rutherfordium', 'Rf'),
(105, 'Dubnium', 'Dubnium', 'Db'),
(106, 'Seaborgium', 'Seaborgium', 'Sg'),
(107, 'Bohrium', 'Bohrium', 'Bh'),
(108, 'Hassium', 'Hassium', 'Hs'),
(109, 'Meitnerium', 'Meitnerium', 'Mt')
";

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
echo 'New record created successfully
';
}
else
{
echo 'Unable to INSERT data:
' . $sql .'
' . mysql_error();
}
}
/*** if we are unable to select the database show an error ***/
else
{
echo 'Unable to select database';
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


The above code will populate the elements table with all one hundred and nine elements. This is far superior to one hundred and nine seperate INSERT statements and database connections. Be aware that of the Max Allowed Packet size as large SQL statements of several thousand INSERTs or BLOBs may breach this limit.

INSERT multiple records with mysqli

The mysqli extenstion provides an object oriented approach to the same task. The mysqli extension contains a class method named multi_query() for exactly this purpose. This class method basically takes an SQL statement, or multiple SQL statements concatenated by a semicolon ; character. Lets see it in action with a small subset of our periodic table of elements. We use a subset because I had to type that first lot out and it took me hours ...



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** sql to INSERT a new record ***/
/*** note the semi colon on the end of each statement ***/
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 1, 'HYDROGENIUM', 'Hydrogen', 'H');";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 2, 'HELIUM', 'Helium', 'He');";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 3, 'LITHIUM', 'Lithium', 'Li')";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 4, 'BERYLLIUM', 'Beryllium', 'Be');";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 5, 'BORUM', 'Boron', 'B')";


/*** run the multiple statements and check for errors ***/
if($mysqli->multi_query($sql) !== FALSE)
{
echo 'New records created successfully
';
}
else
{
echo $sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


The above, shortened INSERT will work for the whole table and it is left as an exercise for the reader to complete the table. The mysqli extension provides a second method for achieving the same result for multiple INSERT. We can also use prepared statements and bind parameters. The mysql extension can send a statement or query without any data to the mysql database. You can then associate or "bind" variables to the columns.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** Create the SQL statement ***/
$sql = 'INSERT INTO elements VALUES(?, ?, ?, ?)';

/*** Initialize a statement object for mysqli_stmt_prepare() ***/
$stmt = mysqli_stmt_init($mysqli);

/*** prepare our statement ***/
if (mysqli_stmt_prepare($stmt, $sql))
{
/*** bind the parameters ***/
mysqli_stmt_bind_param($stmt, 'isss', $atomicnumber, $latin, $english, $abbr);

/*** set parameters and execute ***/
$atomicnumber = 1;
$latin = 'Hydrogenium';
$english = 'Hydrogen';
$abbr = 'H';
mysqli_stmt_execute($stmt);

/*** set parameters and execute ***/
$atomicnumber = 2;
$latin = 'Helium';
$english = 'Helium';
$abbr = 'He';
mysqli_stmt_execute($stmt);

/*** set parameters and execute ***/
$atomicnumber = 3;
$latin = 'Lithium';
$english = 'Lithium';
$abbr = 'Li';
mysqli_stmt_execute($stmt);

/*** set parameters and execute ***/
$atomicnumber = 4;
$latin = 'Beryllium';
$english = 'Beryllium';
$abbr = 'Be';
mysqli_stmt_execute($stmt);

/*** set parameters and execute ***/
$atomicnumber = 5;
$latin = 'Borum';
$english = 'Boron';
$abbr = 'B';
mysqli_stmt_execute($stmt);
}
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


We see above a more modular solution to the issue. We can create code blocks also that are easy to read and easy to manage. Just a note on binding parameters. Lets examine the mysqli_stmt_bind_param() line of code...


mysqli_stmt_bind_param($stmt, 'isss', $atomicnumber, $latin, $english, $abbr);


This function binds the parameters to the query and tells the database what the parameters are. Next is the "isss" arguement. This listes the types of data that the parameters are. The i tells mysql that the parameter is an integer. The s character tells mysql that the parameter is a string. This arguement may be one of four types.

• i - integer
• d - double
• s - string
• b - BLOB

You must have one of these for each parameter. Prepared statements separate data from logic, thereby aiding us in our quest for secure data. By telling mysql what type of data to expect we can help minimise the risk of SQL injection vulnerabilities in our code. Should any values from external sources be used it is critical that they be sanitized and validated. More on this topic later...

INSERT multiple records with PDO

If you have followed the previous example with mysqli, the step to using PDO is small. The PDO interface allows us to use bound parameters and prepared statements also. The great benifit of PDO is exceptions and use of arrays for queries. For multiple a mulitple INSERT statement would could use an array as a parameter. The PDO interface will also give easy access to the use of transactions. In the following example we will see how to quickly deal with a multiple INSERT. Once again we will use only a small portion of the table to demonstrate for obvious reasons.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** begin the transaction ***/
$dbh->beginTransaction();
/*** our SQL statememtns ***/
$dbh->exec("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (1, 'Hydrogen', 'Hydrogen', 'H')");
$dbh->exec("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (1, 'Hydrogenium', 'Hydrogen', 'H')");
$dbh->exec("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (2, 'Helium', 'Helium', 'He')");
$dbh->exec("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (3, 'Lithium', 'Lithium', 'Li')");
$dbh->exec("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (4, 'Beryllium', 'Beryllium', 'Be')");
$dbh->exec("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (5, 'Borum', 'Boron', 'B')");

/*** commit the transaction ***/
$dbh->commit();
/*** echo a message to say the database was created ***/
echo 'Data entered successfully
';
}
catch(PDOException $e)
{
/*** roll back the transaction if we fail ***/
$dbh->rollback();

/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


Because PDO is a total package it will of course allow us to use prepared statements and bound parameters also. Bound parameters are superior in that they minimize bandwidth the the server as you need send only the parameters each time, and not the whole query. This also reduces parsing time as the preparation on the query is done only once. Lets look at the PDO method of preparation and binding of parameters.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** prepare the sql ***/
$stmt = $dbh->prepare("INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES (:atomicnumber, :latin, :english, :abbr)");
$stmt->bindParam(':atomicnumber', $atomicnumber);
$stmt->bindParam(':latin', $latin);
$stmt->bindParam(':english', $english);
$stmt->bindParam(':abbr', $abbr);

/*** insert a row ***/
$atomicnumber = 1;
$latin = 'Hydrogenium';
$english = 'Hydrogen';
$abbr = 'H';
$stmt->execute();

/*** insert another row ***/
$atomicnumber = 2;
$latin = 'Helium';
$english = 'Helium';
$abbr = 'He';
$stmt->execute();

/*** insert another row ***/
$atomicnumber = 3;
$latin = 'Lithium';
$english = 'Lithium';
$abbr = 'Li';
$stmt->execute();

/*** insert another row ***/
$atomicnumber = 4;
$latin = 'Beryllium';
$english = 'Beryllium';
$abbr = 'Be';
$stmt->execute();

/*** insert another row ***/
$atomicnumber = 5;
$latin = 'Borum';
$english = 'Boron';
$abbr = 'B';
$stmt->execute();


/*** echo a message to say the database was created ***/
echo 'Records entered successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


SELECT data with MySQL

This will be the most used feature of your database. Up to this point we have concentrated on getting connected and putting information into the database and now we will see various methods of getting this information out of the database, and of displaying it correctly on a web page. This is what dynamic web sites are all about. To access our information in the database we need to query (ask) the database with the SELECT command. SELECT will do exactly as the name describes and select what ever we tell it to. Once we SELECT the data from the database we need to be able to display it onto a web page. PHP has many tools for this job and makes life rather simple for us. But lets just get an example happening and more will be clear.

SELECT with mysql

Once again we still need to be able to connect to the database server and select the database we wish to extract information from. So much of the code we have used before remains the same in that respect. It is only when we wish to SELECT and display this information that things are new.

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** select the database we wish to use ***/
if(mysql_select_db($dbname, $link) === TRUE)
{
/*** sql to SELECT information***/
$sql = "SELECT * FROM elements";

/*** run the query ***/
$result = mysql_query($sql);

/*** check if the result is a valid resource ***/
if(is_resource($result))
{
/*** check if we have more than zero rows ***/
if(mysql_num_rows($result) !== 0)
{
while($row=mysql_fetch_array($result))
{
echo '




';
}
}
else
{
/*** if zero results are found.. ***/
echo 'Zero results found';
}
}
else
{
/*** if the resource is not valid ***/
echo 'No valid resource found';
}
}
/*** if we are unable to select the database show an error ***/
else
{
echo 'Unable to select database '.$dbname;
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>

Atomic Number	Latin	English	Abbreviation
'.$row['atomicnumber'].'	'.$row['latin'].'	'.$row['english'].'	'.$row['abbr'].'

The table that results from this code will look like this, Only first 5 records shown for the sake of sanity.

Atomic Number	Latin	English	Abbreviation
1	Hydrogenium	Hydrogen	H
2	Helium	Helium	He
3	Lithium	Lithium	Li
4	Beryllium	Beryllium	Be
5	Borum	Boron	B

OK, so there is a little to digest here. We see our connection as we have been using it in all our previous examples. The same applies for the selection of the database. If all is well up to that point, ie: no errors, then we see our SQL query.
SELECT * FROM elements
The * character is a wildcard in SQL meaning everything. What we are doing is SELECTing all records from the elements table, five of them in this example. The next line of code runs our query for us and assigns the resulting data to a variable called $result.

If the $result variable is a valid resource, we continue with our code. We then use the function mysql_num_rows() to check that we have more than zero amount of rows returned. The is_resource() function does not tell us if we SELECTed zero results. We could have done something like this.


if(is_resource($result) && mysql_num_rows($result) !== 0)
{
display results here
}
else
{
echo 'An error has occurred';
}
?>


The above line would tell us that the result is valid AND we have more than zero lines. So why not have them together and make our code smaller? The answer lays in error checking. We may wish to handle the errors differently. If there is a problem we would not know which problem it is. Is it a problem with the result or is it a problem with the number of rows returned. Sometime verbose code is better for readability sake.

From there, assuming we have a valid result and more than zero rows in our result, we use the function mysql_fetch_array() to put all the results into an array that we can iterate over with a while() loop. The while() loop executes the statement within
$row=mysql_fetch_array($result)
as long as it is TRUE. This means it will continue looping over the result set and assigning the $row array variable which we can then access and output along with our HTML table structure.

SELECT with mysqli

In our previous INSERT with mysqli we saw how to bind parameters which helped to shorten up our code base. The mysqli extension also supports bound results or "bound output parameters". Binding results helps us as we no longer need to explicitly assign results to a variable. Lets see it in action.

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** our SELECT query ***/
$sql = "SELECT atomicnumber, latin, english, abbr FROM elements";

/*** prepare statement ***/
if($stmt = $mysqli->prepare($sql))
{
/*** execute our SQL query ***/
$stmt->execute();
/*** bind the results ***/
$stmt->bind_result($atomicnumber, $latin, $english, $abbr);
/*** loop over the result set ***/
while ($stmt->fetch())
{
/*** echo our table rows ***/
echo '';
}
}
/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>

Atomic Number	Latin	English	Abbreviation
'.$atomicnumber.'	'.$latin.'	'.$english.'	'.$abbr.'

The use of bound paramenters and prepared statements are a quantum leap for developers as the increased performance, security and convenience can make scalable, large-scale developments much faster and easier to create.

SELECT with PDO

The PDO extension does not have the richness of features the mysqli extension has but this is more then compensated for by the addition of the SPL class and its ability to create your own extended classes. Here we will use these built in classes to create our table.

/*** extend the RecursiveIteratorIterator ***/
class TableRows extends RecursiveIteratorIterator{

function __construct($it){
/*** here we use the parent class and use LEAVES_ONLY to ***/
parent::__construct($it, self::LEAVES_ONLY);
}

/*** use the current method to wrap up our ';
}

/*** use the beginChildren method to begin a table row ***/
function beginChildren(){
echo '';
}

/*** use the endChildren method to close a table row ***/
function endChildren() {
echo ''."\n";
}

} // end class

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare('SELECT * FROM elements');

/*** exceute the query ***/
$stmt->execute();

/* by setting the FETCH mode we can set
*the resulting arrays to numerical or associative
*/
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

/*** the result should be an instance of PDOStatement ***/
/*** IteratorIterator converts it and after that you can do any iterator operation with it ***/
/*** The iterator will fetch the results for us. ***/
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v)
{
echo $v;
}

$dsn = null;
}
catch (PDOException $e)
{
print "Error!: " . $e->getMessage() . '
';
}
?>

Atomic Number	Latin	English	Abbr
s ***/ function current(){ return '	'.parent::current().'

So, the combination of internal PHP classes and interfaces gives a far superior and standardised method of interacting with MySQL for the purposes of displaying data.

SELECT Mulitple Conditions

We have seen above how to SELECT information FROM a table in MySQL. But what if you wanted to be a little more conditional on what it is you are SELECTing. Eg: you may wish to SELECT only records that have an id of 1. Perhaps only SELECTing elements 21 and 38. To do this we use the two new concepts, WHERE and AND. Whichever method you are using to access the database, mysql, mysqli, or PDO, all you need do is change your SQL query as follows to SELECT records which have an atomic number of 21 AND latin name is Scanadium.


/*** sql to select with 2 conditions ***/
$sql = "SELECT * FROM elements WHERE atomicnumber=21 AND latin='Scanadium'";

?>

From the above SQL query we recieve only a single result. To take this one step further you could introduce the OR operator. It works in much the same manner except is conditional on one of the values being true rather than both. This SQL statement shows how.


/*** sql to select with two possible conditions ***/
$sql = "SELECT * FROM elements WHERE atomicnumber=88 OR latin='Scanadium'";

?>

Now we see that the we recieve two results back in our dataset as the SQL has returned any row where either of the possibilities is true. You can, of course, use more than one AND or OR in a SQL query to test as many conditions as you wish.

Deleting Data

Deleting data from a MySQL database is the same as running and command as we saw earlier in this tutorial. If you have read the first few sections of this tutorial then the syntax should look familiar. In this example we will delete the record with the atomic number of 4. In real world situations you would most likely get the number from a $_GET or $_POST variable from a form. This entails vigorous checking of variables for sanity and validity. This concept is covered in the tutorial Securing PHP Forms.

Delete with mysql.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** sql to create a database ***/
$sql = 'DELETE FROM periodic_table WHERE atomicnumber=4';

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
echo 'Record Deleted
';
}
else
{
echo 'Unable to Delete Record:
' . $sql .'
' . mysql_error();
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


As you can see, this is exactly the same procedure with other SQL statements that do not return a result set. Lets move on with mysqli.

Delete with mysqli



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

/*** create a new mysqli object ***/
$mysqli = @new mysqli($hostname, $username, $password);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** our sql statement ***/
$sql = 'DELETE FROM periodic_table WHERE atomicnumber=4';

if($mysqli->query($sql) === TRUE)
{
echo 'Record Deleted successfully
';
}
else
{
echo 'Unable to Delete Record
'.$sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Delete with PDO



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = 'rootpassword';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** our sql statement ***/
$sql = 'DELETE FROM periodic_table WHERE atomicnumber=4';

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** echo a message to say all is well ***/
echo 'Record Deleted successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo 'Unable to Delete Record
'.$sql . '
' . $e->getMessage();
}
?>


UPDATING Data in MySQL

We have seen in previous sections how to SELECT, INSERT and DELECT data from mysql, but there are times when we need to simply UPDATE the existing information. The UPDATE key word is used by MySQL for this purpose.

UPDATE with MySQL

To update an existing record with the MySQL functions is much the same as the INSERT function with the only appreciable difference being the SQL query has changed. For our purposes here, we will be updating a single record and renaming the a field to lowercase, with a capital letter. There are other methods of doing this, but this example shows well the process of UPDATE. Lets see how it fits together.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** select the database we wish to use ***/
if(mysql_select_db("periodic_table", $link) === TRUE)
{
/*** sql to UPDATE an existing record ***/
$sql = "UPDATE elements SET latin = 'Hydrogenium' WHERE atomicnumber=1";

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
/*** get the number of rows UPDATE has affected ***/
$affected_rows = mysql_affected_rows($link);
echo $affected_rows.' Records UPDATED successfully
';
}
else
{
echo 'Unable to UPDATE data:
' . $sql .'
' . mysql_error();
}
}
/*** if we are unable to select the database show an error ***/
else
{
echo 'Unable to select database';
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


From the code above we get the result
Connected successfully
1 Records UPDATED successfully

We have used the mysql_affected_rows() function to tell us how many rows have been updated. It should be noted here that if the SQL statement were executed with the WHERE clause, ALL of the records would be updated with the new value. This is why it is extremely important excersise caution when using UPDATE.

UPDATE with mysqli

Once again, this is similar to the INSERT statement with just the SQL changing to perform the UPDATE.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** sql to UPDATE an existing record ***/
$sql = "UPDATE elements SET latin = 'Hydogenium' WHERE atomicnumber=1";

/*** execute the query ***/
if($mysqli->query($sql) === TRUE)
{
echo mysqli_affected_rows($mysqli). ' Records UPDATED successfully
';
}
else
{
echo 'Unable to UPDATE Records: '.$sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>

Above we see again that the record has been updated. This time we have used the mysqli_affected_rows() function to tell us how many records have been affected by the query, in this case, just the single record.

UPDATE with PDO

The PDO class has a much simpler approach for UPDATING as it does with INSERT and all database interactions.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** sql to UPDATE an existing record ***/
$sql = "UPDATE elements SET latin = 'Hydrogenium' WHERE atomicnumber=1";

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** echo a message to say the UPDATE succeeded ***/
echo 'Records UPDATED successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo 'UPDATE Failed: '. $sql . '
' . $e->getMessage();
}
?>


The above code of course returns
Records UPDATED successfully
However, this does not give us the number of records affected but the UPDATE. The PDO class itself has no PDO::affected_rows. If you need this functionality, you need to use the PDOStatement::rowCount() method. It looks like this:


/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** sql to UPDATE an existing record ***/
$sql = "UPDATE elements SET latin = 'Hydrognium' WHERE atomicnumber=1";

/*** Prepare statement ***/
$stmt = $dbh->prepare($sql);

/*** execute the query ***/
$stmt->execute();

/*** echo a message to say the UPDATE succeeded ***/
echo $stmt->rowCount().' Records UPDATED successfully
';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo 'UPDATE Failed: '. $sql . '
' . $e->getMessage();
}
?>


Now of course we can see the number of rows affected. It is important to note that if the new value is exactly the same as the existing value in the database, then MySQL will not report change anything, and zero will be returned by mysql_affected_rows(), mysqli_affected_rows and PDOStatement::rowCount().

LIMIT Data Selections

MySQL provides a LIMIT clause that, as the name suggests, limits the number of records affected by an SQL query or statement. Imagine we wished to SELECT all the records from 1 - 20 inclusive in our SELECT query. As this is strictly a MySQL issue no PHP code is provided. Here we will display the SQL need to SELECT. The PHP code is exactly the same as in the above SELECT section.


$sql = "SELECT * FROM periodic_table LIMIT 20";


When run, the above SQL query will return the first twenty results which you can then do with what you wish. But what If I want to offset the beginning of the records and SELECT records 6-15 inclusive? Mysql provides a partner to cater for offsets called OFFSET. When used with LIMIT this will provide a result set beginning with the OFFSET number, and limited to the number of LIMIT. Lets see it working.


/*** SQL query with LIMIT and OFFSET ***/
$sql ="SELECT * FROM periodic_table 5, 10


This can be very effective for use in pagination of PHP pages used for photo sites or any series of data objects stored in a mysql table.

Configuration Options

The Mysql extension comes some very handy configuration options to help change the runtime behaviour as described in php.ini. The options include:

• mysql.allow_persistent
• mysql.max_persistent
• mysql.max_links
• mysql.trace_mode
• mysql.default_port
• mysql.default_socket
• mysql.default_host
• mysql.default_user
• mysql.default_password
• mysql.connect_timeout

At a glance it is not difficult to see what these options do. But we will step through the just the same.

mysql.allow_persistent

This option defines whether to allow mysql_pconnect() to allow persistant connections to the database. The default value for this is On and reasons for disabling this option may be that many scripts running persistant connections could have the MySQL server quickly reach its maximum number of allowed connections due to the persistant connections not being closed when the sctipt is finished, nor when mysql_close() is called.

mysql.max_persistent

Of course, if the above scenario looks likely and you still wish to allow a sane level of persistant connections, this option may be set. The default value of this option is -1 which allows unlimitted connections.

mysql.max_links

This option differs from the above by setting the maximum number of connections, including persistant connections, per process. The default value of -1 means no limit is set.

mysql.trace_mode

When the trace_mode option is set to On, warnings for table and index scans and and SQL errors will be displayed. For good reason, the default value for this option is set to Off.

mysql.default_port

Setting the default port with this option will allow the use of mysql_connect() to connect to a pre-defined port number. If this option is not set, then it will default to the default port as specified by $MYSQL_TCP_PORT or for windows, the compile time defined MYSQL_PORT. *nix users may also find it uses the mysql-tcp port entry in /etc/services or the compile time defined MYSQL_PORT. Note: *nix systems will check for the default port in the order of $MYSQL_TCP_PORT, mysql-tcp port entry in /etc/services, MYSQL_PORT.

mysql.default_socket

By setting this option you may set the default socket name for local MySQL connections. If this option is left empty, the MySQL default is used.

mysql.default_host

This option sets the default host used for mysql_connect(). Note: This option is not available in Safe Mode.

mysql.default_user

Similar to the above, it sets the default username for use with mysql_connect(). Note: This option is not available in Safe Mode.

mysql.default_password

And to complment the above, but sets the default password for mysql_connect. Note: this is a Bad Thing(tm). Any system user with read access to php.ini can see this or even worse, a php script with such as this can see the value:


echo cfg_get_var("mysql.default_password");
?>


mysql.connect_timeout

As the name suggests, this sets the default time out, in seconds, for MySQL connectios. The default is 60 seconds and a value of -1 means no limit it set. Note: Do not set this to -1.

Formatting Dates with MySQL

When SELECTing a data value from MySQL it is generally returned as a MySQL timestamp that looks like this:
2007-10-23 22:23:00
This is generally not useful for end users who know nothing of MySQL timestamps. There are several ways we can deal with dates, and of course, wherever there are different methods of doing something, holy wars arise and prophets sprout up from under their rocks to tell you the "right" way to do it, and should you vary from thier chosen path, you are of course an idiot. We will make no such judgement here today and simply show you two methods of date formatting. The first we will select the date from MySQL as a UNIX TIMESTAMP rather than the default MySQL timestamp. We can then use the PHP date functions for format the date into whatever order we choose.

We will create a temporary table with a date field for the purpose and INSERT a record. The first MySQL TIMESTAMP field in a table will automatically populate with the current time and date, ie: NOW(). We will then SELECT the date_field as it comes from the database.

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** begin the transaction ***/
$dbh->beginTransaction();

/*** CREATE a TEMPORARY TABLE and INSERT a record ***/
$dbh->exec("CREATE TEMPORARY TABLE date_table (id INT(2), date_field TIMESTAMP(14),
INDEX USING BTREE (id)) ENGINE = MEMORY");
$dbh->exec("INSERT INTO date_table (id) VALUES (1)");

/*** commit the transaction ***/
$dbh->commit();

/*** echo a message to say the database was created ***/
echo 'Data entered successfully
';

/*** SQL to fetch the date ***/
$sql = "SELECT * FROM date_table";

/*** prepare the query ***/
$stmt = $dbh->prepare($sql);
if ($stmt->execute())
{
while ($row = $stmt->fetch())
{
echo $row['date_field'];
}
}
}
catch(PDOException $e)
{
/*** roll back the transaction if we fail ***/
$dbh->rollback();

/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}


When the above code is run, it returns a result similar (with different date and time) to this:
Connected to database
Data entered successfully
2007-01-05 02:30:07

This is the standard format of a MySQL TIMESTAMP, but it means little to most end users.

Format Date with PHP

To get the date_field value into a UNIX TIMESTAMP that can be used by PHP date functions we need to change the SQL statement to this:
"SELECT *, UNIX_TIMESTAMP(date_field) AS my_date FROM date_table"
Lets run through the script a second time and show the new date value, then format the value with PHP date() function.


/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** begin the transaction ***/
$dbh->beginTransaction();

/*** CREATE a TEMPORARY TABLE and INSERT a record ***/
$dbh->exec("CREATE TEMPORARY TABLE date_table (id INT(2), date_field TIMESTAMP(14),
INDEX USING BTREE (id)) ENGINE = MEMORY");
$dbh->exec("INSERT INTO date_table (id) VALUES (1)");

/*** commit the transaction ***/
$dbh->commit();

/*** echo a message to say the database was created ***/
echo 'Data entered successfully
';

/*** SQL to fetch the date ***/
$sql = "SELECT *, UNIX_TIMESTAMP(date_field) AS my_date FROM date_table";

/*** prepare the query ***/
$stmt = $dbh->prepare($sql);
if ($stmt->execute())
{
while ($row = $stmt->fetch())
{
/*** assingn a variable ***/
$unix_timestamp = $row['my_date'];
echo "UNIX TIMESTAMP: $unix_timestamp
";
/*** echo the Formatted date ***/
echo "Formatted Date: ".date('D, d M Y H:i:s T');
}
}
}
catch(PDOException $e)
{
/*** roll back the transaction if we fail ***/
$dbh->rollback();

/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}

?>


The above code will produce output similar to this:
Connected to database
Data entered successfully
UNIX TIMESTAMP: 1167948339
Formatted Date: Fri, 05 Jan 2007 09:05:39 EST

From the code above we can see the use of the PHP date() function to make the UNIX TIMESTAMP into a human readable interpretation using the line
echo "Formatted Date: ".date(\'D, d M Y H:i:s T\');
Of course, you may want your date formatted differently to this, so PHP offers a wide range of variations and can seen in PHP manual date section.

Format Date with MySQL

MySQL also has the ability to format dates. The MySQL built in function DATE_FORMAT() is used for this purpose. This allows the formatting of date directly in the database when the SELECT is made, and no PHP code is needed to alter the result.

/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** begin the transaction ***/
$dbh->beginTransaction();

/*** CREATE a TEMPORARY TABLE and INSERT a record ***/
$dbh->exec("CREATE TEMPORARY TABLE date_table (id INT(2), date_field TIMESTAMP(14),
INDEX USING BTREE (id)) ENGINE = MEMORY");
$dbh->exec("INSERT INTO date_table (id) VALUES (1)");

/*** commit the transaction ***/
$dbh->commit();

/*** echo a message to say the database was created ***/
echo 'Data entered successfully
';

/*** SQL to fetch the date ***/
$sql = "SELECT *, DATE_FORMAT(date_field, '%W, %d %Y %r') AS my_date FROM date_table";

/*** prepare the query ***/
$stmt = $dbh->prepare($sql);
if ($stmt->execute())
{
while ($row = $stmt->fetch())
{
/*** echo the formatted date ***/
echo "Formatted Date: ".$row['my_date'];
}
}
}
catch(PDOException $e)
{
/*** roll back the transaction if we fail ***/

/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}

?>


The above code uses the the SQL statement:
"SELECT *, DATE_FORMAT(date_field, '%W, %d %Y %r') AS my_date FROM date_table"
This line has MySQL do the date formatting for us and makes for some shorter PHP code. The output is similar and presented like this:

Connected to database
Data entered successfully
Formatted Date: Friday, 05 2007 10:06:30 AM

There are many other formatting options available in MySQL along with a wide range of date manipulation features. The MySQL manual is recommended reading for more information.

Format Date with PHP and MySQL

We have seen in the two sections above different methods for formatting dates. Both work well and produce similar output. However their is one difference that is quite important. We note in the first example that the timezone is Eastern Standard Time (EST) and in the second example, no timezone information is avialable. This could lead to problems when, as is often the case, the HTTP server and the MySQL server are on two different machines, and, in two different timezones. We can use a combination of PHP and MySQL to fix this problem and set a default timezone all our date information. Here we will discard the time settings, and focus on the DATE only.


/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'periodic_table';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** begin the transaction ***/
$dbh->beginTransaction();

/*** CREATE a TEMPORARY TABLE and INSERT a record ***/
$dbh->exec("CREATE TEMPORARY TABLE date_table (id INT(2), date_field TIMESTAMP(14),
INDEX USING BTREE (id)) ENGINE = MEMORY");
$dbh->exec("INSERT INTO date_table (id) VALUES (1)");

/*** commit the transaction ***/
$dbh->commit();

/*** echo a message to say the database was created ***/
echo 'Data entered successfully
';

/*** SQL to fetch the date ***/
$sql = "SELECT * FROM date_table";

/*** prepare the query ***/
$stmt = $dbh->prepare($sql);
if ($stmt->execute())
{
while ($row = $stmt->fetch())
{
/*** assign the date to a variable ***/
$my_date = $row['date_field'];
}
}

/*** the server timezones ***/
$mysql_timezone = new DateTimeZone("Europe/London");
$httpd_timezone = new DateTimeZone("Australia/Sydney");

$mysql_datetime = date_create($my_date , $mysql_timezone);

/*** set the local timezone ***/
$httpd_timezone = new DateTimeZone("Australia/Sydney");
$httpd_datetime = new Datetime($my_date , $httpd_timezone);

/*** display the local time ***/
echo 'Sydney Time: '.$httpd_datetime->format('D, d M Y H:i:s').'
';

/*** show the time difference ***/
$offset = $httpd_timezone->getOffset($mysql_datetime);

/*** modify the time with the offet ***/
$httpd_datetime->modify("-$offset seconds");

/*** show the London time ***/
echo 'London Time: '.$httpd_datetime->format('D, d M Y H:i:s').'
';

}
catch(PDOException $e)
{
/*** roll back the transaction if we fail ***/

/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
catch(Exception $e)
{
echo $e->getMessage();
}
?>


This sort of time juggling can come in handy in many situations. Even if you choose only to display a single date from your database with the DateTime class, you will be able to do so in an environment that allows you to set you default timezone.

Load a CSV File

If you are reading this section then you know what a CSV file is. Lets take a look at a quicky that would represent data to go into our periodic table of elements. We will name this file periodic_table.csv and it will look like this.


1, HYDROGENIUM, Hydrogen, H
2, HELIUM, Helium, He
3, LITHIUM, Lithium, Li
4, Beryllium, Beryllium, Be
5, Borum, Boron, B


We have kept the number of elements to 5 for the sake of brevity and sanity. Using any of the mysql, mysqli, or PDO methods of executing a query will work. All that changes is the SQL statement used to load the file. To load the file into the respective fieds, which are
atomicnumber, latin, english, abbr
we use the following SQL statement


$sql = "LOAD DATA INFILE '/path/to/periodic_table.csv' REPLACE INTO TABLE elements
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (atomicnumber, latin, english, abbr)";
?>


Then by running the query you will have successfully loaded the file.

Preventing SQL Injection

What is SQL injection?
SQL injection is a type of attack that allows users to execute SQL statements via a web form. This definition is a little concise, as SQL injection relates to a whole class of attacks. You will pick it up quickly as we progress. All problems relating to this type of attack come from a single source, not checking or validating user input. Before we go on, lets see a little example of what an attack using SQL injection looks like.

<form method="post">
<input type="text" name="searchtext" /><br />
<select name="fieldname">
<option value="atomicnumber">Atomic Numberoption>
<option value="latin">Latinoption>
<option value="english">Englishoption>
<option value="abbr">Abbreviationoption>
select>
<input type="submit" value="Query" />
form>
php
if(isset($_POST['searchtext'], $_POST['fieldname']) && $_POST['searchtext'] != '')
{
/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** select the database we wish to use ***/
if(mysql_select_db($dbname, $link) === TRUE)
{
/*** sql to SELECT information***/
$sql = "SELECT * FROM elements WHERE ".$_POST['fieldname']." = '".$_POST['searchtext']."'";

/*** echo the sql query ***/
echo '

'.$sql.'

';

/*** run the query ***/
$result = mysql_query($sql);

/*** check if the result is a valid resource ***/
if(is_resource($result))
{
/*** check if we have more than zero rows ***/
if(mysql_num_rows($result) !== 0)
{
echo '';
while($row=mysql_fetch_array($result))
{
echo '




';
}
echo '

'.$row['atomicnumber'].'

'.$row['latin'].'

'.$row['english'].'

'.$row['abbr'].'

';
}
else
{
/*** if zero results are found.. ***/
echo 'Zero results found';
}
}
else
{
/*** if the resource is not valid ***/
'No valid resource found';
}
}
/*** if we are unable to select the database show an error ****/
else
{
echo 'Unable to select database '.$dbname;
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
}
else
{
echo 'Please Choose An Element';
}
?>

The form and PHP script above is not uncommon to many that populate the web. The form will work without issue when given the correct data to work with. Like many PHP scripts, it relies on data supplied from userland, in this case from POST. The values may come from GET or even curl. Lets look at the problem. If we were to enter the number one (1) in the search text box and the dropdown menu set to Atomic Number, we would get a result that looked like this:

SELECT * FROM elements WHERE atomicnumber = '1'

1

Hydrognium

Hydrogen

H

The results are as expected and the script works quite well, there is plenty of runtime error checking etc, but.. a malicious user may be able to enter, or inject, their own SQL code into the query. Lets change the search text to look like this.

1' OR latin='Helium

Now when we run the query the results look like this:

SELECT * FROM elements WHERE atomicnumber = '1' OR latin='Helium'

1	Hydrognium	Hydrogen	H
2	Helium	Helium	He

We have now successfully exploited the script and injected our own SQL into it. Well, big deal you might say, you can see an extra row. As it stands, this is quite harmless. But suppose our malicious user is a total ass and wants to play dirty. Lets now change the search text to look like this:

1' OR 1='1

The SQL query will now look like this:

SELECT * FROM elements WHERE atomicnumber = '1' OR 1='1'

Now when we submit the form, the results show the entire table! Most web sites will have a user database, so lets add one to our periodic_table database. Here is a dump of what it may look like.

CREATE TABLE p_users (
user_id tinyint(2) NOT NULL auto_increment,
user_name varchar(50) NOT NULL,
user_pass varchar(50) NOT NULL,
PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO p_users (user_id, user_name, user_pass)
VALUES (1, 'admin', 'secretpassword'),
(2, 'lame_user', 'lame_password');

With the p_users table in place, our simple SQL inject exploit now takes on tragic proportions. With a little craftiness we can guess that there is an username called admin, or perhaps a regular users username may be known. We can construct a query now to guess what the password may be. In our current p_users database the admin password is secretpassword. Our query could be to test if the first letter is the character 'a'. So in our search text field we put this code:

' OR EXISTS(SELECT * FROM p_users WHERE user_name='admin' AND user_pass LIKE 'a%') AND ''='

This will produce an output like this

SELECT * FROM elements WHERE atomicnumber = '' OR EXISTS(SELECT * FROM p_users WHERE user_name='admin' AND user_pass LIKE 'a%') AND ''=''

Zero results found

This means the password does not begin with an a, lets see what happens if we wish to see if it begins with an 's'. We simple change our search text to this:

' OR EXISTS(SELECT * FROM p_users WHERE user_name='admin' AND user_pass LIKE 's%') AND ''='

Now we see a minor change to the query and it looks like this:

SELECT * FROM elements WHERE atomicnumber = '' OR EXISTS(SELECT * FROM p_users WHERE user_name='admin' AND user_pass LIKE 's%') AND ''=''

But below this we see the contents of the table. This tells us the query was successful and the password does indeed begin wthe character 's'. It is now simple task to follow along and get the remaining characters by simply altering the search text again, we can move along the password string.

' OR EXISTS(SELECT * FROM p_users WHERE user_name='admin' AND user_pass LIKE '_e%') AND ''='

This process can be continued till the password is correctly guessed. Then, we are at the mercy of user. The same method can be used to find a username also. By using wildcards it is possible to see if a username contains the letter 'b'. With a little trial and error, the username and password can quickly fall into the wrong hands.

' OR EXISTS(SELECT * FROM p_users WHERE user_name='bill' AND user_pass LIKE '%b%') AND ''='

Of course, this type of attack is common in open source environments where it is trivial to access the source code to see what the table names are. But even with closed source, it is still possible to see the table names. Security through obscurity is a myth.

/* Does the database contain the letter p? */
' OR EXISTS(SELECT 1 FROM dual WHERE database() LIKE '%p%') AND ''='
/* check if there is a table called foo in database periodic_table */
' OR EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='periodic_table' AND TABLE_NAME='foo') AND ''='
/* check if there is more than one table in the database containing the letter p? */ ' OR (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE '%p%')>1 AND ''='

Of course, most haXorZ just want to gain access and will try to brute force their way in. A clever hacker though, may try something like this if promted for a username and password to access the system.

' OR ''='

If this is provided for both the username and the password fields, it will always evaluate as true and entry is gained. You lose again. So, how do you protect yourself from this sort of thing? We begin with a simple caveat when accepting variables from users...

1. NEVER TRUST USER INPUT
2. NEVER TRUST USER INPUT
3. NEVER TRUST USER INPUT
4. NEVER TRUST USER INPUT
5. NEVER TRUST USER INPUT

These five simple rules should make it clear that we need to be careful with information we gain from outside sources. With proper filtering of variables, and escaping of data, we can protect ourselves from these types of attacts. Lets run through them.

Prevent with MySQL

MySQL comes with a client function named mysql_real_escape_string that is used for escaping data to prevent SQL injection. The PHP conterpart to this function is mysql_real_escape_string(). When we accept data from external sources, such as a web form or via the query string, it is essential to that the varaible values be checked to be sure we are getting what we are expecting. Checks also need to be made that each variable is safe. Here we show how this may be achieved using the standard MySQL functions.

Atomic Number
Latin
English
Abbreviation

if(filter_has_var(INPUT_POST, 'searchtext') && filter_has_var(INPUT_POST, 'fieldname'))
{
/*** check the variables for content ***/

/*** a list of filters ***/
$filters = array(
'searchtext' => array( 'filter' => FILTER_CALLBACK, 'options' => 'mysql_real_escape_string'),
'fieldname' => array( 'filter' => FILTER_CALLBACK, 'options' => 'mysql_real_escape_string')
);

/*** escape all POST variables ***/
$input = filter_input_array(INPUT_POST, $filters);

/*** check the values are not empty ***/
if(empty($input['fieldname']) || empty($input['searchtext']))
{
echo 'Invalid search';
}
else
{
/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'periodic_table';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** select the database we wish to use ***/
if(mysql_select_db($dbname, $link) === TRUE)
{
/*** sql to SELECT information***/
$sql = sprintf("SELECT * FROM elements WHERE %s = '%s'", $input['fieldname'], $input['searchtext']);

/*** echo the sql query ***/
echo '

'.$sql.'

';

/*** run the query ***/
$result = mysql_query($sql);

/*** check if the result is a valid resource ***/
if(is_resource($result))
{
/*** check if we have more than zero rows ***/
if(mysql_num_rows($result) !== 0)
{
echo '';
while($row=mysql_fetch_array($result))
{
echo '




';
}
echo '

'.$row['atomicnumber'].'

'.$row['latin'].'

'.$row['english'].'

'.$row['abbr'].'

';
}
else
{
/*** if zero results are found.. ***/
echo 'Zero results found';
}
}
else
{
/*** if the resource is not valid ***/
'No valid resource found';
}
}
/*** if we are unable to select the database show an error ****/
else
{
echo 'Unable to select database '.$dbname;
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
}
}
else
{
echo 'Please Choose An Element';
}
?>

The code above will accept our input queries as expected, however, now if we try to submit any nastiness in the search text like that below our results will be different than we have previously seen.

1' OR latin='Helium

By using the above searchtext string we now see that the query and results look like this:

SELECT * FROM elements WHERE atomicnumber = '1\' OR latin=\'Helium'

1

Hydrognium

Hydrogen

H

As you can see, the search string has been escaped and is no longer a threat to our SQL query. We have used the php filter_input_array with a callback to mysql_real_escape_string to filter the variables from the POST array. This allows the use of the variables safely in our query. Prior to that, we added a check to see if the values were empty. This will prevent a malicious users trying to use other input methods to match two null values, which would evaluate to TRUE.

Prevent with MySQLi

Prevent with PDO

Last Insert ID

The MySQL extensions permit the fetching of the ID of the last INSERT on a table with an AUTO_INCREMENT field. This means when a record is added, the ID can be retrieved immediately.

The following code examples assume a database with the fields user_id, user_name, user_email and user_dob. The schema would look like this. Note that the user_id field is an AUTO_INCREMENT field.

CREATE TABLE users (

 user_id int(11) NOT NULL AUTO_INCREMENT,

 user_name varchar(25) NOT NULL,

 user_email varchar(100) NOT NULL,

 user_dob datetime NOT NULL,

 PRIMARY KEY (user_id)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Last Insert ID With MySQL

To fetch the last insert id with the mysql extension the mysql_insert_id() function is used. This snippet shows how.



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** connect to the database ***/
$link = @mysql_connect($hostname, $username, $password);

/*** check if the link is a valid resource ***/
if(is_resource($link))
{
/*** if we are successful ***/
echo 'Connected successfully
';

/*** select the database we wish to use ***/
if(mysql_select_db("test", $link) === TRUE)
{
/*** sql to INSERT a new record ***/
$sql = "INSERT INTO users (user_name, user_email, user_dob)
VALUES ( 'Kevin', 'kevin@example.com', '1995-08-22')";

/*** run the sql query ***/
if(mysql_query($sql, $link))
{
/*** assign the last insert id ***/
$last_id = mysql_insert_id( $link );
echo "New record created successfully with id of $last_id
";
}
else
{
echo 'Unable to INSERT data:
' . $sql .'
' . mysql_error();
}
}
/*** if we are unable to select the database show an error ***/
else
{
echo 'Unable to select database';
}
/*** close the connection ***/
mysql_close($link);
}
else
{
/*** if we fail to connect ***/
echo 'Unable to connect';
}
?>


Last Insert ID With mysqli

The mysqli extension provides the mysqli::insert_id() method and the mysqli_insert_id() function. This offers the user two choices with the first used in object oriented coding and the second for procedural code. Both will return the last ID of generated by a table with an AUTO_INCREMENT field when an INSERT or UPDATE is performed, such as in the code that follows.

Object Oriented



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'test';

/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);

/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully
';

/*** sql to INSERT a new record ***/
$sql = "INSERT INTO users (user_name, user_email, user_dob)
VALUES ( 'Kevin', 'kevin@example.com', '1995-08-22')";

if($mysqli->query($sql) === TRUE)
{
/*** assign the last insert id ***/
$last_id = $mysqli->insert_id;
echo "New record created successfully With ID of $last_id";
}
else
{
echo $sql.'
' . $mysqli->error;
}

/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>


Procedural


/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** mysql database name ***/
$dbname = 'test';

/*** connection ***/
$link = @mysqli_connect($hostname, $username, $password, $dbname);

/*** check for connection error ***/
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
}
else
{
/*** the sql inser ***/
$sql = "INSERT INTO users (user_name, user_email, user_dob)
VALUES ( 'Kevin', 'kevin@example.com', '1995-08-22')";

/*** run the query ***/
if(!mysqli_query($link, $sql))
{
echo 'Unable to add record';
}
else
{
$last_id = mysqli_insert_id($link);
echo "New record created successfully with id of $last_id";
}

/*** close connection ***/
mysqli_close($link);
}
?>


Last Insert ID With PDO



/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'username';

/*** mysql password ***/
$password = 'password';

/*** database name ***/
$dbname = 'test';

try
{
/*** connection string ***/
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);

/*** echo a message saying we have connected ***/
echo 'Connected to database
';

/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** sql to INSERT a new record ***/
$sql = "INSERT INTO users (user_name, user_email, user_dob)
VALUES ( 'Kevin', 'kevin@example.com', '1995-08-22')";

/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);

/*** show the last insert id ***/
$last_id = $dbh->lastInsertId();
echo "New record created successfully With ID of $last_id";
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '
' . $e->getMessage();
}
?>


Useful Tips

By now you should have the gist of how to interact with MySQL using PHP. Here are a tips to help you get going when you are confronted with other situations


/*** set the initial mysql password ***/
mysqladmin -u root password 'my_password'

/*** select yesterday ***/
SELECT * FROM blah WHERE TO_DAYS(datetime_col) = ( TO_DAYS(NOW()) - 1 )

/*** reset auto increment ***/
ALTER TABLE tbl_name AUTO_INCREMENT = 1

/*** start auto increment from 100 ***/
ALTER TABLE tbl_name AUTO_INCREMENT = 100

/*** select all records from today ***/
SELECT * FROM table WHERE DATE_FORMAT(date_column, '%Y-%m-%d')=DATE_FORMAT(NOW(), '%Y-%m-%d')

/*** select records from last 30 minutes ***/
select DATE_SUB(NOW(),INTERVAL 30 MINUTE);

/*** select records from last hour ***/
select DATE_SUB(NOW(),INTERVAL 1 HOUR);

/*** select records from last week ***/
select DATE_SUB(NOW(),INTERVAL 1 WEEK);

/*** get the last day of next month ***/
SELECT LAST_DAY('2006-03-06' + INTERVAL 1 MONTH) AS pay_day;

/*** select only records that appear once ***/
SELECT foo FROM table GROUP BY foo HAVING ( COUNT(foo) = 1 )


/*** select only records that appear more than once ***/
SELECT foo, COUNT(foo) AS NumOccurrences FROM table GROUP BY foo HAVING ( COUNT(foo) > 1 )

/*** select count of records for each hour ***/
SELECT HOUR(timestamp_col) AS hour , COUNT(*) AS count FROM your_table GROUP BY HOUR(timestamp_col)

/*** rename a table ***
"RENAME TABLE periodic_table.elements TO periodic_table.nice_elements";

/*** import a csv file ***/
LOAD DATA INFILE '/path/to/xxx.csv' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n" (col1,col2,col3)

/*** select from table1 where records not in table2 ***/
SELECT table1.some_id FROM table1 LEFT OUTER JOIN table2 ON table1.some_id=table2.some_id WHERE table2.some_id IS NULL

/*** natural sorting for MySQL results ***/
SELECT some_numbers FROM your_table ORDER BY some_numbers + 0 ASC

/*** update text in field named description ***/
UPDATE my_table set description=REPLACE(description, 'Old Text', 'New Text')


RSS Feed

Search

	Enter your search terms
	Web www.phpro.org Submit search form

PHPRO.ORG Poll

Who do you rate lowest?

RIAA
Microsoft
Lawyers
Swedes
Prostitutes

Warning: Participation in PHPRO.ORG polls may incorrectly lead you to believe your opinions matter.

Read more...