Mutiple Table Queries in SQL and PHP

fluiduk · 26 Jul 2006 at 16:28

Hi All

I need to get some data from multiple tables using a query so that I can then output this data on the page.

The querys need to do the following.

Select * from events SORT by id ASC limit 4
Select * from flyers where eventid = "id" (the id above)

Now the hard part is combining the 2 into one query. So that I can print the results out together, as I need to place the data in a repeat region.

Hope this makes sense, as i suspect it may not lol

Thanks
Aaron

paulsheff · 26 Jul 2006 at 21:25

Hmm, not sure whether it would consider * as ambiguous.

Anyway you probably want some thing along the lines of:

Code:

SELECT events.column_name, flyers.column_name 
FROM events, flyers 
WHERE events.id=flyers.eventid 
ORDER BY event.id ASC 
LIMIT 4

fluiduk · 26 Jul 2006 at 21:35

THanks will give that a go

Aaron

Dj_Jestar · 26 Jul 2006 at 22:24

or a join:

Code:

SELECT `events`.`col`, `flyers`.`col` 
  FROM `events` 
    JOIN `flyers`
    ON `events`.`id` = `flyers`.`eventid`
  SORT ASC
  LIMIT 4;

fluiduk · 27 Jul 2006 at 16:33

Hi guys

This is my sql query. I have had to do some date formatting in the query

Code:

SELECT events.id, events.EventName, events.imageurl, flyers.ID, flyers.Flyername, flyers.ImageUrl, DATE_FORMAT(events.EventDate,'%W %D %M %Y')
FROM events, flyers
WHERE events.imageurl=flyers.id
ORDER BY events.id ASC

When I test the query in Dreamweaver it outputs the correct date, but I just cant get it to output in on the page

This is the echo that I am using

Code:

<?php echo $row_recEvents['DATE_FORMAT(events.EventDate']; ?>

I get nothing

PLease help

Ta
Aaron

MossyUK · 27 Jul 2006 at 20:10

<?php echo $row_recEvents['DATE_FORMAT(events.EventDate)']; ?>

im not sure that will work, but your brackets and 's were not correct.

Dj_Jestar · 27 Jul 2006 at 22:35

Code:

SELECT `events`.`id`,
    `events`.`EventName`,
    `events`.`imageurl`,
    `flyers`.`ID`,
    `flyers`.`Flyername`,
    `flyers`.`ImageUrl`,
    DATE_FORMAT(`events`.`EventDate`,'%W %D %M %Y') as EventDate
FROM `events`, 
     `flyers`
WHERE `events`.`imageurl` = `flyers`.`id`
ORDER BY `events`.`id` ASC

Code:

<?php echo $row_recEvents['EventDate']; ?>

fluiduk · 28 Jul 2006 at 10:43

Thanks, that worked perfectly! Gold star for you

Thanks
Aaron