What are some common pitfalls when using UNION and LIMIT in MySQL 5.7 with PHP?

When using UNION and LIMIT in MySQL 5.7 with PHP, a common pitfall is that the LIMIT clause applies to the entire result set of the UNION query, not just to each individual SELECT statement. To overcome this issue, you can use subqueries to apply the LIMIT clause to each SELECT statement separately. 

<?php
// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "dbname");

// Query with UNION and LIMIT applied to each SELECT statement separately
$query = "(SELECT * FROM table1 LIMIT 5) UNION (SELECT * FROM table2 LIMIT 5)";
$result = $mysqli->query($query);

// Fetch and display results
while ($row = $result->fetch_assoc()) {
    echo $row['column_name'] . "<br>";
}

// Close database connection
$mysqli->close();
?>

Keywords

UNION LIMIT MySQL 5.7 PHP pitfalls

Related Questions