Querying Two Tables

[chernove]

I am sure this is something very simple, but I seem to be missing one piece of the puzzle here . . .

On one of my wife's MySQL databases (I'm the webmaster on her FQ-hosted site), I have two different tables: events and venues.

In the events table I have a field called "venueid."
In the venues table I have the following fields:

id
venue
contact
address1
address2
phone

When people go to her site, they can see her upcoming concerts in a table listing the date, day, time and cost of the event (this info. is taken from "events"). What I'd like to do is have the info. for the venue looked up and displayed, too, based on the venueid (which is matched by the id in "venues"). The reason I have these separated into two tables is that she plays at some venues quite often and I'd like to just be able to put in "yeah, this one's at venue #5" rather than reenter all the info. for venue #5.

For example, let's say she has an upcoming concert at Weill Recital Hall in NYC. There would be an entry in the venues table with the contact info for that hall. For the sake of argument, let's give that hall id #3 (in the venues table).

An example of the entry for the event in the events table would be as follows (I'll fill in most of the fields, just so you get the idea; most are immaterial for my current question):

id = 20
status = current [this is as opposed to "archive"]
day = Thursday
month = 11
date = 04
year = 2004
time = 5:30
tod = PM
venueid = 3
guest1 = John Doe, violin
guest2 = Jim Doe, clarinet
summary = The Doe Brothers are special guests in this evening's concert.
cost = 20
program = Chopin: Ballade in G minor, op. 23<br>Bartok: Contrasts<br>Beethoven: Sonata #32 in C minor, op. 111

All of the above information would be available to be displayed, but I want venueid (3) to be replaced, of course, with the actual information for that venue (id #3 in the venues table).

Any help would, as always, be greatly appreciated.

Thanks,
Eric

[Winters]

What does the query that you are currently using look like?

[chernove]

Funny enough, in an effort to clean up my coding "experiments" to clip a bit of the code for you, I actually ended up solving my problem! At least temporarily! :crazy:

For some reason I had failed to make the association of tables in the mysql_query.

I couldn't find a smiley of the Homer Simpson-esque :doh: variety, but it would be appropriate to enter it here! (Perhaps someone could make one with a smiley slapping itself on the head). :hehe:

Thanks much,
Eric

[gymshoe]

Provided you don't have similar field names in your venues and events table, you can query both tables at the same time based on the venueid and id in the venues table matching. Query would be something like this:

SELECT events.*, venues.* FROM events, venues WHERE event.id = 20 AND events.venueid = venues.id;

however, since you have a field called id in both tables, you can't use the wildcards, ..you'll have to do something like:

SELECT events.*, venues.id, venues.venue, venues.contact, venues.address1, venues.address2, venues.phone FROM events, venues WHERE venueid = venues.id AND events.id = 20;

-James

[chernove]

Hi,

Thanks for the advice.

I actually ended up using this (see last post by me):

SELECT events.*,venues.* FROM events,venues WHERE status LIKE 'current' AND venues.id=events.venueid order by year asc,month asc,date asc,time asc

Seems to work fine for the current purpose.

Thanks,
Eric