- Drop me a line! Contact me here.
MySQL and PHP
MySQL and PHP
PHP is a great language for developing websites as it gives you all the tools you'll need in order to make any site you can dream up. If its online then the changes are it can be made using PHP. However, on its own PHP has a limitation. When it coes to storing large amounts of data you really need to look at another application, a database. While it would be possible to create a data storage solution in PHP (using flat text files for example), it would be quite a silly idea. Databases are much faster, easier to use, and they've been created already.
One particular database application the is very well suited to running alongside PHP is MySQL. MySQL is free (with certain licensing restrictions), fast, and quite robust. Whats more, PHP has a suite of functions builtin to take advantage of MySQL, so you're able to jump right in and start working with it right away.
Connecting to a MySQL database
The first thing you need to do in order to use data stored in a MySQL database with your PHP script is connect to the database server. This is usually the same server as your website is stored on, but it doesn't have to be.
The three variables passed to the mysql_connect() statement tell PHP where the database server is, and which database user to connect as. "locahost" in this example is the computer that the script is running on. In this example the web server and database server are the same machine. "user" is a database user that has permission to access the database that we'll be using. Database servers can have many users all with different permissions to access various databases stored on the server. "password" is "user"s database password.
Next we need to tell PHP which database we want to use. In our example we'll be using "shopdb", a database for an online ecommerce store.
In the mysql_select_db() statement we have told PHP to use the "shopdb" database for $connection, the database connection that we set up in step 1.
Now that we have a database selected we're ready to do something with it.
Querying a database
There are lots of different kinds of query that you can run on a database, but we'll concentrate on the three most used: SELECT, INSERT, and UPDATE. A SELECT query is used to pull data out so that you can use it.
This will generate a resultset for all the records in the items table of the database. There are lots of functions in SQL that we could use to alter which records are returned, but thats an exercise in SQL rather than PHP, so we'll save the details of that for a later article.
Once you have a set of results you need to get at the data in them. To do this there are several functions available. Which one you should use depends on your preferred style of coding. There are:
mysql_fetch_array() - This function fetchs each row as an array. What sort of array depends on which type you request in the function call.
mysql_fetch_row() - This function fetchs each row as an enumerated array.
mysql_fetch_assoc() - This function fetchs each row as an associative array.
mysql_fetch_object() - This function fetchs each row as an object.
Whichever you choose you will need to iterate through each row of the results if there is more than one returned.
One particular database application the is very well suited to running alongside PHP is MySQL. MySQL is free (with certain licensing restrictions), fast, and quite robust. Whats more, PHP has a suite of functions builtin to take advantage of MySQL, so you're able to jump right in and start working with it right away.
Connecting to a MySQL database
The first thing you need to do in order to use data stored in a MySQL database with your PHP script is connect to the database server. This is usually the same server as your website is stored on, but it doesn't have to be.
<?php
$connection = mysql_connect("localhost","user","password");
?>
The three variables passed to the mysql_connect() statement tell PHP where the database server is, and which database user to connect as. "locahost" in this example is the computer that the script is running on. In this example the web server and database server are the same machine. "user" is a database user that has permission to access the database that we'll be using. Database servers can have many users all with different permissions to access various databases stored on the server. "password" is "user"s database password.
Next we need to tell PHP which database we want to use. In our example we'll be using "shopdb", a database for an online ecommerce store.
<?php
mysql_select_db("shopdb",$connection);
?>
In the mysql_select_db() statement we have told PHP to use the "shopdb" database for $connection, the database connection that we set up in step 1.
Now that we have a database selected we're ready to do something with it.
Querying a database
There are lots of different kinds of query that you can run on a database, but we'll concentrate on the three most used: SELECT, INSERT, and UPDATE. A SELECT query is used to pull data out so that you can use it.
<?php
$sql = "select * from items"; $result = mysql_query($sql,$connection);
?>
This will generate a resultset for all the records in the items table of the database. There are lots of functions in SQL that we could use to alter which records are returned, but thats an exercise in SQL rather than PHP, so we'll save the details of that for a later article.
Once you have a set of results you need to get at the data in them. To do this there are several functions available. Which one you should use depends on your preferred style of coding. There are:
mysql_fetch_array() - This function fetchs each row as an array. What sort of array depends on which type you request in the function call.
mysql_fetch_row() - This function fetchs each row as an enumerated array.
mysql_fetch_assoc() - This function fetchs each row as an associative array.
mysql_fetch_object() - This function fetchs each row as an object.
Whichever you choose you will need to iterate through each row of the results if there is more than one returned.
In this example we'll use mysql_fetch_object() as it means the code is a little tidier with fewer brackets and quotes around the place.
To step through the results one by one we can use a while() loop. This means that while there is a result the code within the loop block is executed. The loop steps through the results until there aren't any more remaining in the set.
$record is an object that contains the data of the result. Using an object allows us to refer $record->[columnname].
If there are no results returned by the SQL query it would be good to be able to run some specific code to handle an empty query. Fortunately PHP gives us a function that allows us to check how many results were returned from a query.
This is especially useful for telling the user that no results were found for a listing page, or for some search results. Its always better to have a nice message instead of just an empty page after all. If there are results returned we use the code to step through them as in step 4.
The next type of SQL query that is quite common is an INSERT. This is what we use to put data into the database. The basic format of an SQL INSERT query is "insert into [tablename] (column1,column2,column3) values (value1,value2,value3)". In exactly the same way as the SELECT statement we just put this into the mysql_query() function to run it on the database we're connected to.
If the items table has an automatically incrementing id field as the primary key it might be useful to know what that is, we may be doing two inserts in the same script and need to know what the item id of the data we've just inserted is. PHP gives us a function to get this information from the database without needing to do a SELECT in order to find it.
There is something that should be considered with this function. There is no worry that mysql_insert_id() will return an incorrect id for the last insert even under heavy strain. Mysql_insert_id() returns the last id for the connection used so it will always return the correct result.
The final common type of SQL query is the update statement. This is also run by passing an UPDATE to mysql_query() in the same way as the SELECT and the INSERT.
It is often quite useful to know if an update state affected any of the database data, and PHP has a way to find out with the mysql_affected_rows() function.
To step through the results one by one we can use a while() loop. This means that while there is a result the code within the loop block is executed. The loop steps through the results until there aren't any more remaining in the set.
<?php
while ($record = mysql_fetch_object($result)) {
echo $record->item;
}
?>
$record is an object that contains the data of the result. Using an object allows us to refer $record->[columnname].
If there are no results returned by the SQL query it would be good to be able to run some specific code to handle an empty query. Fortunately PHP gives us a function that allows us to check how many results were returned from a query.
<?php
if (mysql_num_rows($result) > 0) {
//Do something with the results
} else {
//No results were found
}
?>
This is especially useful for telling the user that no results were found for a listing page, or for some search results. Its always better to have a nice message instead of just an empty page after all. If there are results returned we use the code to step through them as in step 4.
The next type of SQL query that is quite common is an INSERT. This is what we use to put data into the database. The basic format of an SQL INSERT query is "insert into [tablename] (column1,column2,column3) values (value1,value2,value3)". In exactly the same way as the SELECT statement we just put this into the mysql_query() function to run it on the database we're connected to.
<?php
$sql = "insert into item (name,quantity) values ('Widget','1')";
mysql_query($sql,$connection);
?>
If the items table has an automatically incrementing id field as the primary key it might be useful to know what that is, we may be doing two inserts in the same script and need to know what the item id of the data we've just inserted is. PHP gives us a function to get this information from the database without needing to do a SELECT in order to find it.
<?php
$id = mysql_insert_id($connection);
?>
There is something that should be considered with this function. There is no worry that mysql_insert_id() will return an incorrect id for the last insert even under heavy strain. Mysql_insert_id() returns the last id for the connection used so it will always return the correct result.
The final common type of SQL query is the update statement. This is also run by passing an UPDATE to mysql_query() in the same way as the SELECT and the INSERT.
<?php
$sql = "update item set quantity='2' where item_id='1'";
mysql_query($sql,$connection);
?>
It is often quite useful to know if an update state affected any of the database data, and PHP has a way to find out with the mysql_affected_rows() function.
The function mysql_affected_rows() returns the number of rows in the database table that were changed by the last SQL statement run on the database. If the query was an INSERT statement then this will usually be one (in fact, this is quite a useful way to programmatically tell if an INSERT worked), but an UPDATE can affect many rows at once.
The final mysql query function that needs to be mentioned is mysql_unbuffered_query(). This is used in exactly the same way as mysql_query(), and has exactly the same syntax.
The difference between an ordinary query and an unbuffered query is in the way MySQL sends the data back to the PHP script. In an ordinary query MySQL will work out all the results and save them into an area of memory. For small sets of data this is a very good idea as it speeds everything up, MySQL doesn't need to go back to the data to fetch the next row. However, if you're dealing with extremely large amounts of data caching them in memory is a bad idea. Having database results take up large amounts of space in memory will slow down other things that need to store information. So we use the unbuffered query instead. This query just finds and returns results as and when the PHP script requests them. It tends to be slightly slower than a standard query, but the memory usage is a fraction of its counterpart.
Traversing sets of data
There will be occasions when moving through a set of results returned from a query one at a time is not right for the script you're writing. For example, if you're paging through some data, or if you want to print out every other record, or if you want to move through a set of results backwards. PHP gives us the opportunity to jump about in a resultset as much as we like by using the mysql_data_seek() function.
The first thing to remember when using mysql_data_seek() is that the first result is not number 1. MySQL resultsets are numbered from 0 to the number of results minus 1. So if there are 10 results returned from the database they will be numbers 0 to 9. To find the last record we use the mysql_num_rows() function that was used to find if our SELECT query contained any rows. Once we find the number of rows we subtract one to find the index of the last row we can seek to.
This will take the 10th row of a resultset and place the data into an object called $record.
Now we jump to the last row of the resultset and place it into the $record object.
One important thing to note about using mysql_data_seek() is that it will only work on a standard buffered query, if you have used mysql_unbuffered_query() to select your data then you cannot use mysql_data_seek().
<?php
$rows = mysql_affected_rows($connection);
?>
The final mysql query function that needs to be mentioned is mysql_unbuffered_query(). This is used in exactly the same way as mysql_query(), and has exactly the same syntax.
<?php
$sql = "select * from items";
$result = mysql_unbuffered_query($sql,$connection);
?>
The difference between an ordinary query and an unbuffered query is in the way MySQL sends the data back to the PHP script. In an ordinary query MySQL will work out all the results and save them into an area of memory. For small sets of data this is a very good idea as it speeds everything up, MySQL doesn't need to go back to the data to fetch the next row. However, if you're dealing with extremely large amounts of data caching them in memory is a bad idea. Having database results take up large amounts of space in memory will slow down other things that need to store information. So we use the unbuffered query instead. This query just finds and returns results as and when the PHP script requests them. It tends to be slightly slower than a standard query, but the memory usage is a fraction of its counterpart.
Traversing sets of data
There will be occasions when moving through a set of results returned from a query one at a time is not right for the script you're writing. For example, if you're paging through some data, or if you want to print out every other record, or if you want to move through a set of results backwards. PHP gives us the opportunity to jump about in a resultset as much as we like by using the mysql_data_seek() function.
The first thing to remember when using mysql_data_seek() is that the first result is not number 1. MySQL resultsets are numbered from 0 to the number of results minus 1. So if there are 10 results returned from the database they will be numbers 0 to 9. To find the last record we use the mysql_num_rows() function that was used to find if our SELECT query contained any rows. Once we find the number of rows we subtract one to find the index of the last row we can seek to.
<?php
$sql = "select * from items";
$result = mysql_query($sql,$connection);
mysql_data_seek($result,9);
$record = mysql_fetch_object($result);
?>
This will take the 10th row of a resultset and place the data into an object called $record.
<?php
mysql_data_seek($result,mysql_num_rows($result)-1);
$record = mysql_fetch_object($result);
?>
Now we jump to the last row of the resultset and place it into the $record object.
One important thing to note about using mysql_data_seek() is that it will only work on a standard buffered query, if you have used mysql_unbuffered_query() to select your data then you cannot use mysql_data_seek().
- © Ooer.com Chris Neale 2007
- PHPGD.com
- Powered by PHP
- Database by MySQL
- DB Queries: 3
- DB Time: 0.0226 seconds
