Mysql

PHP functions for working with MySQL: a detailed description

PHP provides a set of functions to work with MySQL databases. As of the more recent versions, developers are encouraged to use the MySQLi or PDO_MySQL extensions instead of the older MySQL extension, as the latter is deprecated. Here, we’ll focus on MySQLi (MySQL Improved) functions, which provide both procedural and object-oriented interfaces for interacting with MySQL databases.

MySQLi Functions Overview

  1. mysqli_connect()
    • Establishes a new connection to the MySQL server.
  2. mysqli_close()
    • Closes a previously opened database connection.
  3. mysqli_query()
    • Performs a query on the database.
  4. mysqli_fetch_assoc()
    • Fetches a result row as an associative array.
  5. mysqli_fetch_array()
    • Fetches a result row as an associative, numeric array, or both.
  6. mysqli_prepare()
    • Prepares an SQL statement for execution.
  7. mysqli_stmt_bind_param()
    • Binds variables to a prepared statement as parameters.
  8. mysqli_stmt_execute()
    • Executes a prepared query.
  9. mysqli_error()
    • Returns the last error description for the most recent function call.
  10. mysqli_num_rows()
    • Gets the number of rows in a result.

Examples

1. Establishing a Connection

$mysqli = mysqli_connect("localhost", "username", "password", "database");

if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

2. Closing a Connection

mysqli_close($mysqli);

3. Performing a Query

$result = mysqli_query($mysqli, "SELECT * FROM users");

if (!$result) {
    echo "Error: " . mysqli_error($mysqli);
}

4. Fetching Data as an Associative Array

if ($result) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row['name'] . "<br>";
    }
}

5. Fetching Data as a Numeric Array

if ($result) {
    while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
        echo "Name: " . $row[1] . "<br>";
    }
}

6. Prepared Statements

$stmt = mysqli_prepare($mysqli, "SELECT name FROM users WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $userId);

$userId = 1;
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $name);

while (mysqli_stmt_fetch($stmt)) {
    echo "Name: $name<br>";
}

mysqli_stmt_close($stmt);

7. Insert Data Using Prepared Statements

$stmt = mysqli_prepare($mysqli, "INSERT INTO users (name, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name = "John Doe";
$email = "john@example.com";
mysqli_stmt_execute($stmt);
echo "New record ID: " . mysqli_insert_id($mysqli);

8. Update Data

$stmt = mysqli_prepare($mysqli, "UPDATE users SET email = ? WHERE id = ?");
mysqli_stmt_bind_param($stmt, "si", $email, $userId);

$email = "john.updated@example.com";
$userId = 1;
mysqli_stmt_execute($stmt);
echo "Affected rows: " . mysqli_stmt_affected_rows($stmt);

9. Delete Data

$stmt = mysqli_prepare($mysqli, "DELETE FROM users WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $userId);

$userId = 1;
mysqli_stmt_execute($stmt);
echo "Deleted rows: " . mysqli_stmt_affected_rows($stmt);

10. Getting the Number of Rows

$result = mysqli_query($mysqli, "SELECT * FROM users");

echo "Number of rows: " . mysqli_num_rows($result);

These examples demonstrate the basic operations you can perform with MySQLi in PHP. The use of prepared statements is especially important for preventing SQL injection and ensuring better security for your database operations.

Victoria

Im just a girl who hanging around with her friends ;)

Recent Posts

Building Your Next Project with wp-scripts: A Comprehensive Guide

WordPress development has evolved significantly, and modern tooling plays a crucial role in creating efficient…

1 week ago

Script for automatically informing search engines about new content on website

I. Project Overview The goal is to automate the process of notifying search engines (like…

2 weeks ago

Creating an XML sitemap script with PHP, designed for automated updates via CRON

1. Database Structure (MySQL) We'll need a database table to store information about our website's…

2 weeks ago

Comprehensive guide on building a URL shortening script

This explanation aims to provide a solid foundation for understanding the process and implementing your…

2 weeks ago

Guide on building a real-time website chat script

Okay, here's a comprehensive guide on building a real-time website chat script using PHP, HTML,…

2 weeks ago

Comprehensive guide on creating a simple website analytics system

Comprehensive guide on creating a simple website analytics system using PHP, HTML, CSS, JavaScript, and…

2 weeks ago