Using a MySQL database with PHP - Page 2

September 24, 2002

Running queries

Now that you're connected, let's try and run some queries. The easiest kind of query to perform is one that doesn't return any results, such as an INSERT, UPDATE or DELETE statement. The function used to perform queries is once again aptly named - mysql_query().

resource mysql_query ( string query [, resource link_identifier [, int result_mode]])

In the case of a query that doesn't return results, the resource that the function returns is simply a value true or false. True means the query succeeded, and false means it failed. The link identifier in our case is $dbh, the database handle. We don't need the result mode yet. Let's add another row of data to the table. Change the script to read 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");
print "connected to mysql<br>";
$selected = mysql_select_db("first_test",$dbh) 
	or die("Could not select first_test");
if (mysql_query("insert into people values('5','Hazel','Burger')")) {
  print "successfully inserted record";
}
else {
	  print "Failed to insert record";
}
mysql_close($dbh);
?>

Note that you don't need to end the query with the semicolon you usually end MySQL queries with. PHP takes care of that for you. The first time you run this script, all going well, it will display "Successfully inserted record". If you run it again, you should get "Failed to insert record". Remember that we made the id field a primary key. When we try and add '5' to the id field a second time, the query fails because it would result in a duplicate key if it succeeded.

To return the results of a query, for example with a SELECT statement, we start in the same way, with the mysql_query() function. This time though the function returns a resource that contains the results of the query, called the result set (or statement handle). We can then use one of the many fetch functions to examine the result. We're going to use the most flexible one, mysql_fetch_array, which returns the results row by row as both an associative array and a numeric array.

array mysql_fetch_array ( resource result [, int result_type])

It takes the result resource returned from the mysql_query() function as an argument. To access the array, you can use either $array[0] for the first field (according to the SELECT statement, i.e the id), $array[1] for the second field (first_name), and so on, or the more convenient $array{'id'}, $array{'first_name'} and so on. It's a waste of resources to populate two arrays, so you should usually choose to return only one kind of array. The optional result type argument specifies which way to return the data. It can be either MYSQL_ASSOC to return an associative array, MYSQL_NUM to return a numeric array, or MYSQL_BOTH (the default) to return both. A convenient way to access all the rows is with a while loop. As soon as there are no more results, mysql_fetch_array will return false, and the while loop will exit. For example:
while ($row = mysql_fetch_array($result)) {
Let's add the code to our script. Replace the insert in the script with a select, and then add the while loop and a line to display the results, 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");
$result = mysql_query("SELECT id, first_name,surname FROM people");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
print "ID:".$row{'id'}." Name:".$row{'first_name'}." 
	".$row{'surname'}."<br>";
}
mysql_close($dbh);
?>