数据库查询选择两个表，按日期排序和限制[重复]

Question

This question already has an answer here:

• mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource or result 32 answers

Is it possible to do a query like I described in the title? I have two tables with different types of columns, nothing similar between them. What I want to do is join them together and display them in reversing order starting with the earliest but limiting to 5 per page. The way I'm getting the number of items is like this:

$limit = 5;
$page = 1;
if(isset($_GET['pn'])) {
    $page = $_GET['pn'];
    $lim = ($page-1)*$limit;
}else{
    $lim = ($page-1)*$limit;
}
$l = mysqli_query($mysqli_connect, "SELECT id FROM table1");
$s = mysqli_query($mysqli_connect, "SELECT id FROM table2");
$r = mysqli_num_rows($l) + mysqli_num_rows($s);

Then I'm just adding the page number in the URL if the user clicks next. Right now to get the query from one table I'm doing this:

$sql = mysqli_query($mysqli_connect, "SELECT * FROM table1 ORDER BY dateadded desc LIMIT $lim, $limit");

I need help constructing a query that will select both tables...and how to keep track of which rows were already added using the LIMIT?

UPDATE:

$sql = mysqli_query($mysqli_connect, "SELECT id FROM (SELECT id, dateadded FROM table1 UNION SELECT id, dateadded FROM table2) ORDER BY dateadded LIMIT $lim, $limit");
$num_rows = mysqli_num_rows($sql);
echo $num_rows;

I get a warning saying mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in.... Can't I see how many items were returned???

</div>

Answer 1

I assume at least both your table have an id and a field with the date (dateadded)

So first I would retrieve the ids you need to display with a query like this for the first page.

SELECT id FROM (
SELECT id, dateadded FROM table1
UNION
SELECT id, dateadded FROM table2
) AS mergedtable ORDER BY dateadded LIMIT 0, 5;

Based on the ids retrieved, you can query your database to get all the information from the tables

SELECT * FROM table1 WHERE id IN ( /* list of ids */ );
SELECT * FROM table2 WHERE id IN ( /* list of ids */ );

And then you will have to make a php manipulation to merge your results and order then based on their date. You might need also to prefix your ids based on their origin table if they are based on two different sequences