PHP and MySQL

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

PHP Sessions

A session is a way to store information (in variables) to be used across multiple pages. Session variables solve this problem by storing user information to be used across multiple pages (e.g. email,username, etc). By default, session variables last until the user closes the browser.

https://www.w3schools.com/php/php_sessions.asp

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 called user 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 user (
user_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_email VARCHAR(255) NOT NULL,
user_password VARCHAR(255) NOT NULL,
user_Name VARCHAR(255) NOT NULL,
user_DofB VARCHAR(255)
)";

if (mysqli_query($conn, $sql)) {
    echo "\nTable user 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.

You can use the following code for SignUp.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());
}

$em = $_POST["email"];
$pd = $_POST["password"];
$un = $_POST["uname"];
$db = $_POST["DOB"];

// (3) Run your SQL command: 
// (3-a) build your SQL string:
// insert one record into the table user

// add code here to insert a record in the user table

// start with $sql =     



  if (mysqli_query($conn, $sql)) 
  {
    $sql = "SELECT user_id FROM user WHERE user_email='".$em."'\n";
    $result = mysqli_query($conn, $sql);
    $row = mysqli_fetch_assoc($result);
    
    // login successful and display username
    echo "<h1>" ."Welcome ". $un."!". "</h1>";
    echo  "<h2>"."Your account has been created successfully."."</h2>";

  } // end if 

 else{
   echo "Error: " . $sql . "<br>" . 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.

You can use the following code for Login.php

<?php
//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["LogIn"]))
	{
	
	$conn = mysqli_connect("localhost", "temp1", "mypassword", "temp1");

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

       
	$email = $_POST["email"];
	$password = $_POST["pswd"];

	// add code here to retrieve a user information if username and password match and 
       // can be found in the database
       // sql command should be: select user email, username, birthday from user table
       // where user email and user password match from the record
       	


		
       //echo "MySQL command is: " . $sql ."<br>" ;
        $result = mysqli_query($conn, $sql);
	$row = mysqli_fetch_assoc($result);
	
	
	if(mysqli_num_rows($result)==0){
		echo "Unable to login <br>";
             

	}
	else{
			

		// add code here to display Username, User email and Date of Birth
	
       	 		      
	    }

	

      }//end of LogIn			
            
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

Step 1: Create table user if you haven't done so.

Step 2: Your SignUp.html calls SignUp.php to collect user information. You need to create SignUp.php

The following picture displays the main idea of the page:

Once the user signup successfully, the following image is displayed.

Step 3: Your Login.html calls Login.php to collect user login information. You need to create Login.php

Link your lab 10 SignUp.html on index.html.

Zip the following files, and submit to URcourses by 11:55 PM.

  1. CreateTable.php
  2. SignUp.php
  3. Login.php