MySQL Insert Data

Last modified on: October 20, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">
<div class="relative p-1 rounded-sm flex items-center justify-center bg-token-main-surface-primary text-token-text-primary h-8 w-8">&nbsp;</div>
</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-[20px] text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="00e30a71-1394-4b37-913b-35a5304ef0dc">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3>MySQL Insert Data</h3>
<p>Inserting data into a MySQL table is a fundamental operation that allows you to populate your database with information. You can insert data using SQL commands in various interfaces like the MySQL command line, PHP scripts, or database management tools.</p>
<hr />
<h3>Basic Syntax</h3>
<p>The basic SQL command to insert data into a table is:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-php"><code>INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);</code></pre>
</div>
</div>
</div>
</div>
<h3>Example of Inserting Data</h3>
<p>Here's a simple example that demonstrates how to insert data into the <code>users</code> table:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-php"><code>INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');</code></pre>
</div>
</div>
</div>
</div>
<h4>Inserting Multiple Rows</h4>
<p>You can also insert multiple rows in a single query:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-php"><code>INSERT INTO users (username, email)
VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3>Inserting Data Using PHP</h3>
<p>You can insert data into a MySQL table using PHP with MySQLi or PDO.</p>
<h4>Using MySQLi</h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-php"><code>// Database configuration
$host = 'localhost';
$username = 'root';
$password = 'your_password';
$database = 'your_database';

// Create a connection
$conn = new mysqli($host, $username, $password, $database);

// Check the connection
if ($conn-&gt;connect_error) {
    die("Connection failed: " . $conn-&gt;connect_error);
}

// Insert data
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";

if ($conn-&gt;query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "&lt;br&gt;" . $conn-&gt;error;
}

// Close the connection
$conn-&gt;close();</code></pre>
</div>
</div>
<h4>Using PDO</h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-php"><code>// Database configuration
$host = 'localhost';
$username = 'root';
$password = 'your_password';
$database = 'your_database';

try {
    // Create a new PDO instance
    $conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
    // Set the PDO error mode to exception
    $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Insert data
    $sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
    $conn-&gt;exec($sql);
    echo "New record created successfully";
} catch (PDOException $e) {
    echo "Error: " . $e-&gt;getMessage();
}

// Close the connection (optional)
$conn = null;</code></pre>
</div>
</div>
<hr />
<h3>Common Issues</h3>
<ol>
<li>
<p><strong>Duplicate Entries</strong>: If a column has a unique constraint (like <code>email</code>), trying to insert a duplicate value will cause an error. Handle this by ensuring unique values or using <code>INSERT IGNORE</code>:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-php"><code>INSERT IGNORE INTO users (username, email) VALUES ('john_doe', 'john@example.com');</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><strong>NULL Values</strong>: If you try to insert a value into a NOT NULL column without providing a value, you will encounter an error.</p>
</li>
<li>
<p><strong>Data Type Mismatches</strong>: Ensure that the values you are inserting match the data types defined in the table.</p>
</li>
</ol>
<hr />
<h3>Best Practices</h3>
<ul>
<li><strong>Prepared Statements</strong>: Use prepared statements to prevent SQL injection and improve security when inserting user-provided data.</li>
</ul>
<h4>Example with Prepared Statements (MySQLi)</h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-php"><code>$stmt = $conn-&gt;prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt-&gt;bind_param("ss", $username, $email);

$username = 'john_doe';
$email = 'john@example.com';
$stmt-&gt;execute();

echo "New record created successfully";
$stmt-&gt;close();</code></pre>
</div>
</div>
<h4>Example with Prepared Statements (PDO)</h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-php"><code>$stmt = $conn-&gt;prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt-&gt;bindParam(':username', $username);
$stmt-&gt;bindParam(':email', $email);

$username = 'john_doe';
$email = 'john@example.com';
$stmt-&gt;execute();

echo "New record created successfully";</code></pre>
</div>
</div>
<ul>
<li><strong>Validation</strong>: Always validate and sanitize user input to ensure data integrity and security.</li>
</ul>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More