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 below so you can use it if you have to,
but it is deprecated and should not be used for new designs
|
This pages discusses the deprecated procedural interface.
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
$connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS);
if(!$connection){
die("Database connection failed: " . mysql_error());
}
$db_select = mysql_select_db(DB_NAME,$connection);
if(!$db_select){
die("Database selection failed: " . mysql_error());
}
?>]]>
|
At this point, it is possible to query the database and populate a form.
I strongly suggest using a separate read/write connection to update the tables.
Closing the database connection
After the page is rendered, this will close the connection.
| <![CDATA[
database_close.php
<?php
// close the database connection
// 07-11-12 start of file
if (isset($connection)){
mysql_close($connection);
}
?>]]>
|
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