php - How to fetch datas from multiple table
I have three different tables, let's name them as
table1, table2, table3
But in these different tables, I have the same number of columns and also the same name. For example,
Table 1 columns: id, title, category, subcategory, image
Table 2 columns: id, title, category, subcategory, image
Table 3 columns: id, title, category, subcategory, image
How can I fetch all those 3 tables using a single foreach?
function post_details(){
global $db;
$query = "SELECT * FROM posts WHERE status = 'Active' ORDER BY id DESC";
$run_query = mysqli_query($db, $query);
$post_details = mysqli_fetch_all($run_query, MYSQLI_ASSOC);
$getdetails = array();
foreach ($post_details as $post_detail) {
array_push($getdetails, $post_detail);
}
return $getdetails;
}
Answer
Solution:
if you page shows blank,add this on top of php file
//if error happend,this will display error on page
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
you can use mysql union
if you want simple loop the rows
function post_details(){
global $db;
$query = "SELECT * FROM posts1 WHERE status = 'Active' ORDER BY id DESC";
$query .= " UNION SELECT * FROM posts2 WHERE status = 'Active' ORDER BY id DESC";
$query .= " UNION SELECT * FROM posts3 WHERE status = 'Active' ORDER BY id DESC";
$run_query = mysqli_query($db, $query);
$post_details = mysqli_fetch_all($run_query, MYSQLI_ASSOC);
$getdetails = array();
foreach ($post_details as $post_detail) {
array_push($getdetails, $post_detail);
}
return $getdetails;
}
if you want order by all table you can use temporary table like this,be careful of this,it could be slow in large dataset,due to it sort on temporary table.
$query = "SELECT * FROM
(SELECT * FROM posts1 WHERE status = 'Active'
UNION SELECT * FROM posts2 WHERE status = 'Active'
UNION SELECT * FROM posts3 WHERE status = 'Active')
AS t1 ORDER BY id DESC";
Answer
Solution:
Actually when we want to fetch records from the multiple table with the same columns or from the same table and different conditions, then we need to use UNION to merge all those records in the same SQL Query so please use this query and replace the table name with your real table names.
$query = "SELECT * FROM ( ( SELECT * FROM table1 WHERE status = 'Active' ORDER BY id DESC )
UNION
( SELECT * FROM table2 WHERE status = 'Active' ORDER BY id DESC )
UNION
( SELECT * FROM table2 WHERE status = 'Active' ORDER BY id DESC ) ) as tempdata";
Source