PHP MySQL: insert data into table

Introduction: In this tutorial, you will learn how to insert data into a MySQL table using PHP PDO.

Recommended reference video tutorial: "mysql tutorial"

Below we will introduce it to you with a simple example.

The contents of the tasks data table are as follows:

If we want to insert data into the table, we can follow the following steps:

Connect to the MySQL database by creating a new instance of the PDO object.

Construct a MySQL INSERT statement.

Call the exec() method of the PDO object.

PHP MySQL insert data example: Insert a new row in the table example

<?php
 
class InsertDataDemo {
 
    const DB_HOST = &#39;localhost&#39;;
    const DB_NAME = &#39;classicmodels&#39;;
    const DB_USER = &#39;root&#39;;
    const DB_PASSWORD = &#39;&#39;;
 
    private $pdo = null;
 
    /**
     * Open the database connection
     */
    public function __construct() {
        // open database connection
        $conStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME);
        try {
            $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
        } catch (PDOException $pe) {
            die($pe->getMessage());
        }
    }
//...

The following example shows how to insert a new row in the tasks table.

/**
     * Insert a row into a table
     * @return
     */
    public function insert() {
        $sql = "INSERT INTO tasks (
                      subject,
                      description,
                      start_date,
                      end_date
                  )
                  VALUES (
                      'Learn PHP MySQL Insert Dat',
                      'PHP MySQL Insert data into a table',
                      '2013-01-01',
                      '2013-01-01'
                  )";
 
        return $this->pdo->exec($sql);
    }

Define class InsertDataDemo. The constructor establishes a database connection and uses the destructor to close the database connection.

In the InsertDataDemo class, we define an insert method, which calls the exec() method of the PDO object to execute the INSERT statement.

The following statements create an instance of the InsertDataDemo class and call the insert() method to insert new rows into the tasks table.

$obj = new InsertDataDemo();
$obj->insert();

Let’s query the data in the tasks table:

SELECT *
  FROM tasks;

PHP MySQL: Insert a single row using prepared statement example

To be dynamic And to safely pass values ​​from PHP to SQL statements, you can use PDO prepared statements.

First, use a MySQL statement with named placeholders as follows:

$sql = 'INSERT INTO tasks (
                      subject,
                      description,
                      start_date,
                      end_date
                  )
                  VALUES (
                      :subject,
                      :description,
                      :start_date,
                      :end_date
                  );';

:subject, :description, :startdate and :enddate are called named placeholders.

Second, call the prepare() method of the PDO object to prepare the SQL statement for execution:

$q = $pdo->prepare($sql);

Then, call the execute() method and pass an array containing the values ​​corresponding to the named placeholders .

$q->execute($task)

Put them together.

/**
     * Insert a new task into the tasks table
     * @param string $subject
     * @param string $description
     * @param string $startDate
     * @param string $endDate
     * @return mixed returns false on failure 
     */
    function insertSingleRow($subject, $description, $startDate, $endDate) {
        $task = array(':subject' => $subject,
            ':description' => $description,
            ':start_date' => $startDate,
            ':end_date' => $endDate);
 
        $sql = 'INSERT INTO tasks (
                      subject,
                      description,
                      start_date,
                      end_date
                  )
                  VALUES (
                      :subject,
                      :description,
                      :start_date,
                      :end_date
                  );';
 
        $q = $this->pdo->prepare($sql);
 
        return $q->execute($task);
    }

Now we can pass the task's data to the insertSingleRow() method:

$obj->insertSingleRow('MySQL PHP Insert Tutorial',
                          'MySQL PHP Insert using prepared statement',
                          '2013-01-01',
                          '2013-01-02');

Check the tasks table:

PHP MySQL in Insert multiple rows in the table example

There are two methods to insert multiple rows in the table:

insertSingleRow() Execute this method multiple times.

Construct an INSERT to insert multiple rows and execute its MySQL statement.

This article is about the specific method of inserting data into the MySQL table using PHP PDO preprocessing statements. I hope it will be helpful to friends in need.

The above is the detailed content of PHP MySQL: insert data into table. For more information, please follow other related articles on the PHP Chinese website!