DEV Community

Falah Al Fitri
Falah Al Fitri

Posted on • Edited on

PHP and MySQL 20: query insert

#php #mysql #mysqli #pdo
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  ↩

Top comments (0)