PHP MySQL: Insert Data Into a Table (original) (raw)

Summary: in this tutorial, you will learn how to use PHP PDO to insert one or more rows into a table.

To insert data into a table, you follow these steps:

First, connect to the MySQL database by creating a new PDO object.
Second, create a prepared statement.
Third, execute the INSERT statement using the prepared statement.

Inserting one row

The following insert.php script inserts a new row into the tasks table of the todo database:

`<?php require_once 'config.php';

try { conn=newPDO("mysql:host=conn = new PDO("mysql:host=conn=newPDO("mysql:host=host;dbname=$dbname", username,username, username,password); $sql = 'insert into tasks(title) values(:title)'; stmt=stmt = stmt=conn->prepare($sql); $stmt->execute([':title' => 'Learn PHP MySQL']); } catch (PDOException $e) { die($e); } `Code language: HTML, XML (xml)

How it works.

First, include the config.php file that contains the database configuration:

require_once 'config.php';Code language: PHP (php)

This statement imported variables declared in the config.php into the script.

Second, establish a connection to the todo database on the MySQL server by creating a new instance of the PDO:

$conn = new PDO("mysql:host=$host;dbname=$dbname", <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>u</mi><mi>s</mi><mi>e</mi><mi>r</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">username, </annotation></semantics></math>username,password);Code language: PHP (php)

Third, construct an INSERT statement:

$sql = 'insert into tasks(title) values(:title)';Code language: PHP (php)

The :title is a parameterized placeholder. This helps prevent SQL injection.

Fourth, prepare the SQL statement for execution:

$stmt = <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>c</mi><mi>o</mi><mi>n</mi><mi>n</mi><mo>−</mo><mo>></mo><mi>p</mi><mi>r</mi><mi>e</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>e</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">conn->prepare(</annotation></semantics></math>conn−>prepare(sql);Code language: PHP (php)

Fifth, execute the prepared SQL statement with the provided value:

$stmt->execute([':title' => 'Learn PHP MySQL']);Code language: PHP (php)

This statement inserts a new row into the tasks table with the title 'Learn PHP MySQL'.

If an exception occurs, the script will terminate with an error message:

} catch (PDOException $e) { die($e); }Code language: PHP (php)

Verify the insert

First, connect to the todo database on the MySQL server:

mysql -u root -p -D todo

Second, retrieve data from the tasks table:

SELECT * FROM tasks;

Output:

+----+-----------------+-----------+ | id | title | completed | +----+-----------------+-----------+ | 1 | Learn PHP MySQL | 0 | +----+-----------------+-----------+ 1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the script has inserted a new row into the table successfully.

Inserting multiple rows

To insert multiple rows into a MySQL database using PDO in PHP, you can modify the script to use a prepared statement in a loop.

For example, the following insert-multiple.php script inserts two rows into the tasks table:

`<?php require_once 'config.php';

try { conn=newPDO("mysql:host=conn = new PDO("mysql:host=conn=newPDO("mysql:host=host;dbname=$dbname", username,username, username,password);

$sql = 'INSERT INTO tasks(title) VALUES(:title)';
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">stmt = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6151em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>conn->prepare($sql);

$titles = ['Build a Web Application', 'Grow the App User Base'];
foreach ($titles as $title) {
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>e</mi><mi>x</mi><mi>e</mi><mi>c</mi><mi>u</mi><mi>t</mi><mi>e</mi><mo stretchy="false">(</mo><msup><mo stretchy="false">[</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>:</mo><mi>t</mi><mi>i</mi><mi>t</mi><mi>l</mi><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>=</mo><mo>&gt;</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;execute([&#x27;:title&#x27; =&gt; </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mord mathnormal">e</span><span class="mord mathnormal">x</span><span class="mord mathnormal">ec</span><span class="mord mathnormal">u</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mopen">(</span><span class="mopen"><span class="mopen">[</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.791em;vertical-align:-0.0391em;"></span><span class="mord mathnormal">t</span><span class="mord mathnormal">i</span><span class="mord mathnormal" style="margin-right:0.01968em;">tl</span><span class="mord"><span class="mord mathnormal">e</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=&gt;</span></span></span></span>title]);
}

} catch (PDOException $e) { die($e->getMessage()); } `Code language: PHP (php)

Summary

Use a prepared statement to insert one or more rows into a table.

Was this tutorial helpful?