Relevant Info via Multiple Tables

[soloman]

Up until now I've used single tables so all the relevant info was contained on the same row, not a whole lot of creative code involved. NOW.. I'm trying to pull additional information based on a venue which is contained on separate tables to reduce redundancy.

 

Essentially it breaks down into "events" (startdate, name, venue, cover, starttime) and then "venues" (name, address, phone, website, city, province).. what I'm trying to accomplish is based on the venue determined by the events table, I want to include the venue info below it.

 

I've researched the hell out of this and have dug up tons of doc on selecting multiple tables which makes sense to me, but hasn't helped inspire a solution. I'm insanely under the gun so I'm learning all this the dunce way, total trial and error.. mostly error.

 

I've omitted the HTML formatting, once I get the variables declared I think I'll be ok. Thanks!!

 

$result = mysql_query("SELECT date_format(starttime, '%l:%i%p') as starttime, date_format(enddate, '%b %d') as enddate, name, venue, province, cover FROM events WHERE enddate >= CURDATE() LIMIT 0 , 1");
if (!$result) {
    die("ERROR:EVE");
}

$fields_num = mysql_num_fields($result);

$venvar = "{$row['venue']}"

$venuelab = mysql_query("SELECT name, photo, address, city, province, web, phone, logo FROM venues WHERE name == {$venvar} LIMIT 0 , 1");
if (!$venuelab) {
    die("ERROR:ADAM");
}

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "

[riwan]

well, the way I see it you haven't fetch the venue value yet.

below should fix it

$row = mysql_fetch_array($result);
$venvar = $row['venue'];

[soloman]

EXCELLENT. I've got that pulling the venue details based on the event's venue..

 

problem now is I screwed up and limited the query to 1 so I'm back to trying to include the venue details under each event, instead of a dozen events stacked with a dozen venue details all at the bottom.

 

is there a way to include the $venuelab variables into the "while" tag at the bottom? or will I have to figure out a way to include everything in the initial $result query so that it automatically repeats itself per event?

[Anzeo]

You can try to use a JOIN statement in your query. Can you provide the structure of the two tables you're fetching information from?

[riwan]

As suggested its better to use Join but if you still want to use your current script, then try moving the venuelab inside the while

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{

$venvar = $row['venue'];
$venuelab = mysql_query("SELECT name, photo, address, city, province, web, phone, logo FROM venues WHERE name == {$venvar} LIMIT 0 , 1");
if (!$venuelab) {
    die("ERROR:ADAM");
}
$row2 = mysql_fetch_array($venuelab, MYSQL_ASSOC)); //fetch the venuelab result and store it to row2 var

echo "

[soloman]

ALRIGHT! I scrapped the original code and got the listing resolved.. this lists EVERYTHING after today. Now I want to set it to only show one month at a time..

 

$result = mysql_query("SELECT venues.address, venues.venuenum, events.enddate, events.venuenum FROM venues, events WHERE events.venuenum = venues.venuenum AND events.enddate >= CURDATE()") or die(ERROR:EVE());

 

I tried events.enddate = '2010-06-*' (lists nothing) and events.enddate ^'2010-06' (lists everything)

 

 

[Anzeo]

Maybe this could help you out? (haven't tested it myself)

$result = mysql_query("SELECT venues.address, venues.venuenum, events.enddate, events.venuenum
FROM venues, events WHERE events.venuenum = venues.venuenum 
AND events.enddate BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 1 MONTH)") or die(ERROR:EVE());