Using MySQL and PHP

By Neil Williams
2007-11-10


Connecting to the database

PHP needs to know your username and password to connect to the MySQL database. Remember that the PHP code remains on the server at all times and your username and password are not revealed unless you deliberately output them in the resulting HTML. (Not wise!) The mysql_connect function returns a handler to the connection which you will need each time you want to query the database, so store the handler in a suitable variable like $connection. Also, if the mysql_connect function fails, PHP may give the user a cryptic error message which will confuse users, so hide these default messages using @ in front of the function call and using " or die" to output a sensible error message:

$connection = @mysql_connect("localhost",
"username","password") or die ("Could not connect!");

The $connection variable is now used to select the database to use. You will usually only have access rights to one database on a live internet site although you can configure as many databases as you need for use on an intranet site if you are the system administrator. Each database can contain multiple tables and all tables within the database are accessible from the one database connection, so it makes sense to make multiple tables within the one database instead of opening and closing database connections to connect to tables in separate databases. Store the returned handler for use in debugging your scripts and use @ and "or die" to handle errors cleanly:

$db = @mysql_select_db($dbname, $connection) 
or die("Could not select a valid database.");

On many webhosts, the name of the database ($dbname) will be the same as your username - one user, one database. On intranets, the database name is up to you. Note that you haven't made a connection to a table yet.

The simplest connection to a table is to list the entire contents of the table. Create a variable $sql to hold the text string that will describe the query to make:

$sql = "SELECT * FROM $tablename";

Capitals are only for the purposes of demonstration although some programmers retain capitals to discriminate between SQL commands and data. It makes no difference to the results. The * means all columns, not all rows. i.e. all fields will be returned. You can limit the fields returned by stating the fields by name, separated by commas -

SELECT id,name,email FROM $tablename;

etc. You can limit the number of rows returned (the actual number of data records) by using statements like WHERE to only match those records you are interested in. e.g.

SELECT * FROM $tablename WHERE id = 5

will display ALL fields for only those records where the value of the field named id is equal to exactly 5. Remember this difference between fields and values - the SELECT statement must contain either a list of fieldnames or * but will return results from all records in the table by default.

Tutorial Pages:
» Creating dynamic pages from online databases
» Connecting to the database
» Getting and displaying results
» Adding and updating records


Copyright © Neil Williams

Related Tutorials: » Web Database Access from Desktop Applications » CubeCart 3.0 Installation and Configuration » PHP Site Search Made Easy » Installing and Configuring Drupal 6.1 » Desktop Application Development with PHP-GTK » Installing PHP on Windows