Getting Started with PHP: MySQL Database

PHP is a popular server-side scripting language widely used for dynamic website development. MySQL is a widely used open source relational database system commonly used to store and manage website data. This article will introduce how to use PHP to connect and operate a MySQL database.

1. Connect to MySQL database

To connect to the MySQL database, you first need to create a connection object using the mysqli_connect() function in the PHP code. In this function, you need to specify the host name, user name, password and database name of the MySQL server. The following is a simple example:

$server = "localhost";
$username = "root";
$password = "123456";
$dbname = "mydatabase";
$conn = mysqli_connect($server, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

In this example, $server is the host name of the MySQL server, $username and $password are the username and password to log in to MySQL respectively, and $dbname is the name of the database to be connected. . The mysqli_connect() function returns a connection object. If the connection fails, an error message is output and script execution is terminated. If the connection is successful, "Connected successfully" will be output.

2. Query data

After connecting to the MySQL database, you can use the mysqli_query() function to execute SQL query statements. For example, the following code queries the data of all rows in a table named "users" and prints the results:

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

In this example, $sql contains the SQL query statement. The mysqli_query() function sends this query to the MySQL server and returns a result object. If the query is successful, you can use the mysqli_num_rows() function to get the number of rows in the result set. Then, you can use the mysqli_fetch_assoc() function to fetch the data row-by-row from the result set. This function returns an associative array containing the data for each row. Finally, the data can be printed out using the echo statement.

3. Insert data

To insert data into the MySQL database, you can use the mysqli_query() function to execute the SQL INSERT statement. For example, the following code inserts a new user's name and email into the "users" table:

$name = "John";
$email = "john@example.com";
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

In this example, $name and $email are the data to be inserted. $sql contains the INSERT statement to insert this data into the "users" table. If the insertion is successful, a message is output. Otherwise, an error message and error message are output.

4. Update data

To update data in the MySQL database, you can use the mysqli_query() function to execute the SQL UPDATE statement. For example, the following code updates the email of a user named "John" to "john@gmail.com":

$name = "John";
$email = "john@gmail.com";
$sql = "UPDATE users SET email='$email' WHERE name='$name'";
if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

In this example, $name is the username to be updated and $email is Email address to update. $sql contains an UPDATE statement that updates the email for the row named "John" in the "users" table to "john@gmail.com". If the update is successful, a message is output. Otherwise, an error message and error message are output.

5. Delete data

To delete data from the MySQL database, you can use the mysqli_query() function to execute the SQL DELETE statement. For example, the following code deletes a user named "John" from the "users" table:

$name = "John";
$sql = "DELETE FROM users WHERE name='$name'";
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

In this example, $name is the name of the user to be deleted. $sql contains the DELETE statement to delete the row named "John" in the "users" table. If the deletion is successful, a message will be output. Otherwise, an error message and error message are output.

Summary

PHP is a popular server-side scripting language that can be used for dynamic website development. MySQL is a widely used open source relational database system commonly used to store and manage website data. The basics of connecting and manipulating MySQL databases are very important for PHP developers. Through the methods of connecting, querying, inserting, updating and deleting data introduced in this article, you can easily use PHP to interact with the MySQL database and create powerful dynamic websites.

The above is the detailed content of Getting Started with PHP: MySQL Database. For more information, please follow other related articles on the PHP Chinese website!