Welcome to S-Design
The home of your bespoke, professional, fast-loading, interactive, database-driven web presence.
Menu
MySQL TUTORIALS


MySQL Fetch Array

Once you've got some records in your database, you might want to get, or select, them!
The MySQL Select chapter demonstrates how to do just that. But how do you get at the data returned by MySQL?
Technically, you use PHP, but as it relates to MySQL, we're including this tutorial in the MySQL section!

Select a record(s) from a MySQL Database: <?PHP
include_once('DBConnect.php');

$query=mysql_query("select email, address, phone, from table_name where name='$name' limit 1");

if(mysql_num_rows($query)>0){

$result=mysql_fetch_array($query);
$email=$result['email'];
$address=$result['address'];
$phone=$result['phone'];

}
else{
echo 'No Records Found';
}

?>
After performing the mysql_query and storing the records (if any) in the PHP variable called $query, we then have to check if $query contains any records.
We do this by checking if MySQL returned any rows (records) using the mysql_num_rows() function and telling it to check the query just performed ($query).
Here we're checking to see if the number of rows in $query is greater than 0, and if so we proceed to getting the data in that row.
We now need to get/fetch the data inside $query and put it into a PHP array called $result. We do this using the mysql_fetch_array() function.
$result will now be an array that looks like: $result=array('name:John Smith','email:j.smith@gmail.com','phone:01207568294');.
To get the data from the array and assign them to individual variables, we simply use the $result array, and specify which field we want... so $email=$result['email']; tells PHP to get the value of the email field inside the $result array and store it in a variable called $email.


The above example is great if you only have 1 record to return, but what if you have more than 1? <?PHP
include_once('DBConnect.php');

$query=mysql_query("select email, address phone, from table_name where name='$name' limit 10");

if(mysql_num_rows($query)>0){

while($result=mysql_fetch_array($query)){

$email=$result['name'];
$address=$result['address'];
$phone=$result['phone'];

}

}
else{
echo 'No Records Found';
}

?>
This example is almost identical to the first, except we've wrapped the array-getting part $result=mysql_fetch_array($query) inside of a while() loop. See the PHP Loops chapter for how to use loops.
This will loop through each record returned from the mysql_query, and will automatically stop when there are no more results!


If you know that there will only be one result, and that that result will only include 1 bit of information, like a row count for example, there is a quicker way to get at that data, than by using mysql_fetch_array(), it's called mysql_result().
Compare these two examples: <?PHP
include_once('DBConnect.php');

$query=mysql_query("select count(*) as number from table_name where name='$name'");

if(mysql_num_rows($query)>0){

$result=mysql_fetch_array($query)
$number=$result['number'];

}
else{
echo 'No Records Found';
}

?>
<?PHP
include_once('DBConnect.php');

$query=mysql_query("select count(*) as number from table_name where name='$name'");
$number=mysql_result($query,0);

?>
We know there is only going to be one row returned from the query, and we know that that row will only contain one field (number), so there is little point in fetching an array (which by definition has more than one name:value).
Instead we simply get the first row (0) from the result!

Title