This weeks PHP code of the week is mysql_fetch_array, mysql while rows and count which allow you to retrieve a MySQL row from queries performed, loop through all the rows performing a certain action and how to count an array.
mysql_fetch_array can be used to retrieve the row(s) returned from the query and be placed into an associative array. Say if we wanted to retrieve user details such as name, address and suburb by using a username we could do something as shown below.
[php]$sql = “SELECT name, address, suburb FROM userdata WHERE username = ‘alex'”;
if ( !($result = mysql_query($sql)) ) {
die(‘Could not query users table’);
}
$userdata = mysql_fetch_array($result, MYSQL_ASSOC);[/php]
So we have the standard SQL query been performed and then we use mysql_fetch_array with the arguments as our result from the MySQL query and the second argument saying that we want an associative array.
This means that name, address and suburb are in the array of $userdata. An array is a variable which can contain other variables at different ‘addresses’. Arrays are in the format shown below.
[php]$myarray[0] = “Hiâ€;
$myarray[1] = 234;
$myarray[5] = “Testâ€;
echo $myarray[0]; // Prints out Hi
echo $myarray[1]; // Prints out 234
echo $myarray[5]; // Prints out Test[/php]
The start of an array always begins at 0 and it’s important to remember this as sometimes you may forget and it’s naturally to believe 1 should be the start of the array.
So regarding our $userdata example, we used an associative array and therefore the array is not indexed by numbers but rather words. In this case we always put the word in quotation marks. In this case the words are the columns we have selected our query to return to us.
[php]echo $userdata[‘name’]; // Prints out the user’s name
echo $userdata[‘address’]; // Prints out the user’s address
echo $userdata[‘suburb’]; // Prints out the user’s suburb[/php]
While is a function that loops until a certain condition is meet. In our case we might want to get a list of all users’ usernames and their address that have the suburb as “Sunnybankâ€.
[php]$sql = “SELECT username, address FROM userdata WHERE suburb = ‘Sunnybank'”;
if ( !($result = mysql_query($sql)) ) {
die(‘Could not query users table’);
}
while ($row = mysql_fetch_array($result)) {
echo $row[‘username’] . “ “ . $row[‘address’];
}[/php]
The code above says while there is another row from the result of the MySQL query then print out the username and address. If there are no more rows from the result then $row would be equal to 0 and therefore 0 is false and the while loop would finish.
We could also have something which lets us process the rows at a later time. This could be as a form of an array ($users) as shown below. We left the $users array index empty as while the loop progresses an empty index will assign the current $row to the last available position on the $users array. This means when printing out anything from the $users array we firstly would need a numeric index and then the associative word.
[php]while ($row = mysql_fetch_array($result)) {
$users[] = $row;
}
echo $users[0][‘name’]; // Prints out the name of the user in the first row[/php]
count can be used to count the number of items in an array. So if our array $users had 3 items ($users[0], $users[1] and $users[2]) then count would return the number 3.
[php]echo count($users); // Prints out 3[/php]
So you should now have an understanding how to use mysql_fetch_array, arrays and the count function. Next week we’ll be covering the implode/explode function.