Innovation Creates Miracles

PHP Team have done a great job where the built-in function for MySQL database are awesome!! Thumbs up!! The functions are straight forward and easy to understand.

Defnitely this will not serve the positive results to all users especially those newbies who just starts their interest in PHP programming.

Hope the below hints may helps those who aren’t sure on how to conenct and query data from MySQL databse server via PHP scripts.

Before starts, get ready some information about the built-in php function:

1. mysql_connect : used to connect to MySQL database. This will be the first function initiated before query the database.
2. mysql_db_query : used to parse sql commands to the MySQL database for results
3. mysql_fetch_array : get the data from the records row by row in array format
4. mysql_close : close the connection to MySQL database server once the queries is done

mysql_connect() function

mysql_connect ([ string $server [, string $username [, string $password [, bool $new_link [, int $client_flags ]]]]] )

This function allows 5 parameters where 3 are normally used, that is $server, $username, and $password. From the name itself you know that this is something relates to the database server login information where:

1. $server : mysql server hostname or IP address
2. $username : username to login to MySQL database server which is having over the queried database
3. $password : password for the username mentioned on option “2″

This functions returns link identifier which will be used by the mysql_db_query() function or return FALSE if the connection fails.

The sample mysql connect are as below:

$connlink = mysql_connect(”localhost”,”myusername”,”mypassword”);

mysql_db_query() function

mysql_db_query ( string $database , string $query [, resource $link_identifier ] )

This function by the name itself shown that it has to be related to database query. The function itself cannot query the database if there is no information passed to this function. It will be like you are asking people a favor to get a pair of sock ( data ) of your preference inside a room without stating exact location ( which database or table ) of the socks and what is your preferences (queries).

This function accepts three parameters which will be :

1. $database : database name that you are going to query from
2. $query : your sql statements
3. $link_identifier : tells the function which connection that it should refer. This will be the link identifiers returned by the mysql_connect() function

This function will return Positive MySQL results of the query or FALSE if the query fails.

The sample function used are as below:

$dbresults = mysql_db_query(”mydbname”,”select * from userlogins”,$connlink);

mysql_fetch_array() function

mysql_fetch_array ( resource $result [, int $result_type ] )

This function is to grab data from the MySQL results row by row upon execution. This means that this function will grab data on the first row of records on its first execution, second row of records on second, and so on..

This function allow two parameters where the common parameter passed to the function will be $results which is a compulsory field. This parameters tells the function which MySQL results should it grab the data from. This MySQL result will be from the result returned by the mysql_db_query() function.

This function will return data in the form of array where table’s field name are used as the array index.

Examples:

If the table contains fieldname “username”, the array storage will be either array[0] or array['username'].

If the table contains more than 1 field, like “username” and “password”, the array storage will be mapped as below array style:

array[0] = array['username']
array[1] = array['password']

If you are using integer index for array on data as mentioned as above, please be extra cautious. The Integer index is based on the table field name arrangement in the table when you creates the table. If you are unsure on your field arrangement, you may use the following sql command to check on it:

show fields from tablename;

The field name will be shown in arrangement from top to bottom.

The sample function call are as below:

$dbdata = mysql_fetch_array($dbresults);

If your query returns more than one records, you may consider to use while() loop to query until the function returns FALSE which indicates the end of the records.

mysql_close() function

mysql_close ([ resource $link_identifier ] )

This function is used to close the mysql connection made earlier by mysql_connect() function by using the link_identifier as the reference for the function to know which connection to be closed. This function is recommended to be used together with the mysql_connect to make sure that there is no unused connection is open.

This function returns TRUE if the connection closed successfully or FALSE otherwise.

The sample function call are as below:

mysql_close($connlink);

You should be wondering which function should comes first and which is later. Here is the hints:

mysql_connect() -> mysql_db_query()-> mysql_fetch_array()-> mysql_close()

That will be :

$connlink = mysql_connect(”localhost”,”myusername”,”mypassword”);
$dbresults = mysql_db_query(”mydbname”,”select * from userlogins”,$connlink);
$dbdata = mysql_fetch_array($dbresults);
mysql_close($connlink);

I believe that you are not going to query data once and it will be very long is you keep on pump this 4 lines whenever there is a database query needed. To have a better management, you may consider to create your own custom function to query on the database.

For example:

function myowndbquery($sqlcmd)
{
$connlink = mysql_connect(”localhost”,”myusername”,”mypassword”);
$dbresults = mysql_db_query(”mydbname”,$sqlcmd,$connlink);
mysql_close($connlink);
return $dbresults;
}

Of course this is just a basic idea for custom function. You may need to include error checking with if else statement for better error handling or logging.

I have developed a sample script which may helps you. Click here to download.