php arrays and sql select

[paddy_fields]

hi, happy new year.

 

i am using an html form and checkboxes so a user can select one or more genres they want returned in a search. the values are sent via GET under the name genre[] so they are stored as an array. however, how do i use this with my sql query to select records that consist of any of the values of the array, eg... searching for rock, pop, dance will return any relevant events. 

 

i can assign each array value to it's own variable, but then i don't see how this works with SQL if i don't already know the number of genres being searched for.

 

headache.

 

any advice would be great.

Pat.

[PFMaBiSmAd]

You would generally use the mysql IN() operator to match a column with several values - http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in

 

You would produce the string used in the IN(...) by imploding the array of selected values.

 

<?php
$_GET['genre'][] = 'rock'; // simulate some data
$_GET['genre'][] = 'pop';
$_GET['genre'][] = 'dance';

$genres = "'".implode("','",$_GET['genre'])."'";
$query = "SELECT * FROM your_table WHERE genre IN($genres)";

echo $query;
?>

[paddy_fields]

web five.

[paddy_fields]

ah, can't seem to get it working. it is telling me that this is an invalid sql argument.

 

<?php 
include("connect.php");				  

$_GET['genre'][] = 'rock'; // simulate some data
$_GET['genre'][] = 'pop';
$_GET['genre'][] = 'dance';

$genres = "'".implode("','",$_GET['genre'])."'";
$query = mysql_query("SELECT * FROM gig WHERE genre IN('$genres')");

$row = mysql_fetch_array($query);

echo $row['gigid'];
?>

 

am i being stupid?

[paddy_fields]

yes, i was.

 

my sql value is called eventgenre, not genre.

[PFMaBiSmAd]

Not intentionally picking on you, but we only see the information that is supplied in posts and any code that is posted in a reply is just an example that shows how you might do something and comes with an implied disclaimer that it may or may not have been tested, could contain typos, syntax errors, logic errors, other oversights, and may or may not be suitable for any particular purpose. There's no guarantee expressed or implied and your actual mileage may vary.

[paddy_fields]

i am aware of that. i was using example you gave me to test the concept.