Selecting latest record and union all

[xoshbin]

hi im new here and i hope being helpfull and help me too.i have this code i want to select latest records from three tables (order by id) it works fine but its adds records of latest table on the bottom and the second one betwen first table and third table. what i want is when i add a record for the third one goes to top not in the botom here's the code:

$result = mysql_query('(SELECT colum1, colum2 FROM tbl1 ORDER BY id DESC LIMIT 2) UNION ALL(SELECT colum1, colum2 FROM tbl2 ORDER BY id DESC LIMIT 1) UNION ALL(SELECT colum1, colum2 FROM tbl3 ORDER BY id DESC LIMIT 1)');while($row = mysql_fetch_array($result)){}

[justsomeguy]

You should be able to use another ORDER BY at the end for the entire result. It's not necessary to order results from each table, you can just order all of them at the end after the union.

[xoshbin]

Thanks for your repliehere its what i did if you mean that but still not working

$result = mysql_query('(SELECT colum1, colum2 FROM tbl1) UNION ALL(SELECT colum1, colum2 FROM tbl2) UNION ALL(SELECT colum1, colum2 FROM tbl3) ORDER BY id DESC LIMIT 4');

Thanks for your time

[xoshbin]

Anyone else?

[justsomeguy]

You may need to add the id to the list of columns to select if you're going to be ordering by that. I'm not sure how you want it to work, but you may also want to put the LIMIT statements back with each table (as it is now, it gets the 4 most recent regardless of which table they come from). Other than that, if it's not working let me know what it is or is not doing that's wrong.

[xoshbin]

All of the tables has id columns. i want to select 4 records from these tables order by latest one added, and the tables has date column i have tried to order lts records by date but still not works here is how for the date

$result = mysql_query('(SELECT colum1, colum2 FROM tbl1 ORDER BY date DESC LIMIT 2) UNION ALL(SELECT colum1, colum2 FROM tbl2 ORDER BY date DESC LIMIT 1) UNION ALL(SELECT colum1, colum2 FROM tbl3 ORDER BY date DESC LIMIT 1)');

[justsomeguy]

I'm saying try something like this:$result = mysql_query('(SELECT id, colum1, colum2 FROM tbl1) UNION ALL(SELECT id, colum1, colum2 FROM tbl2) UNION ALL(SELECT id, colum1, colum2 FROM tbl3) ORDER BY id DESC LIMIT 4');Notice the IDs are in the select list. If that doesn't work, tell me what "doesn't work" means.

[xoshbin]

Ok thanks thats great worked But i tried this to select latest record by date but didnt work

$result = mysql_query('(SELECT date, colum1, colum2 FROM tbl1) UNION ALL(SELECT date, colum1, colum2 FROM tbl2) UNION ALL(SELECT date, colum1, colum2 FROM tbl3) ORDER BY date DESC LIMIT 4');

[justsomeguy]

What's different between that and the one that works?

[xoshbin]

Actually nothing, i think the problem is with my database.Thanks justsomeguy you are great you really helped me.

[justsomeguy]

PHP won't automatically print an error from MySQL, you have to check for that yourself:$result = mysql_query('...') or exit(mysql_error());

[xoshbin]

Actually it works but it returns first records not latest records

[justsomeguy]

What is the data type of the date field?

[xoshbin]

Date

[justsomeguy]

It should be sorting dates correctly as long as the dates in the table are correct. If that field is a varchar field it will sort it alphabetically.

[xoshbin]

Very big thanks for your time, I have solved the problem it was from my database i didnt add the records on date fields.