Using foreach to print out arrays to form a search query

[harold]

Hi,

I try to use foreach to print out a part of an mysql query, but always the last one cancels the previous one out. Please advise me on what to do:

 

$searchterm=$_POST['searchterm'];

 

 

if ($type) {

foreach($_POST['type'] as $type) {

$search_type = "AND type = '$type' ";

}

} else {

$search_type = " ";

}

 

if ($year) {

foreach($_POST['year'] as $year) {

$search_year = "AND DATE_FORMAT(dateofissue,'%Y') = '$year' ";

}

} else {

$search_year = "  ";

}

 

if ($theme) {

foreach($_POST['theme'] as $theme) {

$search_theme = "AND keywords LIKE '%*$theme*%' ";

}

} else {

$search_theme = " ";

}

 

if ($geography) {

foreach($_POST['geography'] as $geography) {

$search_geography = "AND geography = '$geography' ";

}

} else {

$search_geography = " ";

}

 

$search_query = "SELECT * FROM library WHERE (title_en LIKE '%$searchterm%' OR  fulltext_en LIKE '%$searchterm%') $search_type $search_year $search_theme $search_geography ORDER BY dateofissue DESC, type DESC, title_en DESC";

 

 

The original values come from a form which one fills in. So geography[] is parsed to this and it can have one or more values. For example, if geography[]=africa and geography[]=asia, the query will only show "AND geography=asia" as the latter comes later.

 

However the thing would work if I put the variable in the URL, like thisfile.php?geography[]=africa&geography[]=asia

 

Can someone help me?

 

[craygo]

you have to separate the geography values from the name. right now it is looking for geography to = all the values

 

if($geography){
$sg = implode(", ", $_POST['geography']);
$search_geography = "AND geography IN ('$sg') ";
}

 

You can simplify the others the same way if you are passing each POST as an array.

 

Ray

 

[JD*]

Or you can use concatenation. The reason your variables are being overwritten is because every loop you're tell it to set the variable to the current value, not add on.

 

change your strings to be:

 

$search_geography.= "AND geography = '$geography' ";

 

That .= means "take the current $search_geography variable and add this (everything after the .=) to the end of it"

[harold]

hiya guys,

 

Thanks!! It works!! It's great receiving such support and knowledge from the community!

PHPFREAKS.com rocks!!