PHP MySQLi

Last modified on: October 19, 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="ef7f8c68-97cb-4266-8382-3e8b0f0196de">
<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>PHP MySQLi</h3>
<p>MySQLi (MySQL Improved) is a PHP extension that provides an interface for interacting with MySQL databases. It offers improved features over the older MySQL extension, including support for prepared statements, transactions, and a more robust API.</p>
<hr />
<h4>Key Features of MySQLi</h4>
<ol>
<li>
<p><strong>Object-Oriented and Procedural Interfaces</strong>: MySQLi supports both object-oriented and procedural styles for database interaction.</p>
</li>
<li>
<p><strong>Prepared Statements</strong>: Helps prevent SQL injection by separating SQL logic from data.</p>
</li>
<li>
<p><strong>Transactions</strong>: Allows for multiple operations to be treated as a single unit of work.</p>
</li>
<li>
<p><strong>Enhanced Debugging</strong>: Provides better error reporting and debugging capabilities.</p>
</li>
<li>
<p><strong>Support for Stored Procedures</strong>: Allows execution of stored procedures directly from PHP.</p>
</li>
</ol>
<hr />
<h3>Basic Usage of MySQLi</h3>
<h4>1. Connecting to a MySQL Database</h4>
<p><strong>Object-Oriented Style</strong>:</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">
<pre class="language-php"><code>$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli-&gt;connect_error) {
    die("Connection failed: " . $mysqli-&gt;connect_error);
}
echo "Connected successfully";
</code></pre>
</div>
</div>
<p><strong>Procedural Style</strong>:</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">
<pre class="language-php"><code>$connection = mysqli_connect("localhost", "username", "password", "database");

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
</code></pre>
</div>
</div>
<h4>2. Performing Queries</h4>
<p><strong>Running a Simple Query</strong>:</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">
<pre class="language-php"><code>$result = $mysqli-&gt;query("SELECT * FROM users");

while ($row = $result-&gt;fetch_assoc()) {
    echo "User: " . $row['username'] . "&lt;br&gt;";
}
</code></pre>
</div>
</div>
<h4>3. Prepared Statements</h4>
<p>Using prepared statements to insert data securely:</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">
<pre class="language-php"><code>$stmt = $mysqli-&gt;prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt-&gt;bind_param("ss", $username, $password);

// Set parameters and execute
$username = "newuser";
$password = password_hash("userpassword", PASSWORD_DEFAULT); // Hashing the password
$stmt-&gt;execute();
echo "New user created successfully";

$stmt-&gt;close();</code></pre>
</div>
</div>
<h4>4. Transactions</h4>
<p>Using transactions to ensure data integrity:</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">
<pre class="language-php"><code>$mysqli-&gt;begin_transaction();

try {
    $mysqli-&gt;query("INSERT INTO accounts (user_id, balance) VALUES (1, 1000)");
    $mysqli-&gt;query("UPDATE accounts SET balance = balance - 500 WHERE user_id = 2");
    
    $mysqli-&gt;commit(); // Commit the transaction
    echo "Transaction successful";
} catch (Exception $e) {
    $mysqli-&gt;rollback(); // Roll back the transaction on error
    echo "Transaction failed: " . $e-&gt;getMessage();
}</code></pre>
</div>
</div>
<h4>5. Closing the Connection</h4>
<p>Always close the database connection when done:</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">
<pre class="language-php"><code>$mysqli-&gt;close();</code></pre>
</div>
</div>
<hr />
<h3>Example: Complete MySQLi Script</h3>
<p>Here&rsquo;s a complete example demonstrating connection, prepared statements, and transactions:</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">
<pre class="language-php"><code>&lt;?php
// Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli-&gt;connect_error) {
    die("Connection failed: " . $mysqli-&gt;connect_error);
}

// Prepare and bind
$stmt = $mysqli-&gt;prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt-&gt;bind_param("ss", $username, $password);

// Set parameters and execute
$username = "john_doe";
$password = password_hash("securepassword", PASSWORD_DEFAULT);
$stmt-&gt;execute();
echo "User created successfully&lt;br&gt;";

$stmt-&gt;close();

// Using transactions
$mysqli-&gt;begin_transaction();

try {
    $mysqli-&gt;query("INSERT INTO accounts (user_id, balance) VALUES (1, 1000)");
    $mysqli-&gt;query("UPDATE accounts SET balance = balance - 200 WHERE user_id = 2");
    
    $mysqli-&gt;commit();
    echo "Transaction successful&lt;br&gt;";
} catch (Exception $e) {
    $mysqli-&gt;rollback();
    echo "Transaction failed: " . $e-&gt;getMessage() . "&lt;br&gt;";
}

// Close the connection
$mysqli-&gt;close();
?&gt;</code></pre>
</div>
</div>
<h3>Conclusion</h3>
<p>MySQLi is a powerful extension for interacting with MySQL databases in PHP. Its support for both object-oriented and procedural programming, along with features like prepared statements and transactions, makes it a robust choice for database operations.</p>
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More