Retrieve Data From a MySQL Database
Using PHP you can run a MySQL SELECT query to fetch the data out of the database. You have several options in fetching information from MySQL. PHP provide several functions for this. The first one is mysql_fetch_array()which fetch a result row as an associative array, a numeric array, or both.Below is an example of fetching data from MySQL, the table contact have three columns, name, subject and message.
Example : select.php
Source code : select.phps, contact.txt
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "Name :{$row['name']}
" .
"Subject : {$row['subject']}
" .
"Message : {$row['message']}
";
}
include 'closedb.php';
?>
The while() loop will keep fetching new rows until mysql_fetch_array() returns FALSE, which means there are no more rows to fetch. The content of the rows are assigned to the variable $row and the values in row are then printed. Always remember to put curly brackets when you want to insert an array value directly into a string.
In above example I use the constant MYSQL_ASSOC as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index . Personally I think it's more informative to use $row['subject'] instead of $row[1].
PHP also provide a function called mysql_fetch_assoc() which also return the row as an associative array.
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
echo "Name :{$row['name']}
" .
"Subject : {$row['subject']}
" .
"Message : {$row['message']}
";
}
include 'closedb.php';
?>
You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
echo "Name :{$row[0]}
" .
"Subject : {$row[0]}
" .
"Message : {$row[0]}
";
}
include 'closedb.php';
?>
Using the constant MYSQL_NUM with mysql_fetch_array() gives the same result as the function mysql_fetch_row().
There is another method for you to get the values from a row. You can use list(), to assign a list of variables in one operation.
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while(list($name,$subject,$message)= mysql_fetch_row($result))
{
echo "Name :$name
" .
"Subject : $subject
" .
"Message : $row
";
}
include 'closedb.php';
?>
In above example, list() assign the values in the array returned by mysql_fetch_row() into the variable $name, $subject and $message.
Of course you can also do it like this
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_row($result))
{
$name = $row[0];
$subject = $row[1];
$message = $row[2];
echo "Name :$name
" .
"Subject : $subject
" .
"Message : $row
";
}
include 'closedb.php';
?>
So you see you have lots of choices in fetching information from a database. Just choose the one appropriate for your program
