MySQL - Fetch Array
MySQL doesn't have a Fetch Array function. mysql_fetch_array is actually a PHP function that allows you to access data stored in the result returned from a successful mysql_query. If you have been jumping around our MySQL Tutorial then you would have already seen this function popping up all over the place.
This lesson will teach you how and why to use mysql_fetch_array in your PHP Scripts.
Do you know what is returned when you used the mysql_query function to query a MySQL database? It isn't something you can directly manipulate, that is for sure. Here is a sample SELECT query of a table we created in the MySQL Create Table page.
MySQL Fetch Array PHP and MySQL Code
<?php
$result = mysql_query("SELECT * FROM example");
?>
The value that mysql_query returns and stores into $result is a special type of data, it is a MySQL Resource. Additional PHP functions are required to extract the data from this Resource.
The mysql_fetch_array function takes a MySQL query resource as an argument ($result) and returns the first row of data returned by the mysql_query. Our table example basically looks like the table below.
example MySQL Table:
| Name | Age |
|---|
| Putu Sanjaya | 20 |
| Made Sanjaya | 18 |
| Haru Sanjaya | 22 |
The first row of data in this table is "Putu Sanjaya" and "20". When we fetch an array from our MySQL Resource $result it should have Putu's name and age in it.
mysql_fetch_array returns the first row in a MySQL Resource in the form of an associative array. The columns of the MySQL Result can be accessed by using the column names of the table. In our table example these are: name and age. Here is the code to print out the first MySQL Result row.
Getting a Row of Data using mysql_fetch_array PHP and MySQL Example
<?php
// Make a MySQL Connection
$query = "SELECT * FROM example";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['name']. " - ". $row['age'];
?>
Getting a Row of Data PHP & MySQL Display
Putu Sanjaya - 20
This is just what we expected would happen! Now, the cool thing about mysql_fetch_array is that you can use it again on the same MySQL Resource to return the second, third, fourth and so on rows. You can keep doing this until the MySQL Resource has reached the end (which would be three times in our example).
Sounds like an awfully repetitive task. It would be nice if we could get all our results from a MySQL Resource in an easy to do script.
As we have said, the mysql_fetch_array function returns an associative array, but it also returns FALSE if there are no more rows to return! Using a PHP While Loop we can use this information to our advantage.
If we place the statement "$row = mysql_fetch_array()" as our while loop's conditional statement we will accomplish two things:
- We will get a new row of MySQL information that we can print out each time the while loop checks its conditional statement.
- When there are no more rows the function will return FALSE causing the while loop to stop!
Now that we know what we need to do and how to go about doing it, the code pretty much writes itself, so let's move on to the next lesson. Just kidding! Here is the code that will print out all the rows of our MySQL Resource.
Fetch Array While Loop PHP and MySQL Code
<?php
// Make a MySQL Connection
$query = "SELECT * FROM example";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['name']. " - ". $row['age'];
echo "
";
}
?>
Fetch Array While Loop PHP & MysQL Display
Putu Sanjaya - 20
Made Sanjaya - 23
Haru Sanjaya - 18
And there we have all the rows from our example table! You could apply this script to any MySQL table as long as you change both the table name in the query and the column names that we have in the associative array.
