select last record from 2 tables

select last record from 2 tables with PHP and MySQL

[Majid]

Hello,

I have 2 tables.
one is 'contact' and another one is 'comments' .
Now I want to select last 20 records from these 2 tables.
how can I do this ?

for example I have 50 records in 'contact' and 100 records in 'comment' and now I want write a program to show 20 latest records, because the ORDER is date some times may be select only 1 record from 'comment' and 19 records from 'contact' and some times 10 records from 'comment' and 10 records from 'contact'.

[ngrant]

are you looking to get twenty from each? or just the last twenty additions total?

[Majid]

twenty from both table in total

[Majid]

anyone cannot help me?

[topr8]

you can combine the 2 tables using a UNION clause and order them by the date field,

in order to do this the data you are SELECTing must be of a similiar type, so without knowing your fieldnames or types i would guess you do something like this

(SELECT comment FROM comments) UNION (SELECT contact FROM contacts)
ORDER by datefield LIMIT 20;

check out the manual here [dev.mysql.com]

[Majid]

Thanks for help,

according to your help my query is :

(SELECT *
FROM program_comment
WHERE `show` = 'Y'
AND reply_admin = '0')
UNION
(SELECT *
FROM faq_data
WHERE `show` = 'Y'
AND reply = '0')
ORDER BY date DESC
LIMIT 20

but I get this error :
#1222 - The used SELECT statements have a different number of columns

would you please help me again ?

[topr8]

well do program_comment and faq_data have a different number of columns, if yes then that your problem.

don't use the *, list the column names that you actually want, you should request the same number of columns from each table and in the same order and of the same type.
eg if the first column you name from program_comment is a varchar then the first from faq_data should be the same.

if you really need to request more columns from the first table then in the second select statement you need to add columns like this:
SELECT '' as missingcolumnname
or like this:
SELECT NULL as missingcolumnname2

[Majid]

Thanks again topr8 for your help,

I change my code to this :

(SELECT id, name
FROM program_comment
WHERE `show` = 'Y'
AND reply_admin = '0')
UNION
(SELECT id, name
FROM faq_data
WHERE `show` = 'Y'
AND reply = '0')
ORDER BY date DESC
LIMIT 20

but I get this error :
Unknown column 'date' in 'order clause'

would you please help me?

[topr8]

is there a date field in both tables?

if so you'll have to select it in both select statements ...

(SELECT id, name, date
FROM program_comment
WHERE `show` = 'Y'
AND reply_admin = '0')
UNION
(SELECT id, name, date
FROM faq_data
WHERE `show` = 'Y'
AND reply = '0')
ORDER BY date DESC
LIMIT 20

this should work, does it give you the result you expect?

[Majid]

Thank you very much dear topr8,
my problem solved with your help.

Thanks for your kindness