Sunday, July 20, 2008

MySQL Query from PHP

i was coordinating with my external project's Facebook App programmer, when i realised that i haven't write an article on how to query using MySQL from PHP. it's simple. so let's get the steps right.

First of all, you will need to connect to your MySQL server first before you can query. this is to verify that the user you are querying with have authorisation to access certain features of MySQL, such as limited table/database privileges.

$link = @mysql_connect(%server%,%user%,%password%);
- $link contains the information of the connection that you have made and might be required by other mysql_* functions. it becomes false when the mysql_connect returns error as false.
- %server% is the server address, and most probably local MySQL server should be "localhost"
- %user% is the user name of the account used to access MySQL on %server%
- %password% confirms the identity.

Only when connected, MySQL then can be accessed using the other functions, such as mysql_select_db(), mysql_query(), mysql_fetch_array(). Now, you will need to select a specific database to work in.

$return = @mysql_select_db(%database%);
- $return returns the value true or false. False on failure.
- %database% is the name of the database without ` warp.

If both functions are a success, then you are able to use mysql_query() to query and get results from the database specified in mysql_select_db().

$result = @mysql_query(%query%);
- $result is the data that is returned from %query% [WARNING: $result is not an array, it just contains data for other functions to process]. it returns false on error.
- %query% is the fully query string. See MySQL query syntax at

Since the $result of the mysql_query function is not an array or object, or anything that PHP can read properly into variables, you will need to use functions like mysql_fetch_array() or mysql_ fetch_ assoc().

To read off all the rows of the result, do a while loop.

$table = array();
while($array = @mysql_fetch_array($result)){
$table[] = $array;

when mysql_fetch_array() reaches no more result, it will return a false, and thus breaking the loop. $table will be an array of rows of the result returned, where $result is the variable returned by mysql_query().

After you have gotten the results in an array, you can free the $result variable as it might occupy the system memory. To free the result, use mysql_free_result().

- $result is the variable that is returned by mysql_query. function does nothing if variable is false.

When you are done with everything on MySQL, you can use mysql_close() to close the connection. Connection is automatically closed when the script terminates.

- where $link is the connection information returned by mysql_connect(). function closes last opened connection if $link is not specified.

No comments: