DEV Community

loading...

PHP and MySQL 20: query insert

antelove19 profile image Falah Al Fitri Updated on ・3 min read

Happy Coding

Home Previous

In this post, we will learn how to use query INSERT in PHP for store data into database MySQL, with 3 ways:

  1. MySQLi procedural
  2. MySQLi object-oriented
  3. PDO

For known, how to connect to database, can be learned in here

Insert query is used for insert data or record into database

Syntax:

    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);

Example:

    $sql  = "INSERT INTO users ";
    $sql .= "( ";
    $sql .= "firstname ";
    $sql .= "lastname ";    
    $sql .= ") ";

    $sql .= "VALUES ";
    $sql .= "( ";
    $sql .= "'John' "; // string, so we must add quote
    $sql .= "'Doe' "; // string    
    $sql .= ")";

And, for execute the query, we will use 3 ways:

1. MySQLi1 procedural

Back to Home

Execute query using mysqli::query2 and get a $result:

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

Check the $result,
if TRUE, call mysqli::$insert_id3 function for get last id inserted and display a success message,
else display error messages using mysqli::$error4:

    if ( $result ) // if TRUE
    {

        $id = mysqli_insert_id($connection);

        echo "Data has been <b>CREATED</b> with ID {$id} ..";

    }
    else 
    {

        echo "Error: {$sql}";
        echo "<br />";
        echo "Error: " . mysqli_error($connection);

    }

Close connection using mysqli::close5:

    mysqli_close($connection);

Source Code:


2. MySQLi1 object-oriented

Back to Home

Execute query using mysqli::query2 and get a $result:

    $result = $connection->query($sql);

Check the $result:

    if ( $result ) // if TRUE
    {

        $id = $connection->insert_id;

        echo "Data has been <b>CREATED</b> with ID {$id} ..";

    } 
    else 
    {

        echo "Error: {$sql}";
        echo "<br />";
        echo "Error: {$connection->error}";

    }

Close connection using mysqli::close5:

     $connection->close();

Source Code:


3. PDO6

Back to Home

Execute query and get a $result:

    $result = $connection->query($sql);

Check the $result:

    if ( $result ) // if TRUE
    {

        /* last id insert */
        $id = $connection->lastInsertId();

        echo "Data has been <b>CREATED</b> with ID {$id} ..";

    } 
    else 
    {

        echo "Error: {$sql}";
        echo "<br />";
        echo "Error: {$connection->error}";

    }

Close connection:

    $connection = null;

Source Code:


Back to Home | Next


Thank for reading :)


  1. php.net, "MySQL Improved Extension", accessed on date 21 december 2019 and from https://www.php.net/manual/en/book.mysqli.php 

  2. php.net, "mysqli::query", accessed on date 22 december 2019 and from https://www.php.net/manual/en/mysqli.query.php 

  3. php.net, "mysqli::$insert_id", accessed on date 22 december 2019 and from https://www.php.net/manual/en/mysqli.insert-id.php 

  4. php.net, "mysqli::$error", accessed on date 22 december 2019 and from https://www.php.net/manual/en/mysqli.connect-error.php 

  5. php.net, "mysqli::close", accessed on date 21 december 2019 and from https://www.php.net/manual/en/mysqli.close.php 

  6. php.net, "PDO::__construct", accessed on date 21 december 2019 and from https://www.php.net/manual/en/pdo.construct.php  

Discussion (0)

Forem Open with the Forem app