Languages - PHP
MySQL Table Connections
How to connect to MySQL tables via PHP.
There are
3 different API's
| mysqli
| MySQL Improved Extension
This provides both an object oriented interface and a procedural (simple function call)
interface.
|
| PDO
| PHP Data Objects
This makes your code database independent. Using this you can
change from MySQL to another database type without having to change you code.
(We all know that isn't true, but it is a nice thought.)
|
| classic
| This uses function calls, no objects - I have described it
on a separate page
so you can use it if you have to,
but it is deprecated and should not be used for new designs.
|
API Examples
The main MySQL pages provide
examples of the 3 API's
|
<![CDATA[<?php
// mysqli
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);
// PDO
$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);
// mysql
$c = mysql_connect("example.com", "user", "password");
mysql_select_db("database");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);
?>]]>
|
Connecting to the database
These scripts will make a database connection.
The constants simplify moving the database from a development system
to a production system on another computer - only one file has to change.
Typically, the user name and password will be different for security reasons.
In addition,
the prefix test_ will be removed from the database name.
| <![CDATA[
database_constants.php
<?php
// constants used in generic database connection - database_connect.php
// 07-11-12 start of file
define ("DB_SERVER", "localhost");
define ("DB_USER", "read_only"); // the typical user can not modify anything
define ("DB_PASS", "@@@@"); // this is a security problem
define ("DB_NAME", "test_db_name");
?>]]>
|
This code makes the actual connection.
| <![CDATA[
database_connect.php
<?php
// generic database connection
// 07-11-12 start of file
$database = new mysqli(DB_SERVER,DB_USER,DB_PASS, DB_NAME);
if (mysqli_connect_error()) {
printf("Connect failed: %s\n", mysqli_connect_error());
die();
}
?>]]>
|
This is a bit different, based on
mysqli_connect_errno help.
The at sign (@) in front of new means to
ignore all errors.
This uses object syntax to get the error.
| <![CDATA[
<?php
$database = @new mysqli(DB_SERVER,DB_USER,DB_PASS, DB_NAME);
if ($database->connect_error) {
die("Connect failed: " . $database->connect_error . "\n");
}
?>]]>
|
$connection->connect_error will not work with PHP 5.2.9 and below ..
use the procedural method in the previous example instead.
At this point, it is possible to query the database and populate a form.
I strongly suggest using a readonly connection for display and
a separate read/write connection to update the tables.
Closing the database connection
After the page is rendered, this will close the connection.
| <![CDATA[
<?php
if (isset($database)){
$database->close();
}
?>]]>
|
Call Sequence
Code similar to the following will include the scripts in the correct order
to connect to the database,
and, after the page has rendered, disconnect from it.
While disconnecting is optional, it is also good programming practice
- always explicitly close what you open.
|
<![CDATA[<?php
require_once("includes/database_constants.php");
require_once("includes/database_connect.php");
require_once("includes/functions.php");
require_once("includes/header.php");
?>
<h1>Main page goes here</h1>
<?php require_once("includes/footer.php"); ?>
<?php require_once("includes/database_close.php"); ?>]]>
|
Author:
Robert Clemenzi