- Drop me a line! Contact me here.
MySQL and PHP
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.
Comments
Comments are not currently being accepted for this article.
Sidebar
Published:
09/01/2007
Views:
3159
Author:
Chris Neale
Labels:
Print:
Sidebar:
- © Ooer.com Chris Neale 2007
- PHPGD.com
- Powered by PHP
- Database by MySQL
- DB Queries: 9
- DB Time: 0.0718 seconds
