Querying 2 Mysql Tables

PHP
1

Hi, I am confused I have searched all over and not sure how to go about querying 2 tables and getting results from both.

Basically I have a table for videos and pictures. Both tables contain these columns:

ID - TITLE - DESCRIPTION - DATE

I want to select those columns from both tables and display them in an array on my index page. So basically showing the latest pictures and videos on the index by querying the 2 tables.

For example in the video table there is:

23 - Video Title - Video Description - 23254345
24 - Video Title - Video Description - 23256575
25 - Video Title - Video Description - 23256576

and in the picture table there is:

23 - Picture Title - Picture Description - 23254346
24 - Picture Title - Picture Description - 23254347
25 - Picture Title - Picture Description - 23254348

I want the result to show:

23 - Video Title - Video Description - 23254345
23 - Picture Title - Picture Description - 23254346
24 - Video Title - Video Description - 23256575
24 - Picture Title - Picture Description - 23254347
25 - Video Title - Video Description - 23256576
25 - Picture Title - Picture Description - 23254348

2

I’m no expert either, but i think you need to look at ‘JOINS’ as in ‘inner join’ etc.

3 

select
   *
from
 picturesTable p,
 imagesTable i
where
 p.ID = i.ID

substitute in for the table and ID names.

see http://www.tizag.com/sqlTutorial/sqljoin.php

4 

select p.id
     , p.picture_title
     , p.picture_description
     , p.time
     , v.id
     , v.video_title
     , v.video_description
     , v.time
  from picture p
  left join video v
    on p.id = v.id
5

Use “left outer join” or “right outer join” or “inner join”, depends upon your requirement.

Do you have all the records are same in table 1 and table 2?

Do you want only matching records?, then use inner join.

6

those rows don’t match, so you cannot join them

(ah, the confusion sown by those pesky auto_increments…)

what is required here is a UNION

SELECT id
     , title
     , description
     , `date`
  FROM pictures
UNION ALL
SELECT id
     , title
     , description
     , `date`
  FROM videos
ORDER
    BY `date` DESC

notice also the ORDER BY clause – this satisfies the original requirement of showing the latest pictures and videos (so we must disregard the sample output shown in id sequence)

i’m assuming this is a mysql application? hence the backticks around the reserved word date