Evening all,
I've created a CMS for my site, which is working (yay!), but I'm trying to tweak it so it works more dynamically. Basically we have 2 main locations (London and Oxford). Currently my events calendar is rather hard coded, with two columns in a table, each running SQL statment to the effect of "select stuff from table where location = london/oxford" as appropriate. I've included an option in the CMS to add a new location, however with the hardcoding, this new location won't appear in a new column.
I've successfully connected to the location table (event_loc) and managed to start a table with a header row, basically creating the layout of 3 columns as it is now (I've added 'Edinburgh' as a venue'). This works OK using my while() loop.
The problem I'm having is that contained within the while loop I'm running another sql query, connecting to the main events table (and related tables) with a where clause saying "where fk_location = $locid". in this case fk_location is the integer stored in the events table, and $locid is the id of the location in the location table. Trouble is, it's pulling incorrect results through! Basically there should be about 15 results for London, 10 for oxford, and none for edinburgh.
What I'm getting is every results for every location in every column. I figured the same SQL query I used in the original hardcoded version would work, simply changing "London" or "Oxford" for "$locid" - but it doesn't appear to be the case! Is it because I'm trapped in a while() loop?
My code is below - could someone please point out where I'm going wrong?
<?php
$locquery = $connector->query('SELECT * FROM event_loc')or die(mysql_error());
if (mysql_num_rows($locquery) > 0)
{
?>
<table width="100%" id="venue" align="center">
<tr>
<?php
while ($loc = mysql_fetch_array($locquery))
{ //START LOCATION WHILE LOOP
//give a row displaying all available locations - THIS IS WORKING
?>
<td width="200" align="center"><h5><?php echo $loc['event_loc_desc']?></h5>
<?php
$locid = $loc['id']; //THIS BIT IS WORKING AND CORRECTLY SHOWING THE ID
echo $locid.'<br>';
$eventquery = $connector->query('SELECT
d.id AS eventid,
d.fk_event_type,
DATE_FORMAT(d.date,"%d %M %Y") AS date,
d.time,
d.price_members,
d.price_nonmem,
d.fk_venue,
d.fk_location,
d.event_desc,
d.contact,
d.end_time,
d.end_time,
d.event_name,
d.active,
l.id,
l.event_loc_desc,
t.id,
t.type_desc,
v.id,
v.venue_name
FROM sypcms.event_details d, sypcms.event_loc l, sypcms.event_type t, sypcms.event_venue v
WHERE d.fk_event_type = t.id
AND
d.fk_venue = v.id
AND
d.fk_location = l.id
AND
d.fk_location = "$locid"
AND
DATE_FORMAT(d.date,"%Y-%m-%d") >= DATE_FORMAT(NOW(),"%Y-%m-%d")
ORDER BY
d.date ASC') or die(mysql_error());
if (mysql_num_rows($eventquery) > 0)
{
while ($detail = mysql_fetch_array($eventquery))
{
echo $detail['event_name'];
}
//echo 'results found';
}
else
{
echo 'no results';
}
?>
<!-- End cell for holding the event infomation -->
</td>
<?php
} //END GETTING LOCATION WHILE LOOP
?>
</table>
<?php
}
else
{
echo 'no locations - this ain\'t worked!';
}
?>
