PHP MySQL Insert, Delete, Update, and Select Statements

From w3cyberlearnings

1 Require
2 PHP Connect to MySQL
- 2.1 Connect to MySQL Database (Connect.php)
3 Create Database
4 PHP Delete Database
5 PHP Create Table
6 PHP Delete Table
7 PHP Insert Record
8 PHP Insert From Array
9 PHP Insert From File using LOAD DATA LOCAL INFILE
- 9.1 student.txt File Content
- 9.2 PHP program to insert file to the table
10 PHP Update Record
11 PHP Delete Record
12 PHP Select or Query Record
- 12.1 Display Result
13 PHP Select Multiple Tables
- 13.1 Create another table (student_subject table)
- 13.2 How to query multiple tables
- 13.3 Multiple Queries
- 13.4 Display Result
14 Filter return result using WHERE clause
- 14.1 List All Subjects
- 14.2 Code(subject_table.php)
- 14.3 Display Result
15 PHP Select Order By
- 15.1 List All Students Without Using Order By Clause
- 15.2 Code (student_list.php)
- 15.3 Display Result
16 INNER JOIN for joining multiple tables
- 16.1 Join Multiple Tables
- 16.2 Join multiple table using the INNER JOIN
- 16.3 Display Result
17 Join multiple tables using LEFT JOIN
- 17.1 Display Result
18 Select multiple tables using RIGHT JOIN
- 18.1 Display Result
19 PHP Select Group By
- 19.1 Code (groupByExample.php)
- 19.2 Display Result
20 Filter record using HAVING
- 20.1 Code (GroupBy.php)
21 Select the largest score
- 21.1 Code (insert_score.php)
- 21.2 Get the maximum score
- 21.3 Display Result
22 PHP Select MIN
- 22.1 Code (MixScore.php)
- 22.2 Display Result
23 PHP Select Avg
- 23.1 Code (avgScore4EachSubject.php)
- 23.2 Display Result
24 Sum or total records
- 24.1 Code (TotalScore4EachSubject.php)
- 24.2 Display Result
25 PHP Insert Date
- 25.1 Insert sample records to the mycalendar table
- 25.2 How to Insert/Update/Add/Delete FROM DATE,DATETIME
26 PHP Select Date
- 26.1 Code(SelectMyCalendar.php)
- 26.2 Display Result

Require

PHP, MySQL, Apache Web Server, Linux OS, and Eclipse or Netbean IDE
Understand basic PHP, HTML, and MySQL

PHP Connect to MySQL

Connect to the database required: user-name, password, database-name, and host name.

$host = "HOST NAME";
$username = "USER NAME";
$password = "PASSWORD ";

Connect to MySQL Database (Connect.php)

Display A connection to the MySQL could not be established! for unsuccessful connect to the database.
Dispaly Connect to MySQL is successful! for successful connect to the database.

<?php
  // connect to mysql parameters
  $host ='localhost';
  $username = 'username';
  $password= 'userpassowrd';

  // connect to mysql server
  $connection= mysql_connect ($host, $username, $password)
          or die("Unable to connect to the MySQL Server!");

  // check connect
  if (!$connection) {
     die ("A connection to the MySQL could not be established!");
  }
  else {
      echo "Connect to MySQL is successful!";
  }
  mysql_close($connection);
?>

Create Database

Create MySQL database name called: school200

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// query to create the school database
$create_database = "CREATE DATABASE IF NOT EXISTS school200";

// call this function to create the database
if (!mysql_query($create_database, $connection)) {
    echo "Can't create database: " . mysql_error($connection);
} else {
    echo "You have successfully created the database name school200";
}

// close connection
mysql_close($connection);
?>

PHP Delete Database

How to delete a database..

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

$deldatabase = "DROP DATABASE school200";

if (!mysql_query($deldatabase, $connection)) {
    echo "Can't delete database: " . mysql_error($connection);
} else {
    echo "You have successfully deleted database name school200";
}

// close connection
mysql_close($connection);
?>

PHP Create Table

Create a database table: student

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// student table
$create_table = 
                'CREATE TABLE IF NOT EXISTS student  (
                        student_id INT NOT NULL AUTO_INCREMENT,
                        first_name VARCHAR(30) NOT NULL,
                        last_name VARCHAR(30) NOT NULL,
                        PRIMARY KEY(student_id)
                  ) ENGINE=InnoDB';
// call this function to create the table
if (!mysql_query($create_table, $connection)) {
    echo "Can't create table: " . mysql_error($connection) . "<br/>";
} else {
    echo "You have successfully create student table <br/>";
}

// close connection
mysql_close($connection);
?>

PHP Delete Table

Delete a table will delete all the records in the table.

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");


// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// student table
$delete_table = 'DROP TABLE IF EXISTS student';

// call this function to delete table
if (!mysql_query($delete_table, $connection)) {
    echo "Can't delete table: " . mysql_error($connection);
} else {
    echo "You have successfully delete student table";
}
// close connection
mysql_close($connection);
?>

PHP Insert Record

Insert record to the student table

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// insert to the student table query
$insert_query = '
                    INSERT INTO student (first_name, last_name)
                    VALUES
                    ("Johny","Zhar"),
                    ("Jacob","Milli"),
                    ("Isabella","Catous"),
                    ("Michael","Vieva"),
                    ("Emma","lee")
                ';
// insert to student table

if (!mysql_query($insert_query, $connection)) {
    echo "Can't insert student record : " . mysql_error($connection);
} else {
    echo "You have successfully insert student record into student table";
}

// close connection
mysql_close($connection);
?>

PHP Insert From Array

Insert records from array
First loop through the array, and do the insert

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// student array
$student_record = array(
    array("Peeou", "Zhou"),
    array("Isabel", "Peom"),
    array("Sophal", "Tang"),
    array("Chong", "Mok"),
    array("Ebo", "Kaun"));

for ($i = 0; $i < count($student_record); $i++) {
    // build a sql statement for insert
    $insert_sql = sprintf("INSERT INTO student (first_name, last_name) VALUES(\"%s\",\"%s\")",
                    $student_record[$i][0], $student_record[$i][1]);
    // insert to the student table
    mysql_query($insert_sql, $connection) or die(mysql_error($connection));
    echo "id: " . mysql_insert_id() ;
}

// close connection
mysql_close($connection);
?>

PHP Insert From File using LOAD DATA LOCAL INFILE

student.txt File Content

This is the file content to be inserted into the table
Separate first name and last with -.
Separate record with :.

lee-salina:so-pheank:lee-lyekwan:otam-kiloma

PHP program to insert file to the table

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// insert to the student table query
$insert_query = "LOAD DATA LOCAL INFILE 'student.txt' 
                INTO table student
                FIELDS TERMINATED BY '-'
                LINES TERMINATED BY ':'
                (last_name,first_name)
                ";

if (!mysql_query($insert_query, $connection)) {
    echo "Can't insert student record : " . mysql_error($connection);
} else {
    echo "You have successfully insert student record into student table";
}
?>

PHP Update Record

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$update_query = 'UPDATE student SET first_name="lili", last_name="lee" WHERE student_id IN (1,2,20)';

if (!mysql_query($update_query, $connection)) {
    echo "Can't update student table: " . mysql_error($connection);
} else {
    echo "You have successfully update the student table";
}

// close connection
mysql_close($connection);
?>

PHP Delete Record

Delete Record From Student Table
When you want to delete a record, you can delete it by using a DELETE STATEMENT.
You have to specify a specific row that you want to delete by using the row unique ID.

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// Delete statement
$delete_query = 'DELETE FROM student WHERE student_id=5';

if (!mysql_query($delete_query, $connection)) {
    echo "Can't delete from student table: " . mysql_error($connection);
} else {
    echo "You have successfully deleted a student from the student table";
}

// close connection
mysql_close($connection);
?>

PHP Select or Query Record

We retrieve all students record from student table, and generate a table list.

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select teacher
$sel_query = 'SELECT first_name, last_name FROM student';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>First Name</th><th>Last Name</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';
// close connection
mysql_close($connection);

?>

Display Result

First Name	Last Name
Johny	Zhar
Jacob	Milli
Isabella	Catous
Michael	Vieva
Emma	lee
salina	lee
pheank	so
lyekwan	lee
kiloma	otam

PHP Select Multiple Tables

Query Multiple Tables
We are going to create two more tables, and insert some data into those tables.
Table name: subject
Insert sample records.

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection)
        or die(mysql_error($connection));

// create subject table
$subject_table = 
   'CREATE TABLE IF NOT EXISTS subject
   (    subject_id INT NOT NULL AUTO_INCREMENT,
        credit_hour INT NOT NULL,
        subject_name VARCHAR(200) NOT NULL,
        PRIMARY KEY(subject_id)
   )ENGINE=InnoDB';

// call this function to create subject table
if (!mysql_query($subject_table, $connection)) {
    echo "Can't create table: " . mysql_error($connection) . "<br/>";
} else {
    echo "You have successfully created subject table <br/>";
}

// insert to the subject table query
$insert_query = '
                    INSERT INTO subject (subject_id, credit_hour, subject_name)
                    VALUES
                    (null,3,"C++"),
                    (null,3,"System Analysis"),
                    (null,2,"Web and HTML"),
                    (null,3,"PHP and Ajax"),
                    (null,2,"Java"),
                    (null,3,"Perl OOP")
                ';
// insert to subject table

if (!mysql_query($insert_query, $connection)) {
    echo "Can't insert subject record : " . mysql_error($connection);
} else {
    echo "You have successfully inserted into subject table  <br/>";
}

// close connection
mysql_close($connection);
?>

Create another table (student_subject table)

Table name: student_subject
Insert some sample records

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection)
        or die(mysql_error($connection));


// create student subject table
$student_subject_table = 'CREATE TABLE IF NOT EXISTS student_subject
    (   student_subject_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        student_id  INT NOT NULL,
        subject_id  INT NOT NULL
    )ENGINE=InnoDB';

// call this function to create student_subject table
if (!mysql_query($student_subject_table, $connection)) {
    echo "Can't create table: " . mysql_error($connection) . "<br/>";
} else {
    echo "You have successfully created student_subject table <br/>";
}

// insert to the student_subject table query
$insert_query = '
                    INSERT INTO student_subject (student_subject_id, student_id, subject_id)
                    VALUES
                    (null,1,1),
                    (null,1,2),
                    (null,1,4),
                    (null,1,5),

                    (null,2,1),
                    (null,2,2),
                    (null,2,4),
                    (null,2,5),

                    (null,3,1),
                    (null,3,2),
                    (null,3,4),
                    (null,3,5),

                    (null,4,1),
                    (null,4,2),
                    (null,4,3),
                    (null,4,6),


                    (null,5,1),
                    (null,5,2),
                    (null,5,3),
                    (null,5,6),


                    (null,6,1),
                    (null,6,2),
                    (null,6,3),
                    (null,6,6),


                    (null,7,1),
                    (null,7,2),
                    (null,7,3),
                    (null,7,6),


                    (null,8,1),
                    (null,8,4),
                    (null,8,3),
                    (null,8,5),

                    (null,9,1),
                    (null,9,4),
                    (null,9,3),
                    (null,9,5)

                ';
// insert to student_subject table

if (!mysql_query($insert_query, $connection)) {
    echo "Can't insert into student_subject table : " . mysql_error($connection);
} else {
    echo "You have successfully inserted into student_subject table  <br/>";
}

// close connection
mysql_close($connection);
?>

How to query multiple tables

Table names: student, subject, student_subject tables.
In this example, we query all the students who take C++ class.
The C++ class has the ID equal to 1

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select teacher
$sel_query = '
    SELECT stu.student_id, 
           CONCAT(stu.first_name," ",stu.last_name) AS "Full Name",
           sub.subject_name, 
           sub.credit_hour
    FROM 
          student stu, 
          subject sub, 
          student_subject ss
    WHERE 
         stu.student_id = ss.student_id AND
         sub.subject_id = ss.subject_id AND
         sub.subject_id = 1;
    ';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Student ID</th><th>Full Name</th><th>Subject Name</th><th>Credit Hour</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>'. $row[0] .'</td>';
    echo '<td>'. $row[1] .'</td>';
    echo '<td>'. $row[2] .'</td>';
    echo '<td>'. $row[3] .'</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Multiple Queries

PHP Programs generates result for student, subject, student_subject table.

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select student table
$sel_query = 'SELECT * FROM student';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<br/>Student Table <br/>';
echo '<table border="1">';
echo '<tr><th>Student ID</th><th>First Name</th><th>Last Name</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';

// select  subject table
$sel_query = 'SELECT subject_id,subject_name,credit_hour FROM subject';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<br/>Student Table <br/>';
echo '<table border="1">';
echo '<tr><th>Subject ID</th><th>Subject Name</th><th>Credit</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';

// select  student_subject table
$sel_query = 'SELECT student_subject_id,subject_id,student_id
        FROM student_subject
        ORDER BY subject_id, student_id';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<br/>student_subject Table <br/>';
echo '<table border="1">';
echo '<tr><th>Student_Subject ID</th><th>subject_id</th><th>student_id</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';

// select students who are taking C++ subject
$sel_query = 'SELECT stu.student_id,
    CONCAT(stu.first_name," ",stu.last_name) AS "Full Name",
    sub.subject_name, sub.credit_hour
    FROM student stu, subject sub, student_subject ss
    WHERE stu.student_id = ss.student_id AND
    sub.subject_id = ss.subject_id AND
    sub.subject_id = 1;
    ';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<br/>Select students who are taking C++ subject <br/>';
echo '<table border="1">';
echo '<tr><th>Student ID</th><th>Full Name</th><th>Subject Name</th><th>Credit Hour</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>'. $row[0] .'</td>';
    echo '<td>'. $row[1] .'</td>';
    echo '<td>'. $row[2] .'</td>';
    echo '<td>'. $row[3] .'</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

Student Table

Student ID	First Name	Last Name
1	Johny	Zhar
2	Jacob	Milli
3	Isabella	Catous
4	Michael	Vieva
5	Emma	lee
6	salina	lee
7	pheank	so
8	lyekwan	lee
9	kiloma	otam

Subject Table

Subject ID	Subject Name	Credit
1	C++	3
2	System Analysis	3
3	Web and HTML	2
4	PHP and Ajax	3
5	Java	2
6	Perl OOP	3

student_subject Table

Student_Subject ID	subject_id	student_id
1	1	1
5	1	2
9	1	3
13	1	4
17	1	5
21	1	6
25	1	7
29	1	8
33	1	9
2	2	1
6	2	2
10	2	3
14	2	4
18	2	5
22	2	6
26	2	7
15	3	4
19	3	5
23	3	6
27	3	7
31	3	8
35	3	9
3	4	1
7	4	2
11	4	3
30	4	8
34	4	9
4	5	1
8	5	2
12	5	3
32	5	8
36	5	9
16	6	4
20	6	5
24	6	6
28	6	7

Select students who are taking C++ subject

Student ID	Full Name	Subject Name	Credit Hour
1	Johny Zhar	C++	3
2	Jacob Milli	C++	3
3	Isabella Catous	C++	3
4	Michael Vieva	C++	3
5	Emma lee	C++	3
6	salina lee	C++	3
7	pheank so	C++	3
8	lyekwan lee	C++	3
9	kiloma otam	C++	3

Filter return result using WHERE clause

Use the WHERE CLAUSE to filter the return data.
WHERE CLAUSE can be used in SELECT STATEMENT, UPDATE STATEMENT, and DELETE STATEMENT.

List All Subjects

Subject Table

Subject ID	Subject Name	Credit
1	C++	3
2	System Analysis	3
3	Web and HTML	2
4	PHP and Ajax	3
5	Java	2
6	Perl OOP	3

Code(subject_table.php)

Select only subject has 3 credit hours

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select student who has first name as salina, and last name as lee
$sel_query = 'SELECT * FROM subject WHERE credit_hour=3';

$result = mysql_query($sel_query, $connection) or die(mysql_errno());
echo '<table border="1">';
echo '<tr><th>Subject ID</th><th>Credit Hour</th><th>Subject Name</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';

// close connection
mysql_close($connection);
?>

Display Result

Subject ID	Credit Hour	Subject Name
1	3	C++
2	3	System Analysis
4	3	PHP and Ajax
6	3	Perl OOP

PHP Select Order By

List All Students Without Using Order By Clause

First Name	Last Name
Johny	Zhar
Jacob	Milli
Isabella	Catous
Michael	Vieva
Emma	lee
salina	lee
pheank	so
lyekwan	lee
kiloma	otam

Code (student_list.php)

Query the student table, and the return record by using ORDER BY last name, and first name.
In the last name, the return data start from Catous, lee, Mili, the return data is alphabetical order.

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select student order by first name, and last name
$sel_query = 'SELECT first_name, last_name FROM student ORDER  BY last_name,first_name';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>First Name</th><th>Last Name</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

First Name	Last Name
Isabella	Catous
Emma	lee
lyekwan	lee
salina	lee
Jacob	Milli
kiloma	otam
pheank	so
Michael	Vieva
Johny	Zhar

INNER JOIN for joining multiple tables

Join Multiple Tables

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select students who are taking Perl OOP class
$sel_query = '
    SELECT stu.student_id,
           CONCAT(stu.first_name," ",stu.last_name) AS "Full Name",
           sub.subject_name,
           sub.credit_hour
    FROM student stu,
         subject sub,
         student_subject ss
    WHERE
        stu.student_id = ss.student_id AND
        sub.subject_id = ss.subject_id AND
        sub.subject_id = 6;
    ';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Student ID</th><th>Full Name</th><th>Subject Name</th><th>Credit Hour</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>'. $row[0] .'</td>';
    echo '<td>'. $row[1] .'</td>';
    echo '<td>'. $row[2] .'</td>';
    echo '<td>'. $row[3] .'</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Join multiple table using the INNER JOIN

This example is the same as the previous example.
In this example uses the INNER JOIN to join three tables together.
We get the same result as the previous SELECT STATEMENT.

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select students who are taking Perl OOP class (using inner join)
$sel_query = '
        SELECT
            stu.student_id,
            CONCAT(stu.first_name," ", stu.last_name) AS "Full Name",
            sub.subject_name,
            sub.credit_hour
        FROM
            student stu INNER JOIN student_subject ss
                   ON stu.student_id = ss.student_id
                        INNER JOIN subject sub
                   ON ss.subject_id = sub.subject_id
       WHERE sub.subject_id = 6';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Student ID</th><th>Full Name</th><th>Subject Name</th><th>Credit Hour</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>'. $row[0] .'</td>';
    echo '<td>'. $row[1] .'</td>';
    echo '<td>'. $row[2] .'</td>';
    echo '<td>'. $row[3] .'</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

Student ID	Full Name	Subject Name	Credit Hour
4	Michael Vieva	Perl OOP	3
5	Emma lee	Perl OOP	3
6	salina lee	Perl OOP	3
7	pheank so	Perl OOP	3

Join multiple tables using LEFT JOIN

LEFT OUTER JOIN is based on the left table, and it displays null for the right table if the join condition is not matched.
Get all students who take PHP and Ajax subject, and as well as students who are not taking any subjects.
There are 9 students, and there are 5 out of 9 students who are taking the PHP and Ajax subject.
Four other students (Michael Vieva, Emma lee, salina lee, and pheank so) are not taking the PHP and Ajax subject.

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select students who are taking PHP and AJax subject 
// subject_id=4

$sel_query = '
    SELECT stu.student_id,
           CONCAT(stu.first_name, " ",stu.last_name) AS "full name",
           ss.subject_id
    FROM student stu
        LEFT OUTER JOIN student_subject ss
             ON stu.student_id = ss.student_id
             AND ss.subject_id = 4';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Student ID</th><th>Full Name</th><th>Subject ID</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>' . $row[0] . '</td>';
    echo '<td>' . $row[1] . '</td>';
    echo '<td>' . (empty($row[2]) ? "NULL" : $row[2]) . '</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

Student ID	Full Name	Subject ID
1	Johny Zhar	4
2	Jacob Milli	4
3	Isabella Catous	4
4	Michael Vieva	NULL
5	Emma lee	NULL
6	salina lee	NULL
7	pheank so	NULL
8	lyekwan lee	4
9	kiloma otam	4

Select multiple tables using RIGHT JOIN

The RIGHT JOIN to join two tables based on the right table.
All records on the right table will display, and records on the left table will display as "NULL" when the condition does not matched.
In this program, we try to get information between subject and student relation.
Which subject that student_id=4 does not take

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// what subject does a student_id=4 not taking?
$sel_query = '
     SELECT ss.student_subject_id,
            ss.student_id,
            ss.subject_id,
            
            st.subject_name,
            st.credit_hour
        FROM student_subject ss RIGHT OUTER JOIN subject st
            ON ss.subject_id = st.subject_id
            AND ss.student_id=4';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>student subject Id</th><th>student_id</th><th>subject_id</th><th>subject name</th><th>credit hour</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>' . (!empty($row[0]) ? $row[0] : 'NULL') . '</td>';
    echo '<td>' . (!empty($row[1]) ? $row[1] : 'NULL') . '</td>';
    echo '<td>' . (!empty($row[2]) ? $row[2] : 'NULL') . '</td>';
    echo '<td>' . $row[3] . '</td>';
    echo '<td>' . $row[4] . '</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

student subject Id	student_id	subject_id	subject name	credit hour
13	4	1	C++	3
14	4	2	System Analysis	3
15	4	3	Web and HTML	2
NULL	NULL	NULL	PHP and Ajax	3
NULL	NULL	NULL	Java	2
16	4	6	Perl OOP	3

PHP Select Group By

GROUP BY uses to group return data.

Code (groupByExample.php)

In this example, we use the group by to group the return data based on the subject id.

<?php

// connect to mysql parameters
$host =     'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$sel_query = 'SELECT subject_id,
    (SELECT su.subject_name FROM subject su WHERE su.subject_id = student_subject.subject_id),
    COUNT(student_id) AS "Total Student"
                FROM student_subject GROUP BY subject_id';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Subject Id</th><th>Subject Name</th><th>Total Student</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>' . $row[0] . '</td>';
    echo '<td>' . $row[1] . '</td>';
    echo '<td>' . $row[2] . '</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

Subject Id	Subject Name	Total Student
1	C++	9
2	System Analysis	7
3	Web and HTML	6
4	PHP and Ajax	5
5	Java	5
6	Perl OOP	4

Filter record using HAVING

HAVING clause uses to filter the GROUP BY.
It returns data when the total student is greater than 5.

Code (GroupBy.php)

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// select students who are taking PHP and AJax subject (subject_id=4)
$sel_query = 'SELECT subject_id,
    (SELECT su.subject_name FROM subject su WHERE su.subject_id = student_subject.subject_id),
    COUNT(student_id) AS "Total Student"
                FROM student_subject GROUP BY subject_id HAVING COUNT(student_id) > 5';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>Subject Id</th><th>Subject Name</th><th>Total Student</th></tr>';
while ($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo '<td>' . $row[0] . '</td>';
    echo '<td>' . $row[1] . '</td>';
    echo '<td>' . $row[2] . '</td>';
    echo '</tr>';
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Select the largest score

Create a new table
Table name: score
Insert some sample records

Code (insert_score.php)

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

// score table
$create_table = 'CREATE TABLE IF NOT EXISTS score  (
                        score_id INT NOT NULL AUTO_INCREMENT,
                        score DOUBLE NOT NULL,
                        subject varchar(20),
                        PRIMARY KEY(score_id)
                  ) ENGINE=InnoDB';

// call this function to create the score table
if (!mysql_query($create_table, $connection)) {
    echo "Can't create table: " . mysql_error($connection) . "<br/>";
} else {
    echo "You have successfully create score table <br/>";
}

// insert query insert into score table
$insert_query = "
                    INSERT INTO score (score, subject)
                    VALUES
                    (42, 'English'),
                    (53,'MATH'),
                    (32,'PHP'),
                    (78,'MATH'),
                    (80,'JAVA'),
                    (67,'MATH'),
                    (89,'C++'),
                    (56,'MATH'),
                    (63,'PYTH'),
                    (82,'MATH'),
                    (89,'C++'),
                    (89,'JAVA'),
                    (91,'C++'),
                    (79,'C++'),
                    (89,'PHP'),
                    (93,'PHP'),
                    (81,'PERL'),
                    (82,'C++'),
                    (78,'MySQL'),
                    (67,'C++'),
                    (83,'MySQL'),
                    (67,'C++'),
                    (85,'MySQL'),
                    (54,'C++'),
                    (78,'MySQL'),
                    (34,'C++')
                    ";
// insert to score table
if (!mysql_query($insert_query, $connection)) {
    echo "Can't insert score record : " . mysql_error($connection);
} else {
    echo "You have successfully insert into score table";
}
// close connection
mysql_close($connection);
?>

Get the maximum score

This program displays the maximum score for each subject.
We use the group by "subject" to group the return result according to the subject.

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$query_max = 'SELECT MAX(score), subject FROM score GROUP BY subject';
$result = mysql_query($query_max,$connection) or die(mysql_error());
echo "<table border=\"1\">";
echo "<tr><th>MAX Score</th><th>Subject</th></tr>";
while($row = mysql_fetch_array($result)) {
    echo '<tr><td>'.$row[0]. '</td><td>'. $row[1]. '</td></tr>';
}
echo '</table>';

// close connection
mysql_close($connection);
?>

Display Result

MAX Score	Subject
91	C++
42	English
89	JAVA
82	MATH
85	MySQL
81	PERL
93	PHP
63	PYTH

PHP Select MIN

List all the minimum score for each subject. We use the GROUP BY subject to group each select statement.

Code (MixScore.php)

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$query_min = 'SELECT MIN(score), subject FROM score GROUP BY subject';
$result = mysql_query($query_min,$connection) or die(mysql_error());
echo "<table border=\"1\">";
echo "<tr><th>MIN Score</th><th>Subject</th></tr>";
while($row = mysql_fetch_array($result)) {
    echo '<tr><td>'.$row[0]. '</td><td>'. $row[1]. '</td></tr>';
}
echo '</table>';

// close connection
mysql_close($connection);
?>

Display Result

MINScore	Subject
34	C++
42	English
80	JAVA
53	MATH
78	MySQL
81	PERL
32	PHP
63	PYTH

PHP Select Avg

We want to get average score for each subject, and we use the GROUP BY to group each subject.

Code (avgScore4EachSubject.php)

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$query_avg = 'SELECT AVG(score), subject FROM score GROUP BY subject';
$result = mysql_query($query_avg,$connection) or die(mysql_error());
echo "<table border=\"1\">";
echo "<tr><th>Average Score</th><th>Subject</th></tr>";
while($row = mysql_fetch_array($result)) {
    echo '<tr><td>'. ceil($row[0]). '</td><td>'. $row[1]. '</td></tr>';
}
echo '</table>';

// close connection
mysql_close($connection);
?>

Display Result

Average Score	Subject
73	C++
42	English
85	JAVA
68	MATH
81	MySQL
81	PERL
72	PHP
63	PYTH

Sum or total records

Total all the score for each subject
Use the GROUP BY to group each subject by using SUM keyword to total the score.

Code (TotalScore4EachSubject.php)

<?php
// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$query_a = 'SELECT SUM(score), subject FROM score GROUP BY subject';
$result = mysql_query($query_a,$connection) or die(mysql_error());
echo "<table border=\"1\">";
echo "<tr><th>Total Score</th><th>Subject</th></tr>";
while($row = mysql_fetch_array($result)) {
    echo '<tr><td>'. ceil($row[0]). '</td><td>'. $row[1]. '</td></tr>';
}
echo '</table>';

// close connection
mysql_close($connection);
?>

Display Result

Total Score	Subject
652	C++
42	English
169	JAVA
336	MATH
324	MySQL
81	PERL
214	PHP
63	PYTH

PHP Insert Date

Create a new table
Table name: mycalendar

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection)
        or die(mysql_error($connection));


// create mycalendar table
$mycalendar_table = 'CREATE TABLE IF NOT EXISTS mycalendar
    (   mycalendar_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        wedding_date  DATE DEFAULT 0,
        schedule_date   DATETIME DEFAULT 0,
        access_infor TIMESTAMP DEFAULT 0
    )ENGINE=InnoDB';

// call this function to create mycalendar table
if (!mysql_query($mycalendar_table, $connection)) {
    echo "Can't create table: " . mysql_error($connection) . "<br/>";
} else {
    echo "You have successfully created mycalendar table <br/>";
}

// close connection
mysql_close($connection);
?>

Insert sample records to the mycalendar table

Insert record into the mycalendar table.
In the insert statement, the $i is from 1 to 5,
Insert day: date_add(NOW(),interval $i day)
Insert month: date_add(NOW(),interval $i month)

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");

// select database school
mysql_select_db('school200', $connection)
        or die(mysql_error($connection));

for ($i = 1; $i <= 5; $i++) {
    $sql = "INSERT INTO mycalendar(wedding_date, schedule_date, access_infor)
	VALUES(date_add(NOW(),interval $i day),date_add(NOW(),interval $i month),NOW());";
    if (!mysql_query($sql, $connection)) {
        echo "Error insert into mycalendar " . mysql_error();
    }
    else {
        echo "Insert record into mycalendar <br/>";
    }
}
// close connection
mysql_close($connection);
?>

How to Insert/Update/Add/Delete FROM DATE,DATETIME

// set data with specific date
INSERT INTO mycalendar(mycalendar_id, wedding_date, schedule_date, access_infor)
	VALUES(null,'2001-09-20','2011-04-03 10:30:40','2011-04-03 10:30:40');


// set all data with current date
INSERT INTO mycalendar(mycalendar_id, wedding_date, schedule_date, access_infor)
	VALUES(null,now(),now(),now());

// insert date with curdate() (current date)
INSERT INTO mycalendar(mycalendar_id, wedding_date, schedule_date, access_infor)
	VALUES(null,CURDATE(),now(),now());

// insert date with * delimiter
INSERT INTO mycalendar(mycalendar_id, wedding_date, schedule_date, access_infor)
	VALUES(null,'2010*10*13','2010*10*13 10*10*30','2010*11*22 10*4*20');

// update and add 1 day on the wedding date
UPDATE mycalendar SET wedding_date=DATE_ADD(wedding_date, INTERVAL 1 DAY) WHERE mycalendar_id=1;


// update and substract 5 day from the wedding date
UPDATE mycalendar SET wedding_date=DATE_SUB(wedding_date, INTERVAL 5 DAY) WHERE mycalendar_id=1;


// update and add 30 minutes to the schedule_date
UPDATE mycalendar SET schedule_date=DATE_ADD(schedule_date, INTERVAL 30 MINUTE) WHERE mycalendar_id=1;


// update and add 30 minutes and 10 second to the schedule_date
UPDATE mycalendar SET schedule_date=DATE_ADD(schedule_date, INTERVAL '30:10' MINUTE_SECOND) WHERE mycalendar_id=1;


// update and substract 30 minutes and 10 second to the schedule_date
UPDATE mycalendar SET schedule_date=DATE_SUB(schedule_date, INTERVAL '30:10' MINUTE_SECOND) WHERE mycalendar_id=1;


// update and add 2 hour and 4 day to the schedule_date
UPDATE mycalendar SET schedule_date=DATE_ADD(schedule_date, INTERVAL '4 2' DAY_HOUR) WHERE mycalendar_id=1;


// update and add 3 month to the schedule_date
UPDATE mycalendar SET schedule_date=DATE_ADD(schedule_date, INTERVAL 3 MONTH) WHERE mycalendar_id=1;

// update and add 10 seconds to the schedule_date
UPDATE mycalendar SET schedule_date=DATE_ADD(schedule_date, INTERVAL 10 SECOND) WHERE mycalendar_id=1;

// update and add hour get from schedule date to the schedule date
UPDATE mycalendar SET schedule_date=DATE_ADD(schedule_date, INTERVAL HOUR(schedule_date) HOUR) WHERE mycalendar_id=2;

PHP Select Date

Code(SelectMyCalendar.php)

<?php

// connect to mysql parameters
$host = 'localhost';
$username = 'root';
$password = 'caojiang';

// connect to mysql server
$connection = mysql_connect($host, $username, $password)
        or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school200', $connection) or die(mysql_error($connection));

$sel_query = 'SELECT * FROM mycalendar';

$result = mysql_query($sel_query, $connection) or die(mysql_error($connection));
echo '<table border="1">';
echo '<tr><th>ID</th><th>Wedding Date</th><th>Schedule Date</th><th>Access Date</th></tr>';
while ($row = mysql_fetch_assoc($result)) {
    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>' . $value . '</td>';
    }
    echo "</tr>";
}
echo '</table>';
// close connection
mysql_close($connection);
?>

Display Result

ID	Wedding Date	Schedule Date	Access Date
1	2011-06-21	2011-07-20 02:30:06	2011-06-20 02:30:06
2	2011-06-22	2011-08-20 02:30:06	2011-06-20 02:30:06
3	2011-06-23	2011-09-20 02:30:06	2011-06-20 02:30:06
4	2011-06-24	2011-10-20 02:30:06	2011-06-20 02:30:06
5	2011-06-25	2011-11-20 02:30:06	2011-06-20 02:30:06

Student_Subject ID	subject_id	student_id
1	1	1
5	1	2
9	1	3
13	1	4
17	1	5
21	1	6
25	1	7
29	1	8
33	1	9
2	2	1
6	2	2
10	2	3
14	2	4
18	2	5
22	2	6
26	2	7
15	3	4
19	3	5
23	3	6
27	3	7
31	3	8
35	3	9
3	4	1
7	4	2
11	4	3
30	4	8
34	4	9
4	5	1
8	5	2
12	5	3
32	5	8
36	5	9
16	6	4
20	6	5
24	6	6
28	6	7

Student_Subject ID	subject_id	student_id
1	1	1
5	1	2
9	1	3
13	1	4
17	1	5
21	1	6
25	1	7
29	1	8
33	1	9
2	2	1
6	2	2
10	2	3
14	2	4
18	2	5
22	2	6
26	2	7
15	3	4
19	3	5
23	3	6
27	3	7
31	3	8
35	3	9
3	4	1
7	4	2
11	4	3
30	4	8
34	4	9
4	5	1
8	5	2
12	5	3
32	5	8
36	5	9
16	6	4
20	6	5
24	6	6
28	6	7

Search

PHP MySQL Insert, Delete, Update, and Select Statements

Contents

Require

PHP Connect to MySQL

Connect to MySQL Database (Connect.php)

Create Database

PHP Delete Database

PHP Create Table

PHP Delete Table

PHP Insert Record

PHP Insert From Array

PHP Insert From File using LOAD DATA LOCAL INFILE

student.txt File Content

PHP program to insert file to the table

PHP Update Record

PHP Delete Record

PHP Select or Query Record

Display Result

PHP Select Multiple Tables

Create another table (student_subject table)

How to query multiple tables

Multiple Queries

Display Result

Filter return result using WHERE clause

List All Subjects

Code(subject_table.php)

Display Result

PHP Select Order By

List All Students Without Using Order By Clause

Code (student_list.php)

Display Result

INNER JOIN for joining multiple tables

Join Multiple Tables

Join multiple table using the INNER JOIN

Display Result

Join multiple tables using LEFT JOIN

Display Result

Select multiple tables using RIGHT JOIN

Display Result

PHP Select Group By

Code (groupByExample.php)

Display Result

Filter record using HAVING

Code (GroupBy.php)

Select the largest score

Code (insert_score.php)

Get the maximum score

Display Result

PHP Select MIN

Code (MixScore.php)

Display Result

PHP Select Avg

Code (avgScore4EachSubject.php)

Display Result

Sum or total records

Code (TotalScore4EachSubject.php)

Display Result

PHP Insert Date

Insert sample records to the mycalendar table

How to Insert/Update/Add/Delete FROM DATE,DATETIME

PHP Select Date

Code(SelectMyCalendar.php)

Display Result

Navigation

Web

SQL

MISC

References

Personal tools

Namespaces

Variants

Views

Actions

Student_Subject ID	subject_id	student_id
1	1	1
5	1	2
9	1	3
13	1	4
17	1	5
21	1	6
25	1	7
29	1	8
33	1	9
2	2	1
6	2	2
10	2	3
14	2	4
18	2	5
22	2	6
26	2	7
15	3	4
19	3	5
23	3	6
27	3	7
31	3	8
35	3	9
3	4	1
7	4	2
11	4	3
30	4	8
34	4	9
4	5	1
8	5	2
12	5	3
32	5	8
36	5	9
16	6	4
20	6	5
24	6	6
28	6	7