MySQL and PHP

From DreamHost
Jump to: navigation, search

Overview

PHP has built in functions that allow you to connect to your database server to retrieve data. This article details a few examples of using these functions.

Standard Connections

The following script creates a MySQL resource named $link. This resource can then be used to create queries. Below is a very simple example of a PHP connection script. View the PHPMyAdmin article for details on how to find your database credentials :

<?php

$hostname = "mysql.example.com";  //the hostname you created when creating the database
$username = "yourusername";   // the username specified when setting up the database
$password = "yourpassword";    // the password specified when setting up the database
$database = "databasename";   // the database name chosen when setting up the database 

$link = mysqli_connect($hostname, $username, $password, $database);
if (mysqli_connect_errno()) {
   die("Connect failed: %s\n" + mysqli_connect_error());
   exit();

?>

It's 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 mysqli_close() function.

Executing Queries

Retrieving a Resultset

The following example extracts data in the specified table and displays it in an HTML table for you to view. This script is an addition to the script above, so make sure you already have your connection script created. The $link resource will then be used to extract the data.

In this example, a MySQL table named ‘test_data’ contains the following:

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

This script then extracts the data from that table:

<?php

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

?>

See Also

General info-
Connecting to MySQL with PHP-
Executing Queries-