How to connect php to mysql and query to obtain data

PHP is a server-side programming language that can be used to create dynamic websites and web applications. MySQL is an extremely popular relational database management system that is widely used in web development. In PHP development, connecting to MySQL and performing data query is an extremely important task. This article will introduce you how to use PHP to connect to MySQL and perform data query.

Connecting to MySQL database

To connect to MySQL database in PHP, you need to use mysqli or PDO. The implementation methods of these two methods are introduced below.

Use mysqli to connect to the MySQL database

The mysqli method is the most common method to connect to the MySQL database in PHP. You can connect to the MySQL server using the following code:

$host = "localhost";
$user = "username";
$pass = "password";
$dbname = "database";

$conn = new mysqli($host, $user, $pass, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

This code will first define the required hostname, username, password and database name. Next use the mysqli constructor to create a new MySQL connection.

If the connection fails, use the connect_error attribute to return an error message and exit the script.

Connecting to a MySQL database using PDO

The PDO method can be used with different types of databases, including MySQL, PostgreSQL, and Oracle. In PHP, when using PDO, you need to define the following:

$host = 'localhost';
$dbname = 'dbname';
$user = 'username';
$pass = 'password';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

This code will first define the required hostname, username, password and database name. Next, create a new MySQL connection using PDO's constructor. You can set the exception handling mode using the setAttribute method.

If the connection fails, the PDOException contained in the catch block will catch and output an error message.

Querying MySQL Data

Once you have established a connection to the MySQL server, you can start querying the database and retrieving data. Below is sample code for querying data.

Query MySQL database using mysqli

$sql = "SELECT * FROM table WHERE column = 'value'";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

In this example, first define the SQL query. Use the mysqli query method to execute this query and store the results in the $result variable. Next, use the $num_rows property to check whether the data rows exist in the result set.

If a data row is detected, use the fetch_assoc method to loop out the ID and Name of each row.

If the data row does not exist in the result set, print "0 results".

Querying MySQL database using PDO

The following is the code to query MySQL database using PDO:

$sql = "SELECT * FROM table WHERE column = 'value'";
$stmt = $conn->prepare($sql);
$stmt->execute();

if ($stmt->rowCount() > 0) {
    while ($row = $stmt->fetch()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

In this example, first define the SQL query and then use $stmt-> The ;prepare method executes this query and stores the results in the $stmt variable. Use the rowCount method to check whether a data row exists in the result set.

If a data row is detected, use the fetch method to loop out the ID and Name of each row. If the data row does not exist in the result set, "0 results" is printed.

Summary

In PHP development, connecting to MySQL and performing data query is an extremely important task. You can use mysqli or PDO to achieve this task. Before executing the query, you need to define the SQL query and then store the results in the corresponding variables. Then, the data in the MySQL database can be retrieved by looping through the result set using different methods.

The above is the detailed content of How to connect php to mysql and query to obtain data. For more information, please follow other related articles on the PHP Chinese website!