How to retrieve mysql data in PHP

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 ismysql_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

<?php
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’;
?>
Thewhile() loop will keep fetching new rows untilmysql_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 constantMYSQL_ASSOCas the second argument tomysql_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 calledmysql_fetch_assoc() which also return the row as an associative array.

<?php
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 constantMYSQL_NUM, as the second argument tomysql_fetch_array(). This will cause the function to return an array with numeric index.

<?php
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 constantMYSQL_NUM withmysql_fetch_array() gives the same result as the functionmysql_fetch_row().There is another method for you to get the values from a row. You can uselist(), to assign a list of variables in one operation.

<?php
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 bymysql_fetch_row() into the variable$name,$subjectand$message.
Of course you can also do it like this

<?php
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
  Freeing the memory ?
In some cases a query can return large result sets. As this results are stored in memory there’s a concern about memory usage. However you do not need to worry that you will have to call this function in all your script to prevent memory congestion. In PHP all results memory is automatically freed at the end of the script’s execution.
But you are really concerned about how much memory is being used for queries that return large result sets you can use mysql_free_result().Calling this function will free all memory associated with the result identifier ($result ).
Using the above example you can callmysql_free_result() like this :

 Convert MySQL Query Result To Excel
Using PHP to convert MySQL query result to Excel format is also common especially in web based finance applications. The finance data stored in database are downloaded as Excel file for easy viewing. There is no special functions in PHP to do the job. But you can do it easily by formatting the query result as tab separated values or put the value in an HTML table. After that set the content type toapplication/vnd.ms-excel
Example :convert.php
Source :convert.php,students.txt

<?php
include 'library/config.php';
include 'library/opendb.php';
$query  = "SELECT fname, lname FROM students";
$result = mysql_query($query) or die('Error, query failed');
$tsv  = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
   $tsv[]  = implode("\t", $row);
   $html[] = "

” .implode(“ “, $row) .              “

“;
}
$tsv = implode(“\r\n”, $tsv);
$html = “

” . implode(“\r\n”, $html) . “

“;
$fileName = ‘mysql-to-excel.xls’;
header(“Content-type: application/vnd.ms-excel”);
header(“Content-Disposition: attachment; filename=$fileName”);
echo $tsv;
//echo $html;
include ‘library/closedb.php’;
?>
In the above example$tsv is a string containing tab separated values and$htmlcontain an HTML table. I useimplode()to join the values of$row with tab to create a tab separated string.
After the while loop implode() is used once again to join the rows using newline characters. The headers are set and the value of$tsvis then printed. This will force the browser to save the file asmysql-to-excel.xsl
Try running the script in your own computer then try commentingecho $tsv and uncommentecho $html to see the difference.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s