MySQL – Database Connections

PHP has inbuilt support for MySql databases. Before installing PHP, you should install MySql first, so that when PHP installs you can point it to your MySql installation otherwise it will use it’s default settings.

DB Connection

To connect to a MySql db you’ll need to use the function mysql_connect and host, username and password as part of the argument eg

$connection = mysql_connect(“hostname”,”username”,”password”);

You can use the die function to offer an alternative option if the connection fails eg

$connection = mysql_connect(“hostname”,”username”,”password”) or die(“Couldn’t connect to MySql”);

With the addition of using the @ symbol to before the function call will suppress warnings which would be passed on the the user eg

$connection = @mysql_connect(“hostname”,”username”,”password”) or die(“Couldn’t connect to MySql”);

Selecting the Database

Once connected you can use your connection to open a database with the function mysql_select_db eg

<?PHP
  $connection = mysql_connect("hostname","username","password")
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $db = mysql_select_db($myDB,$connection) or die("Couldn't
  select database ");
?>

Queries

To run a query use mysql_query which takes a a SQL statement and a connection as the argument eg

<?PHP
  $connection = mysql_connect("hostname","username","password")
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $db = mysql_select_db($myDB,$connection) or die("Couldn't
  select database ");
  $sql = "SELECT * FROM users;";
  $result = mysql_query($sql,$connection) or die("Couldn't
  execute query. ");
?>

The records returned are placed in $result which is an array or recordset.

Reading Returned Records

To read the records returned from a query use mysql_fetch_array which takes the returned array as the argument eg

<?php
  $connection = mysql_connect("hostname","username","password")
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $connection = mysql_select_db($myDB,$connection) or
  die("Couldn't select database ");
  $sql = "SELECT * FROM users;";
  $result = mysql_query($sql,$connection) or die("Couldn't
  execute query. ");
  while ($row = mysql_fetch_array($result)){
  echo $row["firstname"];
  echo $row["lastname"];
  echo $row["email"];
  }
?>

MySQL – Inserting

Inserting Data

To insert data you would also use mysql_query eg

<?PHP
  $connection = mysql_connect("hostname","username","paswword")
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $connection = mysql_select_db($myDB,$connection) or
  die("Couldn't select database ");
  $sql = "INSERT INTO users(firstname,lastname,email)VALUES($first,$last,$email);";
  $result = mysql_query($sql,$connection) or die("Couldn't
  execute query. ");
?>

Unfortunately as SQL is written using english you’ll need to escape the values being written if held in a variable otherwise the insert statement will insert the variable name instead of the value eg

<?PHP
   $connection = mysql_connect("hostname","username","paswword")
  or die("Couldn't connect to MySql");
  $myDB= "users";
  $connection = mysql_select_db($myDB,$connection) or
  die("Couldn't select database ");
  $sql = "INSERT INTO users(firstname,lastname,email)VALUES(\"$first\",\"$last\",\"$email\");";
  $result = mysql_query($sql,$connection) or die("Couldn't
  execute query. ");
?>

next

MySQL – Database Functions

You’ll find brief descriptions of the following functions helpful when working with a MySql database.

mysql_list_tables

Using this function will return a pointer eg

$tables = mysql_list_tables($mydb);

mysql_list_tablename

Using this function will return a table’s name from a database. It must be used in conjunction with mysql_list_tables to be able to extract the table name eg

$tables = mysql_list_tables($mydb);
for ($i < $mysql_num_rows ($tables)){
$tablename = mysql_tablename($tables,$i);
echo $tablename;
$i++;
}

mysql_list_fields

Using this function will return a pointer to list a table’s fields eg

$fieldinfo = mysql_list_fields($mydb,$tablename);

mysql_num_rows

Using this function will return the number of rows from a query eg

$numrows = mysql_num_rows($result);

mysql_num_fields

Using this function will return the number of fields in a result set eg

$numfields = mysql_num_fields($result);

mysql_field_name

Using this function will return the name of a field based on a result and an index number eg

$fieldname = mysql_field_name($result,2);