Array and database access problem. Please help

[geroid]

I'm trying to access a database using the contents of an array in the where clause as follows:

 

$query = "select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = $sumprogdate and category = '".$cat_titles[$i]."' ";

 

$cat_titles is the array which contains categories of image type. I know the array is populated and contains the values I need. If I echo the array, it displays the contents no problem. However, within this sql select statement it won't recognise the array. So if I echo the query I get this:

 

select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = 2010 and category = '' 

 

As you can see, there is no value for category at the end of the sql statement. It has not accessed the values in the array. Does anyone know why or how I can fix this? The strange thing is if I replace the variable $i in the statement with an actual index number then it works like this:

 

$query = "select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = $sumprogdate and category = '".$cat_titles[0]."' ";

 

Any ideas?

[Mark Baker]

What was the value of $i?

 

Why not use:

$query = "select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = $sumprogdate and category IN ('".implode("',",$cat_titles)."') ";

 

[geroid]

Thanks Mark

The implode statement won't work as it takes all of the values in the array at once and tries to search the database as follows:

 

select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = 2009 and category IN ('Coláiste Samhraidh',Campa Samhraidh',Mini Campa')

 

This of course generates an error.

 

There are several category values and I must search the database with each value one by one.

[Mark Baker]

select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = 2009 and category IN ('Coláiste Samhraidh',Campa Samhraidh',Mini Campa')

will generate an error because I missed a comma in the implode. It should be

implode("','",$cat_titles)

If you really have to loop through and issue several select statements (I can't understand why you need to):

foreach($cat_titles as $cat) {
   $query = "select image_id, image_name, category, prog_date from imagesumprog WHERE `prog_date` = $sumprogdate and category = '".$cat."' ";
   ...
}

 

[geroid]

Thanks for the help Mark. I'm working on that now. Much appreciated