MySQL

From Lankyland

Contents 1 Our Test Database 2 Connect to the Database 3 Displaying the Table Data 3.1 Displaying Random Data 4 Inserting Data

Our Test Database

Within out foo database we have this table which is called contents

Field Name	Field Type	Field Extras
id	INT	auto_incremental
date	DATETIME
body	TEXT

Connect to the Database

This code connects to the MySQL server and then selects the database called foo

<?

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}
?>

Displaying the Table Data

The most common use of a database is in a dynamic website. It allows you to have one PHP file that can display different data that is sourced from a database. Here is the most basic query that can display all the data from the Database foo and the Table contents.

 <?
 $sql = "SELECT * FROM contents";
 $result = mysql_query($sql);
 while($row = mysql_fetch_array($result))
 {
   print var_export($row);
 }
 ?>

Let's go through the lines.

• The First Line
  • SELECT tells MySQL that we want to pull out the data.
  • The * (which is a wildcard) tells MySQL that we want to Select data from every field in the table.
  • FROM contents is a simple command. It tells MySQL that this is the table that we want to Select our data from.
• The second line is where we actually run the query using the mysql_query() function.
• The third line is how we prepare the data for display.
  • We make use of a while() function to assign the extracted data to the variable $row.
  • We then use the mysql_fetch_array() function to order the data into the array called $row
  • As we only want to display exactly what is in the table we just show the array's data using var_export() to print the data

Displaying Random Data

 $sql = "SELECT * FROM contents ORDER BY RAND() LIMIT 1";
 $result = mysql_query($sql);
 while($row = mysql_fetch_array($result))
 {
   print var_export($row);
 }

This will grab one random row and print it.

Inserting Data

To have a truly dynamic website you need to be able to insert data into your database via your website.

 $sql = "INSERT INTO content (id, date, body) VALUES (' ','$date','$body')";
 $result = mysql_query($sql);

As can be seen, putting data in involves a lot less coding than getting data out.

• The First Line
  • INSERT tells MySQL that we are going to be inserting a row into the table
  • INTO content (id, date, body) tells MySQL that we are going to insert this row of data INTO the content table which has the fields id, date and body
  • VALUES (,'$date','$body') tells MySQL what we are inserting into the fields of the new row. We have left the value field for id blank as we have setup the id field to be auto_incremental so we don't need to specify a value
• The second line is where we actually run the query using the mysql_query() function.