MySQL and PHP

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().

Move to page: 1 2 3

Comments are not currently being accepted for this article.