PHP queries the contents of two tables at the same time

When developing a website or application, we often need to obtain data from different database tables. In PHP, we can use SQL statements to retrieve data from a single table, but if we need to get data from multiple tables at the same time, we need to write complex query statements. In this article, we will learn how to query the contents of two tables simultaneously using PHP.

1. Join two tables

When using SQL query statements to retrieve data from two tables, we need to use the connection (JOIN) function. A join matches data in two tables, producing a result set that contains all matching data in both tables. In PHP, we can use the mysqli function to perform connection queries.

The following is a sample code to get data from two tables:

$mysqli = new mysqli("localhost", "username", "password", "mydatabase");

$query = "SELECT * FROM table1 JOIN table2 ON table1.id = table2.id";

$result = $mysqli->query($query);

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

$mysqli->close();

In the above code, we query two tables named table1 and table2. By specifying the ON statement, we join the two tables together. By using SELECT * we can retrieve all fields from both tables.

2. Use a left join

If we need to get data from two tables, and the data in one table may not match the other table, we need to use a left join. A left join can retrieve a result set with matching and non-matching rows. In PHP, we can perform a left join using the LEFT JOIN keyword.

The following is a sample code that uses a left join to obtain data:

$mysqli = new mysqli("localhost", "username", "password", "mydatabase");

$query = "SELECT table1.name, table2.email FROM table1 LEFT JOIN table2 ON table1.id = table2.id";

$result = $mysqli->query($query);

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

$mysqli->close();

In the above code, we used a left join to retrieve data from two tables. We specify the fields to select from each table, and the ON condition used in the LEFT JOIN statement. The result set will include all rows in table1, as well as rows that match rows in table2. If there is no matching row in table2, NULL will replace the value in the result set.

3. Use aliases

When retrieving data from multiple tables, we may encounter problems with fields with the same name in multiple tables. To solve this problem we can use aliases. An alias is a different name used to identify a table or a field. In PHP, we can use the AS keyword to specify an alias for a table or field.

The following is a sample code using aliases:

$mysqli = new mysqli("localhost", "username", "password", "mydatabase");

$query = "SELECT table1.name AS customer_name, table2.email AS customer_email FROM table1 JOIN table2 ON table1.id = table2.id";

$result = $mysqli->query($query);

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

$mysqli->close();

In the above code, we have specified aliases for the fields in each table using the AS keyword. By using aliases we can avoid conflicts caused by fields with the same name in two tables.

Conclusion

In this article, we discussed how to query the contents of two tables simultaneously using PHP. We used join and left join functionality, as well as alias syntax. By using these techniques, we can easily retrieve data from multiple tables and merge them into a single result set. Whether we are developing a website or an application, these technologies are very useful for retrieving and organizing data.

The above is the detailed content of PHP queries the contents of two tables at the same time. For more information, please follow other related articles on the PHP Chinese website!