PHP and SQL

Overview

Information for this section is based on w3schools.com.

You can use PHP to create tables, delete tables, insert records, remove records, and display records. The basic format of your code is:

Connect to the mysql database. General format is:

$con=mysql_connect("localhost","username","password");  //use your mysql username and password

Select the database that you are working with. On hercules, the database is your userid:
```
mysql_select_db("username", $con); //use your own username
```
Use the mysql_query command to create a table, insert records, display records, etc

Create a Table

<?php
  //File: createTable.php 
  $con = mysql_connect("localhost","username","password");
  if (!$con)
  {
     die('Could not connect: ' . mysql_error());
  }
  echo "Connection worked\n";
  
  mysql_select_db("username", $con);
  
  $sql = "CREATE TABLE Person
  (
  FirstName varchar(15),
  LastName varchar(15),
  ID int
  )";
  
  mysql_query($sql,$con);
  
  mysql_close($con);
?>

This code can be run from the command line:
php createTable.php

Insert Records

<?php
  //File: insertTable.php
  $con = mysql_connect("localhost","username","password");
  if (!$con)
  {
      die('Could not connect: ' . mysql_error());
  }
  echo "Connection worked\n";
  
  mysql_select_db("username", $con);
   
  mysql_query("INSERT INTO Person (FirstName, LastName, ID)
              VALUES('Peter', 'Smith', '333867')", $con);
  
  mysql_query("INSERT INTO Person (FirstName, LastName, ID)
              VALUES('Tom', 'Stone', '335845')", $con);
			  
  mysql_close($con);
?>

Display Records

<?php
  //File: displayRecords.php
  $con = mysql_connect("localhost","username","password");
  if (!$con)
  {
     die('Could not connect: ' . mysql_error());
  }
  
  echo "Connection worked\n";
  
  mysql_select_db("username", $con);
  
  $result = mysql_query("SELECT * FROM Person");
  
  while ($row = mysql_fetch_array($result))
  {
      echo $row['FirstName'] . " " . $row['LastName']. " ". $row['ID'] . "\n";
  }
  
  mysql_close($con);
?>

Some Useful Code Snippets:

The following will tell you how many rows were returned by the query. More information at: ca3.php.net
```
mysql_num_rows($result)
```

The following is useful when using $_POST data in queries. More information at: ca3.php.net

$query=sprintf("SELECT * FROM Person WHERE LastName='%s'",
          mysql_real_escape_string($_POST['search']));
$result = mysql_query($query);

The following is useful to indicate whether or not you have clicked on a specific "submit" button. Assume the name of your button is "Add". The following will test if "Add" has been clicked.
```
if (isset($_POST["Add"])) 
```