[RESOLVED] Helpwith query please!

[tripodkid]

Hello!

I'm really strugggling with this script and I was hoping someone could help me with it.

I have two tables, events and images. I also have two variables, $event_date and $event_id.

I want to get the data from column image_path but only if it matches $event_date and $event_id. Pulling data from image_path is easy because event_id is a foriegn key on the images table. But I need to go to the events table and check that the event ID matches the event date and only return the image_path records that do.

Many thanks, Chris

Here's what I've been generating headaches with!

$rs_event_images = mysql_pconnect($hostname, $username, $password);

mysql_select_db($database, $rs_event_images);
$rs_images = mysql_query('SELECT image_path FROM images JOIN events ON images.event_id = events.event_id 
WHERE images.event_id =' . $event_id . 'AND events.event_date =' . $event_date);

$row_rs_images = mysql_fetch_assoc($rs_images);

$totalRows_rs_images = mysql_num_rows($rs_images);

mysql_free_result($rs_images);

[Roger_Ramjet]

Sound like your table schema is wrong. Post the full table definations here and we'll see what can be done.

[tripodkid]

Thanks!

The events table has two columns:
event_id which I set as an interger, unsigned, primary key.
event_date which I set to text as it is inputted manually.

The images table has five columns:
image_id which I set as an interger, unsigned, autoincrement, primary key.
event_id
image_name which is a text field
image_path which is a text field
thumb_path which is also a text field.

What I'm trying to do is allow users to see images from an event but only if they know the event ID and date.

Thanks again!

Chris

[Roger_Ramjet]

Right. Well I was a bit quick with my first post cos I can now see the p[roblem. I'm not going to tell you what it is - I'm going to tell you how to find out what it is for yourself.

Simply echo out the query string after you have built it - the syntax error should be obvious then.

While you are at it, do the WHERE part for both the vars on the events table - should be just a bit more efficient. Whenever possible, use the primary key column not the foreign key column for the where clause when you have joined 2 tables.

Oh yes, and get into the habit of using explicit joins, in this case it should be INNER JOIN. Yours will work because that is the default, but get into the habit of writing strict sql syntax so that you can move from database to database without having to learn it all over again.

[tripodkid]

Yep! I echoed the query as you suggested then immediately saw the problem!

No more syntax errors yippee! 🙂

The only problem is the query is not returning any results. I've checked the two variables and they do contain the correct POST data and I've checked the db and there are records that correspond with the data in the two variables. 😕

Here's my latest query.

Thanks!

mysql_select_db($database, $rs_event_images); 
$rs_images = mysql_query('SELECT * FROM images INNER JOIN events ON images.event_id = events.event_id 
WHERE events.event_id = ' . $event_id . ' AND events.event_date = ' . $event_date); 

[Roger_Ramjet]

So break it down into stages, just query for the event_d first. If that works then you know that there is something wrong with the date part of the query, and you'll have examples of the dates to compare with your post event_date.

[tripodkid]

Thanks for your help!

Following your advice I found the date was the problem because it was not formatted correctly.

Chris

[Roger_Ramjet]

OK, so now you know the basic approach to problem resolution:

Break it down into basic stages, test each part seperately and make one change at a time untill the problem appears again, then you know what to fix.