PHP Programing language

MySQL database not using a Fetch Array function.The mysql_fetch_array is inbuilt in a PHP function that allows you to access data stored in the result returned from a successful mysql_query.

This lesson will teach you how and why to use mysql_fetch_array in your PHP Scripts.

Why mysql_fetch_array Use?

In mysql database when using mysql_query function then what is returned. 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 lesson.

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.

What is a Row of Data

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.

The first row of data in this table is "Amit" and "23". When we fetch an array from our MySQL Resource $result it should have Timmy's name and age in it.

Getting a Row of Data using mysql_fetch_array

In this database using mysql_fetch_array returns the first row in a MySQL Resource in the form of an associative array.and it's the columns of the MySQL Result can be accessed by using the column names of the table.

PHP and MySQL Code:
<?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'];
?>

Output:

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.

How to Fetching Array with using While Loop

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.

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 "<br />";
    }
?>

Output:

Amit - 23
Akash - 21
Abhinav - 15

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.