Using a MySQL database with PHP - Page 3

September 24, 2002

Returning information about results

So far you have no way of knowing how many records were affected or returned (the information that MySQL gives you at the bottom of the query). There are two functions that provide this - one for queries that affected data but did not return any (INSERT's etc), and the other for queries that returned data (SELECT's etc). The mysql_affected_rows() function returns the number of rows affected by the last query.

int mysql_affected_rows ( [resource link_identifier])

This can be useful to see if you actually did perform an update, for example. Let's modify our script as follows:

<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";	
$dbh = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to MySQL");
$selected = mysql_select_db("first_test",$dbh) 
	or die("Could not select first_test");
if (mysql_query("UPDATE people SET surname='Arendse' WHERE id=6")) {
  print "Successfully updated data";
}
else {
	  print "Failed to update data";
}
mysql_close($dbh);
?>

If you run the script, it will seem to succeed. But no data will have been changed, because there are no records with an id of 6. So the UPDATE succeeded, but affected no records. Actually Hazel Burger has married and changed her name to Hazel Arendse, and we wanted to reflect this. We can get a more meaningful result, and have picked up our problem, if we'd used mysql_affected_rows(). Change the script as follows:

<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";	
$dbh = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to MySQL");
$selected = mysql_select_db("first_test",$dbh) 
	or die("Could not select first_test");
if (mysql_query("UPDATE people SET surname='Arendse' WHERE id=6")) {
  $rows_affected = mysql_affected_rows($dbh);
  print "Successfully updated $rows_affected rows";
}
else {
	  print "Failed to update data";
}
mysql_close($dbh);
?>

Note that mysql_affected_rows takes the database handle as the argument, not the result from the query. It will only return data for the most recent query. If you ran other UPDATE's and DELETE's, you would lose the affected rows data unless you caught it before any other queries.

You can return similar information about data returned with the mysql_num_rows() function. It returns the number of rows returned from the last query. Unlike mysql_affected_rows(), it takes the result resource as an argument, not the database handle. Note that SELECT queries are not the only kind of query that returns data. SHOW, DESCRIBE and EXPLAIN do as well. Examine the following changes to the script:

<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";	
$dbh = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to MySQL");
$selected = mysql_select_db("first_test",$dbh) 
	or die("Could not select first_test");
$result = mysql_query("SHOW DATABASES");
while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
  print "Database:".$row[0]."<br>";
}
mysql_close($dbh);
?>

Hopefully this article has got you started and shown you that the basics of running a database-driven site are not as difficult as you may have thought. Many dynamic sites are not much more complex than this. Of course there's much more than this, and future articles will explore the topic more thoroughly. In the mean time, you can investgate some of the other resources on this topic.