MySQL and PHP

From DreamHost
Revision as of 13:41, 12 May 2010 by Javierrod (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Connecting to MySQL with PHP

Standard Connections

The following script creates a MySQL resource, $link, that can be used to make queries:

<?php

$hostname = "";   // eg. mysql.yourdomain.com (unique)
$username = "";   // the username specified when setting-up the database
$password = "";   // the password specified when setting-up the database
$database = "";   // the database name chosen when setting-up the database (unique)

$link = mysql_connect($hostname,$username,$password);
mysql_select_db($database) or die("Unable to select database");

?>

It is a good idea to put this script into a separate file, and then use PHP's include() function to append it to the beginning of any web page that requires database access. There is normally no reason to have more than one database for a website (particularly because there is no theoretical limit to the number of tables per database), so there is rarely a need for more than one connection resource. The connection will close once execution of the script ends, or by invoking the mysql_close() function.

Persistent Connections

An alternative to the normal connection is a persistent connection. mysql_pconnect() first searches for an existing connection that has the same hostname, username, and password. If the function finds one, it returns its identifier instead of trying to open a new connection. The persistent connection will not end with execution of the script - nor will it respect the mysql_close() function.

Executing Queries

Retrieving a Resultset

Given this example table (test_table):

+----+---------------+
| id | test_data     |
+----+---------------+
|  1 | A value       |
|  2 | Another value |
|  3 | Still another |
+----+---------------+

This example script will extract all the data in the table and display it in an HTML table, assuming the connection resource is stored in the $link variable:

<?php

$sql = "SELECT * FROM test_table";
$result = mysql_query($sql,$link) or die("Unable to select: ".mysql_error());
print "<table>\n";
while($row = mysql_fetch_row($result)) {
    print "<tr>\n";
    foreach($row as $field) {
        print "<td>$field</td>\n";
    }
    print "</tr>\n";
}
print "</table>\n";
mysql_close($link);

?>

See Also

External Links