PHP and MySQL

Database connection, insert from PHP, select and show in PHP

MVC and PHP

What is MVC?

MVC stands for Model-view-controller.

It is a software architectural pattern for implementing user interfaces.

The user uses CONTROLLER to manipulate MODEL, the MODEL updates the VIEW for user. Detail can be found here. Model-view-controller

Understanding MVC in PHP One example on MVC using PHP.

PHP Database Connection

PHP provides several ways in obtaining a database connection. You can use a function mysqli to connect MySQL databases. However, in case you need to connect other data sources other than MySQL, additional work for using the function mysqli is necessary. The PHP data object (PDO) provides a way of connecting to various database systems. In this lab, you will learn how to use mysqli to establish a MySQL database connection.

You need to prepare the following information before establishing a MySQL databsed connection in PHP:

The first two steps help you to build a MySQL database connection.

<?php
// (1) Create a MySQL database connection:
// suppose the user name is temp1 and 
// the database you will use is yourusername:

$conn = mysqli_connect("localhost", "temp1", "mypassword", "temp1");

// (2) Check connection: 
// if failed to establish a connection, then exit the php program

if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error());
}

// (3) Run your SQL commands in PHP...

// (4) Always close Database connection:
mysqli_close($conn);
?>
 

Note: You must always remember to close each database connection you established after you have done with your SQL work in PHP.

The following provides details of how to use mysqli function.

MySQL database connection: http://www.w3schools.com/php/php_mysql_connect.asp

Create a table in MySQL

The CREATE TABLE statement is used to create a table in MySQL.

The following code create a table in php, you can name it CreateTable.php

<?php
// (1) Create a MySQL database connection:
$conn = mysqli_connect("localhost", "temp1", "mypassword", "temp1");

// (2) Check connection: 
if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE Person (
ID INT(9) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL
)";

if (mysqli_query($conn, $sql)) {
    echo "\nTable Person created successfully.\n";
} 

else {
    echo "Error creating table: " . mysqli_error($conn);
}
// (4) Always close Database connection:
mysqli_close($conn);
?>
 

To execute the above code in hercules, use command php CreateTable.php at the hercules prompt.

Here is a link on how to use sql to create table. http://www.w3schools.com/php/php_mysql_create_table.asp

Insert from PHP

After establishing a MySQL database connection, you can use a function mysqli_query to execute your SQL command and insert a new record to your database table. The function returns true if the operation is successful, otherwise, it returns false. In case the insert operation failed, you need to show the error message to the user.

<?php
// (1) Create a MySQL database connection:
$conn = mysqli_connect("localhost", "temp1", "mypassword", "temp1");

// (2) Check connection: 
if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error());
}

// (3) Run your SQL command: 
// (3-a) build your SQL string:
// 
// To enter a user defined (specific) record, 
// $sql = "INSERT INTO Person (ID, FirstName, LastName) VALUES('335854', 'Tom', 'Stone')";
// To enter any record
// $sql = "INSERT INTO Person (ID, FirstName, LastName) VALUES('$_POST[ID]', '$_POST[FirstName]', '$_POST[LastName]')";

// $id = $_POST["ID"];
// $fn = $_POST["FirstName"];
// $ln = $_POST["LastName"];
// $sql = "INSERT INTO Person (ID, FirstName, LastName) VALUES('$id', '$fn', '$ln')";

// (3-b) for debugging purpose, you can output your SQL into the webpage 
//or into a log file on hercules. 
//For example, you can: echo "MySQL command is: " . $sql ;

// (3-c) run the sql  
if (mysqli_query($conn, $sql)) {
    echo "A record is successfully added.";
} else { // if failed to add a new record: 
    echo "Error: " . $sql . "
" . mysqli_error($conn); } // (4) Always close Database connection: mysqli_close($conn); ?>

The following link provides the detail.

PHP insert: http://www.w3schools.com/php/php_mysql_insert.asp

The mysqli_real_escape_string() function escapes special characters in a string for use in an SQL statement.

http://www.w3schools.com/php/func_mysqli_real_escape_string.asp

Query and Show Records

In PHP, it's easy to query records from a database and show the results.

<?php
// (1) Create a MySQL database connection:
$conn = mysqli_connect("localhost", "temp1", "mypassword", "temp1");

// (2) Check connection: 
if (!$conn) { 
    die("Connection failed: " . mysqli_connect_error());
}

// (3) Run your SQL command: 
// build your SQL string:
//The isset () function is used to check whether a variable is set or not.
//The isset() function return false if testing variable contains a NULL value. 

if ((isset($_POST["Searchname"])) == "*")

{
$sql = "select * from Person";

echo "My SQL command is: " . $sql . "<br />";

$result = mysqli_query($conn, $sql);

// (4) Output records into an HTML table:
echo "<table border=1>";

// table header:
echo "<tr><th>ID</th><th>FirstName</th><th>LastName</th></tr>";


if (mysqli_num_rows($result)<=0)
{
  
  echo "No Records Found.<br />";

} else { // for each loop, we output a record/row: 

  while ($row = mysqli_fetch_assoc($result))
  {
     echo "<tr>";

     echo "<td>" . $row['ID'] . "</td><td>" . $row['FirstName']. "</td><td>". $row['LastName'] . "</td>";
       
     echo "</tr>";
  }//end while
}  //end else

echo "</table>";
}//end if
// (5) Always close Database connection:
mysqli_close($conn);
?>
 

The following links provide the detail of database operations, such as query, update and delete.

PHP query and show: http://www.w3schools.com/php/php_mysql_select.asp

PHP Update: http://www.w3schools.com/php/php_mysql_update.asp

PHP Delete: http://www.w3schools.com/php/php_mysql_delete.asp

In Class Exercise Lab Assignment

In this assignment, you need to create lab10_SqlForm.html to collect user ID, FirstName and LastName information.

The following picture displays the main idea of the lab10_SqlForm.html page:

You also need to implement two PHP scripts. The first one is CreateTable.php which creates the table Person.

The second php file is sqlphp.php. This php program should have two functions.

The following code is useful for indicating whether or not you have clicked on a specific "submit" button.

if (isset($_POST["Add"])) 
{
   InsertTable(); 
}
else
{
   if(isset($_POST["Query"]))
      SearchTable();
}

The function InsertTable()

The function InsertTable() adds a record into the MySQL database table. You need to collect ID, FirstName and LastName and add the record to the MySQL database. If the InsertTable() operation is successful, you should see a screen similar to the following :

If InsertTable() failed, you also need to display a warning message.

The SearchTable() function

The SearchTable()function is for query records from the table according to the user input.

For SearchTable() function querying records, typing "*" in the input text box labeled with "Search for" returns all records in the database table.

If you select a "field" from the radio button, for example, "FirstName", and input "Brittney", you should display the record with the firstname "Brittney". The following picture shows the results.

To check if the field is empty you can use the following code:

if(isset($_POST["Searchname"]) && trim($_POST["Searchname"]) != "") { .. }
  

If there are matching records, display the results, user ID, FirstName and LastName in an HTML table.

If there are no matching records, display no matching records.

Link your lab10_SqlForm.html on your index.html

Zero will be given if the files are not linked on your index.html

Zip the following files and name it Yourusername_lab10.zip. Upload in URcourses by 11:55 PM.

  1. lab10_SqlForm.html
  2. CreateTable.php
  3. sqlphp.php