Database query selecting two tables, ordering by date and limit

Question

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???

Valentin Clement · Accepted Answer · 2014-08-15T13:51:31.753

2

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

edited Aug 15 '14 at 13:51

answered Aug 15 '14 at 13:31

Valentin Clement

241
3
13

That makes sense. I was thinking of just making a whole new table and insert from which table something was added along with time...but I was hoping this could be done in one query. – denikov Aug 15 '14 at 13:36
I updated my question with a warning I'm getting. Any idea why? – denikov Aug 15 '14 at 13:41
1

Because your query failed This is the reference for mysqli_query _Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE._ Have your tried your query first in a client – Valentin Clement Aug 15 '14 at 13:45
`mysqli_error()` gave me this..."Every derived table must have its own alias"... – denikov Aug 15 '14 at 13:49
That's right, I edited my answer ... it should work – Valentin Clement Aug 15 '14 at 13:51
Yeah, I added just a `as a` after the closing parenthesis and it worked. Thanks for your help – denikov Aug 15 '14 at 13:55
One more quick question...if I want to change the query to `SELECT id, category FROM (SELECT id, dateadded FROM table1 UNION SELECT id, dateadded FROM table2) as a order by dateadded`, I get an error saying "Unknown column 'category' in 'field list'"...although it is in both tables. Adding "'id', 'category'" and echoing `$row['id'] $row['category']` just prints out "idcategory", not the actual row value. – denikov Aug 15 '14 at 14:14
You need to add the column category in both queries which compose the union – Valentin Clement Aug 15 '14 at 15:17
1

`SELECT id, category FROM (SELECT id, category, dateadded FROM table1 UNION SELECT id, category, dateadded FROM table2) as a order by dateadded ` – Valentin Clement Aug 15 '14 at 16:01

Limiter · Answer 2 · 2014-08-15T13:45:45.157

0

SELECT id FROM (SELECT id, date FROM table1 UNION ALL SELECT id FROM table2) ORDER BY date

edited Aug 15 '14 at 13:45

answered Aug 15 '14 at 13:32

Limiter

497
3
11

1

Your query won't work as your don't select any date field in the inner query. – Valentin Clement Aug 15 '14 at 13:39

Database query selecting two tables, ordering by date and limit

2 Answers2