Get Data From MySQL Database
Get Data From 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
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.
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.
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.
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
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 call mysql_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 to application/vnd.ms-excel
Example : convert.php
Source : convert.php, students.txt
In the above example $tsv is a string containing tab separated values and $html contain an HTML table. I use implode() 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 $tsv is then printed. This will force the browser to save the file as mysql-to-excel.xsl
Try running the script in your own computer then try commenting echo $tsv and uncomment echo $html to see the difference.
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
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.
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.
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.
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
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 call mysql_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 to application/vnd.ms-excel
Example : convert.php
Source : convert.php, students.txt
In the above example $tsv is a string containing tab separated values and $html contain an HTML table. I use implode() 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 $tsv is then printed. This will force the browser to save the file as mysql-to-excel.xsl
Try running the script in your own computer then try commenting echo $tsv and uncomment echo $html to see the difference.