DEV Community

Falah Al Fitri
Falah Al Fitri

Posted on • Edited on

1 1

PHP and MySQL 20: query insert


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, ...);
Enter fullscreen mode Exit fullscreen mode

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 .= ")";
Enter fullscreen mode Exit fullscreen mode

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 );
Enter fullscreen mode Exit fullscreen mode

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);

    }
Enter fullscreen mode Exit fullscreen mode

Close connection using mysqli::close5:

    mysqli_close($connection);
Enter fullscreen mode Exit fullscreen mode

Source Code:


2. MySQLi1 object-oriented

Back to Home

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

    $result = $connection->query($sql);
Enter fullscreen mode Exit fullscreen mode

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}";

    }
Enter fullscreen mode Exit fullscreen mode

Close connection using mysqli::close5:

     $connection->close();
Enter fullscreen mode Exit fullscreen mode

Source Code:


3. PDO6

Back to Home

Execute query and get a $result:

    $result = $connection->query($sql);
Enter fullscreen mode Exit fullscreen mode

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}";

    }
Enter fullscreen mode Exit fullscreen mode

Close connection:

    $connection = null;
Enter fullscreen mode Exit fullscreen mode

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  

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay