codeofaninja
website

PHP and MySQL CRUD Tutorial for Beginners

Photo of Mike Dalisay
Modified Thursday, January 30, 2014
by - @ninjazhai
Create, read, update or delete (CRUD) MySQL database records - these are some of the basic things a PHP web programmer must be able to code effortlessly. But if you are a beginner or just want to have a little reference on how these things can be coded, this tutorial post is for you!

This post contains three ways to do this simple PHP CRUD grid. The most updated way is using the PDO extension. MySQLi extension can also be used. And the oldest way is using the MySQL extension.

MySQL extension is not recommended, it has been deprecated since PHP 5.5, so for newer versions of PHP, we have to use either MySQLi (i means improved) or PDO extension. But if you're debugging with an old software, you can still use this reference I provided below

simple php crud tutorial


Contents:

1.0 Program Output
     1.1 Demo Video
     1.2 Screenshots
          1.2.1 Reading Records
          1.2.2 Creating New Record
          1.2.3 Updating a Record
          1.2.4 Deleting a  Record

2.0 File Structure

3.0 Example Database
     3.1 Table Structure
     3.2 Dummy Data

4.0 PHP and MySQL CRUD - PDO Extension
     4.1 PDO: Connect to Database
     4.2 PDO: Create New Record
     4.3 PDO: Read Records
     4.4 PDO: Update a Record
     4.5 PDO: Delete a Record

5.0 PHP and MySQL CRUD - MySQLi Extension
     5.1 MySQLi: Connect to Database
     5.2 MySQLi: Create New Record
     5.3 MySQLi: Read Records
     5.4 MySQLi: Update a Record
     5.5 MySQLi: Delete a Record

6.0 PHP and MySQL CRUD - MySQL Extension
     6.1 MySQL: Connect to Database
     6.2 MySQL: Create New Record
     6.3 MySQL: Read Records
     6.4 MySQL: Update a Record
     6.5 MySQL: Delete a Record

7.0 Links and Literature
     7.1 Source Code Downloads
     7.2 Online Resources
     7.3 The Code of a Ninja Resources

1.0 Program Output

1.1 Demo Video


For those who prefer seeing the output in a video, see the following youtube video. The sample was taken using our MySQLi CRUD code, but it has the same flow and is applicable with our PDO and MySQL code output.



1.2 Screenshots

Some of you prefer seeing the code output in screenshots so I also provide the following images.

1.2.1 Reading Records


Here's the index.php, it shows the list of records using a table. A "create new record" link were also provided if a user wanted to add or insert a new record.



1.2.2 Creating New Record


When the user clicks on "create new record", he will be redirected to our add.php where he can enter the new user's information using an HTML form.



When the user is done and hit the "save" button, a message"Record was saved." can be seen.



As you can see, a new record with first name "Marykris" were added to the list.



1.2.3 Updating a Record


Now, we'll try to edit the record by clicking the "Edit" link right across the record we want to update. Clicking it will show the user edit.php, with an "id" as parameter as you can see in the URL.



When the user is done editing and hit the "edit" button, a message saying "Record was updated." can be seen, confirming the update.



Going back to the list to see if the record were really updated. The "Marykris" record were updated.



1.2.4 Deleting a Record


Now, we'll try to delete a record by clicking the "Delete" link or button on the right side of the row of the record we want to delete. Clicking it will show a pop up saying "Are you sure?" and clicking the "OK" button will delete the record.



The message "Record was deleted." can be seen if the user clicked the "OK" button, confirming the delete operation made. As you can see on the image below, the "Marykris" record were deleted.



2.0 File Structure


Our three PHP and MySQL CRUD codes (PDO, MySQLi, MySQL extensions) will contain the same file structure. Although they have the same file names, they won't have the same code.
  • sql/data.sql - contains the database table structure and sample data used in this project. See section 3.0 for more.
  • libs/db_connect.php - used for database connection and configuration. 
  • add.php - used for creating a new record. It contains an HTML form where the user can enter details for a new record. 
  • index.php - used for reading records from the database. It uses an HTML table to appropriately display the data retrieved from the MySQL database.
  • edit.php - used for updating a record. It uses an HTML form automatically filled out with data based on the given "id" parameter.
  • delete.php - used for deleting a record. It accepts and "id" parameter and deletes the record with the given id. Once the delete query was executed, it will redirect the user to the index page.

    3.0 Example Database


    3.1 Table Structure

    Here's the table structure we are going to use.

    --
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

    3.2 Dummy Data

    ...and here's the example data.

    --
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`, `modified`) VALUES
(28, 'John', 'Dalisay', '', 'john', 'john123', '2012-01-15 07:26:14'),
(39, 'Jem', 'Panlilio', '', 'jemboy09', 'jem123', '2012-01-15 07:26:46'),
(40, 'Darwin', 'Dalisay', '', 'dada08', 'dada123', '2012-01-15 07:25:34'),
(46, 'Jaylord', 'Bitamug', '', 'jayjay', 'jay123', '2012-01-15 07:27:04'),
(49, 'Justine', 'Bongola', '', 'jaja', 'jaja123', '2012-01-15 07:27:21'),
(50, 'Jun', 'Sabayton', '', 'jun', 'jun123', '2012-02-05 10:15:14'),
(51, 'Lourd', 'De Veyra', '', 'lourd', 'lourd123', '2012-02-05 10:15:36'),
(52, 'Asi', 'Taulava', '', 'asi', 'asi123', '2012-02-05 10:15:58'),
(53, 'James', 'Yap', '', 'james', 'jame123', '2012-02-05 10:16:17'),
(54, 'Chris', 'Tiu', '', 'chris', 'chris123', '2012-02-05 10:16:29');

    4.0 PHP and MySQL CRUD - PDO Extension

    PDO is a more updated extension and it is actively developed by the PHP community. There are lots of reasons why you should use PDO in your projects. It helps you protect from SQL injection, lots of database support including IBM DB2, Oracle, and more.

    4.1 PDO: Connect to Database

    db_connect.php - answers how to connect to MySQL database with PDO?

    <?php
$host = "localhost";
$db_name = "your_db_name";
$username = "your_db_username";
$password = "your_db_password";

try {
    $con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// to handle connection error
catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}
?>

    4.2 PDO: Create New Record

    add.php - answers how to create a record with PDO?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>PDO Create Record - http://codeofaninja.com/</title>
    
    </head>
<body>

<h1>PDO: Add a Record</h1>

<?php
$action = isset($_POST['action']) ? $_POST['action'] : "";

if($action=='create'){
    //include database connection
    include 'libs/db_connect.php';

    try{
    
        //write query
        $query = "INSERT INTO users SET firstname = ?, lastname = ?, username = ?, password  = ?";

        //prepare query for excecution
        $stmt = $con->prepare($query);

        //bind the parameters
        //this is the first question mark
        $stmt->bindParam(1, $_POST['firstname']);

        //this is the second question mark
        $stmt->bindParam(2, $_POST['lastname']);

        //this is the third question mark
        $stmt->bindParam(3, $_POST['username']);

        //this is the fourth question mark
        $stmt->bindParam(4, $_POST['password']);

        // Execute the query
        if($stmt->execute()){
        
            echo "Record was saved.";
        }else{
            die('Unable to save record.');
        }
        
    }catch(PDOException $exception){ //to handle error
        echo "Error: " . $exception->getMessage();
    }
}

?>

<!--we have our html form here where user information will be entered-->
<form action='#' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username' /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password' /></td>
        <tr>
            <td></td>
            <td>
                <input type='hidden' name='action' value='create' />
                <input type='submit' value='Save' />
                
                <a href='index.php'>Back to index</a>
            </td>
        </tr>
    </table>
</form>

</body>
</html>

    4.3 PDO: Read Records

    index.php - answers how to read records with PDO?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>PDO Read Records - code from codeofaninja.com</title>
        
    </head>
<body>

<h1>PDO: Read Records</h1>

<?php
//include database connection
include 'libs/db_connect.php';

$action = isset($_GET['action']) ? $_GET['action'] : "";

// if it was redirected from delete.php
if($action=='deleted'){
    echo "<div>Record was deleted.</div>";
}

//select all data
$query = "SELECT id, firstname, lastname, username FROM users";
$stmt = $con->prepare( $query );
$stmt->execute();

//this is how to get number of rows returned
$num = $stmt->rowCount();

echo "<a href='add.php'>Create New Record</a>";

if($num>0){ //check if more than 0 record found

    echo "<table border='1'>";//start table
    
        //creating our table heading
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Username</th>";
            echo "<th>Action</th>";
        echo "</tr>";
        
        //retrieve our table contents
        //fetch() is faster than fetchAll()
        //http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            //extract row
            //this will make $row['firstname'] to
            //just $firstname only
            extract($row);
            
            //creating new table row per record
            echo "<tr>";
                echo "<td>{$firstname}</td>";
                echo "<td>{$lastname}</td>";
                echo "<td>{$username}</td>";
                echo "<td>";
                    //we will use this links on next part of this post
                    echo "<a href='edit.php?id={$id}'>Edit</a>";
                    echo " / ";
                    //we will use this links on next part of this post
                    echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
                echo "</td>";
            echo "</tr>";
        }
    
    //end table
    echo "</table>";
    
}

//if no records found
else{
    echo "No records found.";
}

?>

<script type='text/javascript'>
function delete_user( id ){
    
    var answer = confirm('Are you sure?');
    if ( answer ){
    
        //if user clicked ok, pass the id to delete.php and execute the delete query
        window.location = 'delete.php?id=' + id;
    } 
}
</script>

</body>
</html>

    4.4 PDO: Update a Record

    edit.php - answers how to update a record with PDO?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>PDO Update a Record</title>
        
    </head>
<body>

<h1>PDO: Update a Record</h1>

<?php
//include database connection
include 'libs/db_connect.php';

$action = isset( $_POST['action'] ) ? $_POST['action'] : "";
if($action == "update"){
    try{
    
        //write query
        //in this case, it seemed like we have so many fields to pass and 
        //its kinda better if we'll label them and not use question marks
        //like what we used here
        $query = "update users 
                    set firstname = :firstname, lastname = :lastname, username = :username, password  = :password
                    where id = :id";

        //prepare query for excecution
        $stmt = $con->prepare($query);

        //bind the parameters
        $stmt->bindParam(':firstname', $_POST['firstname']);
        $stmt->bindParam(':lastname', $_POST['lastname']);
        $stmt->bindParam(':username', $_POST['username']);
        $stmt->bindParam(':password', $_POST['password']);
        $stmt->bindParam(':id', $_POST['id']);
        
        // Execute the query
        if($stmt->execute()){
            echo "Record was updated.";
        }else{
            die('Unable to update record.');
        }
        
    }catch(PDOException $exception){ //to handle error
        echo "Error: " . $exception->getMessage();
    }
}

try {

    //prepare query
    $query = "select id, firstname, lastname, username, password from users where id = ? limit 0,1";
    $stmt = $con->prepare( $query );
    
    //this is the first question mark
    $stmt->bindParam(1, $_REQUEST['id']);
    
    //execute our query
    $stmt->execute();
    
    //store retrieved row to a variable
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    //values to fill up our form
    $id = $row['id'];
    $firstname = $row['firstname'];
    $lastname = $row['lastname'];
    $username = $row['username'];
    $password = $row['password'];
    
}catch(PDOException $exception){ //to handle error
    echo "Error: " . $exception->getMessage();
}
    

?>
<!--we have our html form here where new user information will be entered-->
<form action='#' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' value='<?php echo $firstname;  ?>' /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' value='<?php echo $lastname;  ?>' /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username'  value='<?php echo $username;  ?>' /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password'  value='<?php echo $password;  ?>' /></td>
        <tr>
            <td></td>
            <td>
                <!-- so that we could identify what record is to be updated -->
                <input type='hidden' name='id' value='<?php echo $id ?>' /> 
                
                <!-- we will set the action to edit -->
                <input type='hidden' name='action' value='update' />
                <input type='submit' value='Edit' />
                
                <a href='index.php'>Back to index</a>
            </td>
        </tr>
    </table>
</form>

</body>
</html>

    4.5 PDO: Delete a Record

    delete.php - answers how to delete a record with PDO?

    <?php
//include database connection
include 'libs/db_connect.php';

try {

    // delete query
    $query = "DELETE FROM users WHERE id = ?";
    $stmt = $con->prepare($query);
    $stmt->bindParam(1, $_GET['id']);
    
    if($result = $stmt->execute()){
        // redirect to index page
        header('Location: index.php?action=deleted');
    }else{
        die('Unable to delete record.');
    }
}

// to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>


    5.0 PHP and MySQL CRUD - MySQLi Extension

    5.1 MySQLi: Connect to Database

    db_connect.php - answers how to connect to database with MySQLi?

    <?php
//set connection variables
$host = "localhost";
$db_name = "your_db_name";
$username = "your_db_username";
$password = "your_db_password";

//connect to mysql server
$mysqli = new mysqli($host, $username, $password, $db_name);

//check if any connection error was encountered
if(mysqli_connect_errno()) {
    echo "Error: Could not connect to database.";
    exit;
}
?>

    5.2 MySQLi: Create New Record

    add.php - answers how to create a record with MySQLi?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>MySQLi: Create Record</title>
    </head>
<body>

<h1>MySQLi: Create Record</h1>

<?php
// if the form was submitted
if($_POST){

    // connect to database
    include 'libs/db_connect.php';

    // sql query
    $sql = "INSERT INTO 
                users (firstname, lastname, username, password)
            VALUES
                (?, ?, ?, ?)";

    // if the statement was prepared
    if($stmt = $mysqli->prepare($sql) ){

        /*
         * bind the values,
         * "ssss" means 4 string were being binded,
         * aside from s for string, you can also use:
         * i for integer
         * d for decimal
         * b for blob
         */
        $stmt->bind_param(
            "ssss", 
            $_POST['firstname'],
            $_POST['lastname'],
            $_POST['username'],
            $_POST['password']
        );

        // execute the insert query
        if($stmt->execute()){
            echo "User was saved.";
            $stmt->close();
        }else{
            die("Unable to save.");
        }

    }else{
        die("Unable to prepare statement.");
    }

    // close the database
    $mysqli->close();
}

?>

<!--we have our html form here where user information will be entered-->
<form action='add.php' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username' /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password' /></td>
        <tr>
            <td></td>
            <td>
                <input type='submit' value='Save' />
                <a href='index.php'>Back to index</a>
            </td>
        </tr>
    </table>
</form>

</body>
</html>

    5.3 MySQLi: Read Records

    index.php - answers how to read records with MySQLi?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>MySQLi: Read Records - code from codeofaninja.com</title>
    </head>
<body>

<h1>MySQLi: Read Records</h1>

<?php
//include database connection
include 'libs/db_connect.php';

$action = isset($_GET['action']) ? $_GET['action'] : "";

//if the user clicked ok, run our delete query
if($action=='deleted'){
    echo "User was deleted.";
}

$query = "select * from users";
$result = $mysqli->query( $query );

$num_results = $result->num_rows;

echo "<div><a href='add.php'>Create New Record</a></div>";

if( $num_results ){

    // html table
    echo "<table border='1'>";

        // table heading
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Username</th>";
            echo "<th>Action</th>";
        echo "</tr>";

    //loop to show each records
    while( $row = $result->fetch_assoc() ){
    
        //extract row
        //this will make $row['firstname'] to just $firstname only
        extract($row);

        //creating new table row per record
        echo "<tr>";
            echo "<td>{$firstname}</td>";
            echo "<td>{$lastname}</td>";
            echo "<td>{$username}</td>";
            echo "<td>";
                echo "<a href='edit.php?id={$id}'>Edit</a>";
                echo " / ";
                
                // delete_user is a javascript function, see at the bottom par of the page
                echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
            echo "</td>";
        echo "</tr>";
    }

    //end table
    echo "</table>";

}

//if table is empty
else{
    echo "No records found.";
}

//disconnect from database
$result->free();
$mysqli->close();
?>

<script type='text/javascript'>
function delete_user( id ){

    var answer = confirm('Are you sure?');

    //if user clicked ok
    if ( answer ){ 
        //redirect to url with action as delete and id to the record to be deleted
        window.location = 'delete.php?id=' + id;
    }
}
</script>

</body>
</html>

    5.4 MySQLi: Update a Record

    edit.php - answers how to update record with MySQLi?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>MySQLi Create Record</title>
    </head>
<body>

<h1>MySQLi: Update a Record</h1>

<?php
//include database connection
include 'libs/db_connect.php';

// if the form was submitted/posted, update the record
if($_POST){

    //write query
    $sql = "UPDATE 
                users 
            SET
                firstname = ?, 
                lastname = ?,
                username = ?,
                password  = ?
            WHERE 
                id= ?";
    
    $stmt = $mysqli->prepare($sql);
    
    // you can bind params this way,
    // if you want to see the other way, see our add.php
    $stmt->bind_param(
        'ssssi', 
        $_POST['firstname'], 
        $_POST['lastname'],
        $_POST['username'],
        $_POST['password'],
        $_POST['id']
    );
    
    // execute the update statement
    if($stmt->execute()){
        echo "User was updated.";
        
        // close the prepared statement
        $stmt->close();
    }else{
        die("Unable to update.");
    }
}

/*
 * select the record to be edited,
 * you can also use prepared statement here, 
 * but my hosting provider seems it does not support the mysqli get_result() function
 * you can use it like this one http://php.net/manual/fr/mysqli.prepare.php#107568
 
 * so it I'm going to use $mysqli->real_escape_string() this time.
 */

$sql = "SELECT 
            id, firstname, lastname, username, password 
        FROM 
            users
        WHERE 
            id = \"" . $mysqli->real_escape_string($_GET['id']) . "\"
        LIMIT 
            0,1";

// execute the sql query
$result = $mysqli->query( $sql );

//get the result
$row = $result->fetch_assoc();

// php's extract() makes $row['firstname'] to $firstname automatically
extract($row);

//disconnect from database
$result->free();
$mysqli->close();
?>

<!--we have our html form here where new user information will be entered-->
<form action='edit.php?id=<?php echo $id; ?>' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' value='<?php echo $firstname;  ?>' /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' value='<?php echo $lastname;  ?>' /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username'  value='<?php echo $username;  ?>' /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password'  value='<?php echo $password;  ?>' /></td>
        <tr>
            <td></td>
            <td>
                <!-- so that we could identify what record is to be updated -->
                <input type='hidden' name='id' value='<?php echo $id ?>' /> 
                <input type='submit' value='Edit' />
                <a href='index.php'>Back to index</a>
            </td>
        </tr>
    </table>
</form>

</body>
</html>

    5.5 MySQLi: Delete a Record

    delete.php - answers how to delete a record with MySQLi?

    <?php
include 'libs/db_connect.php';

// delete sql query
$sql = "DELETE FROM users WHERE id = ?";

// prepare the sql statement
if($stmt = $mysqli->prepare($sql)){

    // bind the id of the record to be deleted
    // we use "i" here for integer
    $stmt->bind_param("i", $_GET['id']);

    // execute the delete statement
    if($stmt->execute()){
    
        // close the prepared statement
        $stmt->close();
        
        // redirect to index page
        // parameter "action=deleted" is used to show that something was deleted
        header('Location: index.php?action=deleted');
        
    }else{
        die("Unable to delete.");
    }

}
?>

    6.0 PHP and MySQL CRUD - MySQL Extension

    6.1 MySQL: Connect to Database

    db_connect.php - answers how to connect to database with MySQL extension?

    <?php
$host = "localhost";
$db_name = "your_db_name";
$username = "your_db_username";
$password = "your_db_password";

$conn = mysql_connect($host, $username, $password) or die ('Error connecting to mysql');
mysql_select_db($db_name) or die ('Unable to select database!');
?>

    6.2 MySQL: Create New Record

    add.php - answers how to create a record with MySQL extension?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>MySQL: Create a Record</title>
    
    </head>
<body>

<h1>MySQL: Create a Record</h1>

<?php

if($_POST){

    //include database configuration
    include 'libs/db_connect.php';

    //sql insert statement
    $sql="INSERT INTO users 
            ( firstname, lastname, username, password ) 
        VALUES 
            ( '". mysql_real_escape_string( $_POST['firstname'] ) . "', 
            '" . mysql_real_escape_string( $_POST['lastname'] ) . "', 
            '". mysql_real_escape_string( $_POST['username'] ) . "', 
            '". mysql_real_escape_string( $_POST['password'] ) . "')";

    //execute query
    if(mysql_query($sql)){
        //if successful query
        echo "New record was saved.";
    }else{
        //if query failed
        die("SQL: " . $sql . " >> ERROR: " . mysql_error());
    }
}
?>

<!--we have our html form here where user information will be entered-->
<form action='#' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username' /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password' /></td>
        <tr>
            <td></td>
            <td>
                <input type='submit' value='Save' />
                <a href='index.php'>Back to index</a>
            </td>
        </tr>
    </table>
</form>

</body>
</html>

    6.3 MySQL: Read Records

    index.php - answers how to read records with MySQL extension?

    <!DOCTYPE HTML>
<html>
    <head>
        <title>MySQL: Read Records - code from codeofaninja.com</title>
    </head>
<body>

<h1>MySQL: Read Records</h1>

<?php

isset($_GET['action']) ? $action=$_GET['action'] : $action="";
    
if($action=='deleted'){
    echo "<div>Record was deleted.</div>";
}

// add record link
echo "<a href='add.php'>Create New Record</a>";

//include database configuration
include 'libs/db_connect.php';

//selecting records
$sql="SELECT id, firstname, lastname, username FROM users";

//query the database
$rs=mysql_query($sql) or die($sql.">>".mysql_error());

//count how many records found
$num=mysql_num_rows($rs);

//check if more than 0 record found
if($num>0){

    //start table
    echo "<table border='1'>";
   
        //creating our table heading
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Username</th>";
            
            //we're gonna add this column for edit and delete action
            echo "<th>Action</th>";
        echo "</tr>";
       
        //retrieve our table contents
        while($row=mysql_fetch_array($rs)){
            //extract row
            //this will make $row['firstname'] to
            //just $firstname only
            extract($row);
           
            //creating new table row per record
            echo "<tr>";
                echo "<td>{$firstname}</td>";
                echo "<td>{$lastname}</td>";
                echo "<td>{$username}</td>";
                
                // edit and delete options
                echo "<td>";
                    echo "<a href='edit.php?id={$id}'>Edit</a>";
                    echo " / ";
                    echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
                echo "</td>";
                
            echo "</tr>";
        }
        
    //end table
    echo "</table>";

}

// if no records found
else{
    echo "No records found.";
}
?>

<script type='text/javascript'>
function delete_user( id ){

    var answer = confirm('Are you sure?');

    //if user clicked ok
    if ( answer ){
        //redirect to url with action as delete and id to the record to be deleted
        window.location = 'delete.php?id=' + id;
    }
}
</script>

</body>
</html>

    6.4 MySQL: Update a Record

    edit.php - answers how to update a record with MySQL extension.

    <!DOCTYPE HTML>
<html>
    <head>
        <title>MySQL: Update a Record</title>
        
    </head>
<body>

<h1>MySQL: Update a Record</h1>

<?php
include 'libs/db_connect.php';

if($_POST){

    //update the record if the form was submitted
    $sql="UPDATE
            users
        SET 
            firstname='" . mysql_real_escape_string($_POST['firstname']) . "',
            lastname='" . mysql_real_escape_string($_POST['lastname']) . "',
            username='" . mysql_real_escape_string($_POST['username']) . "',
            password='" . mysql_real_escape_string($_POST['password']) . "'
        WHERE 
            id=" . mysql_real_escape_string($_POST['id']);
                
    if(mysql_query($sql)){
        //this will be displayed when the query was successful
        echo "<div>Record was edited.</div>";
    }else{
        die("SQL: " . $sql . " >> ERROR: " . mysql_error());
    }
}

$id=$_REQUEST['id']; //the user id

//this query will select the user data which is to be used to fill up the form
$sql="select * from users where id={$id}";
$rs=mysql_query($sql) or die("SQL: ".$sql." >> ".mysql_error());
$num=mysql_num_rows($rs);

//just a little validation, if a record was found, the form will be shown
//it means that there's an information to be edited
if($num>0){ 
    $row=mysql_fetch_assoc($rs);
    extract($row);
?>
<!--we have our html form here where new user information will be entered-->
<form action='#' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' value='<?php echo $firstname;  ?>' /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' value='<?php echo $lastname;  ?>' /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username'  value='<?php echo $username;  ?>' /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password'  value='<?php echo $password;  ?>' /></td>
        <tr>
            <td></td>
            <td>
                <!-- so that we could identify what record is to be updated -->
                <input type='hidden' name='id' value='<?php echo $id ?>' /> 
                
                <!-- we will set the action to edit -->
                <input type='hidden' name='action' value='edit' />
                <input type='submit' value='Edit' />
                <a href='index.php'>Back to index</a>
            </td>
        </tr>
    </table>
</form>
<?php
}else{
    echo "User with this id is not found.";
}
?>
    
</body>
</html>

    6.5 MySQL: Delete a Record

    delete.php - answers how to delete a record with MySQL extension.

    <?php
// connect to database
include 'libs/db_connect.php';

// delete query
$sql = "DELETE FROM users WHERE id = {$_GET['id']}";

if(mysql_query($sql)){
            
    // redirect to index page
    // parameter "action=deleted" is used to show that something was deleted
    header('Location: index.php?action=deleted');

}

else{
    die("SQL: " . $sql . " >> ERROR: ".mysql_error());
}
?>

    7.0 Links and Literature

    7.1 Source Code Download

    7.2 Online Resources

    7.3 The Code of a Ninja Resources

    If you have something to say or point out something that can further improve this resource, please don't hesitate to drop a comment below. Also, please share this post if you think this is a useful PHP CRUD Tutorial. Thanks for reading!
    For FREE programming tutorials, click the red button below and subscribe! :)
    Thanks for the comments!
     
     
    Fundamentals
    "First do it, then do it right, then do it better."
    ~ Addy Osmani
    "Talk is cheap. Show me the code."
    ~ Linus Torvalds
    My Other Links
    Privacy Policy
    Terms of Use
    About Us
    Contact Mike
    Let's Stay Connected!
    g+ r
    Android app on Google Play
    © 2011-2014 The Code Of A Ninja. All rights reserved. Proudly Powered by Google Blogger. Images, logos, marks or names mentioned herein are the property of their respective owners.