CODEWITHSUNDEEP
phpmysql
alanj
alanj

Reputation: 171

Select rows by one field and sort by another across multiple tables with mysql using PHP

I have 7 different tables that all have the same field, user(int) and time(int). Ideally, I want one query that will select all the rows that match a specific user, then sort them all by time. I've tried this: http://www.w3schools.com/sql/sql_join_full.asp (JOIN FULL) with no success. I'm having a hard time searching because I don't even know if a JOIN is what I need here.

Something like this:

SELECT * FROM table1,table2,table3 WHERE user='1' ORDER BY time DESC

Would get me this:

//my query and a while loop here
    $row['food'];//[{"food" : "burrito","qty" : "1" }]
    $row['feel'];//8
    //next row
    $row['ailment'];// xx some value xx
    $row['feel'];//4
    //etc

Here's some examples of the tables - I have a users table too.

+----+------------+------------------+------+------+---------+
| id | time       | ailments         | user | feel | comment |
+----+------------+------------------+------+------+---------+
|  1 | 1426870546 | xx some value xx | 1    |    4 |         |
|  2 | 1426870577 | xx some value xx | 1    |    4 |         |
|  3 | 1426870881 | xx some value xx | 1    |    8 |         |
|  4 | 1426877198 | xx some value xx | 5    |    8 |         |
|  5 | 1426881122 | xx some value xx | 2    |    2 |         |
|  6 | 1426881778 | xx some value xx | 6    |    8 |         |
+----+------------+------------------+------+------+---------+

and 

+----+------------+-------------------------------------+------+------+---------+
| id | time       | food                                | user | feel | comment |
+----+------------+-------------------------------------+------+------+---------+
|  1 | 1426860681 | [{"food" : "burrito","qty" : "1" }] | 2    |    8 |         |
|  2 | 1426861024 | [{"food" : "burrito","qty" : "1" }] | 1    |    8 |         |
|  3 | 1426861043 | [{"food" : "burrito","qty" : "1" }] | 5    |    8 |         |
|  4 | 1426861069 | [{"food" : "burrito","qty" : "1" }] | 2    |    8 |         |
|  5 | 1426882559 | [{"food" : "taco","qty" : "1" }]    | 6    |    7 |         |
|  6 | 1426884089 | [{"food" : "taco","qty" : "1" }]    | 6    |    7 |         |
|  7 | 1426884487 | [{"food" : "taco","qty" : "1" }]    | 6    |    7 |         |
+----+------------+-------------------------------------+------+------+---------+

EDIT: Users table, the user in the other table is the id in this one

+----+------+--------+--------+
| id | user |  pass  | email  |
+----+------+--------+--------+
|  1 | jim  | xxxxx  | [email protected] |
|  4 | otto | xxxxx  | [email protected] |
|  5 | tat  | xxxxx  | [email protected] |
|  6 | al   | xxxxx  | [email protected] |
+-----------+--------+--------+

Upvotes: 1

Views: 310

Answers (1)

Peter Bowers
Peter Bowers

Reputation: 3093

I'm going to assume that each table follows the same pattern as the 2 that you've shown us: id, time, user, feel, comment, plus 1 otherwise-named column (food and ailments in these cases). To be clear I will name the other columns A, B, etc. I will name each table after it's "special" column. (Next time give us some table names...)

SELECT id, time, ailments AS special, user, feel, comment, 'ailments' as src
FROM table_ailments
WHERE user = 1
UNION ALL
SELECT id, time, food as special, user, feel, comment, 'food' as src
FROM table_food
WHERE user = 1
UNION ALL
SELECT id, time, A as special, user, feel, comment, 'A' as src
FROM table_A
WHERE user = 1
UNION ALL
SELECT id, time, B as special, user, feel, comment, 'B' as src
FROM table_B
WHERE user = 1
UNION ALL
SELECT id, time, C as special, user, feel, comment, 'C' as src
FROM table_C
WHERE user = 1
ORDER by user, time

This will give you all rows from all tables ordered by user and then by time and you can tell where the row came from by looking at the src column

Upvotes: 1

Related Questions