Before learning how to interact with a MySQL database using PHP, you must first be aware of a special type of variable in PHP known as a resource. A resource is essentially a pointer to some external entity that PHP has the ability to interact with. You will never have to display or manipulate a resource directly – instead, resources are stored in variables and passed to other functions related to the function that returned the resource.
PHP’s MySQL functions make heavy use of resoruces. A connection to a MySQL database is represented by a resource, and SQL queries run on that database return a resource that references the set of results returned by that query. If this all sounds complicated don’t worry – the actual syntax used to deal with MySQL databases is surprisingly simple.
To run SQL commands we will use a PHP library called mysqli, the improved version of mysql library.
Connecting
In order to run queries on an external MySQL database, you must first establish a connection to that database from within your script. To do this, you will need to know the location of the database server and your username and password for accessing that server. In our examples we will connect to the local server you get when you install WAMP or MAMP
STEP 1. Connect to a server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php //setting CONSTANTS for host, user, password DEFINE('DB_HOST', 'localhost'); DEFINE('DB_USER', 'root'); DEFINE('DB_PASS', ''); ; //'root' as a default in MAMP servers //storing the result of the connection (mysqli $link) in a variable $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASS) or die('could not connect: '. mysqli_connect_error() ); /** * most of the mysqli function will ask the mysqli $link ($dbc) as a first parameter */ ?> |
The variable $dbc (DataBaseConnection) will now contain a resource which references the connection you have just created.
Selecting the Database
A MySQL server can host any number of individual databases, each with a unique name. You must now “select” the database you wish to access.
STEP 2. Select your DB:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php //setting CONSTANTS for host, user, password DEFINE('DB_HOST', 'localhost'); DEFINE('DB_USER', 'root'); DEFINE('DB_PASS', ''); ; //'root' as a default in MAMP servers //storing the result of the connection (mysqli $link) in a variable $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASS) or die('could not connect: '. mysqli_connect_error() ); //storing the name of the database you want to work with DEFINE('DB_NAME', 'yourDatabaseName'); //selecting the database mysqli_select_db($dbc, DB_NAME) or die('could not select db'); ?> |
Should both of the above statements succeed, you will have established a connection to your database!
From now on we will refer to the mysli $link resource as $dbc.
die() is a php construct you can use to terminate a script. You can say die() is an alias of exit().