Query over 2 & more columns

[Anon]

I've got following problem:
I made a searchengine with some checkboxes for music-links. These links are in a simple table in mySQL. Every Checkbox like land, category, style has to search in just one column. But alternative-category has to search in two columns (alternativkat & alternativkat2). The menue is in search.phtml. The result comes in result.phtml.

Example for searchfield "aletnativcategory in such.phtml:
<td align="right"><p>Alternativkategorie: &nbsp </td>
<td align="left" valign="top" colspan="4">
<?PHP
$dbh = mysql_connect('rdbms','www.internetname.de','passwort');
mysql_select_db("DB0000");
$err = mysql_error();
if($err){
echo "Oh, something wrong<br>".$err;
}
$query = "SELECT kurz,Auswahlmenue_Eintrag_fuer_Suchformular FROM
Alternativkat WHERE ShowInCombo=1 ORDER BY
Auswahlmenue_Eintrag_fuer_Suchformular";
#echo $query.'<br>';
$res = mysql_query($query, $dbh);
$err = mysql_error();
if($err){
echo "Oh, da ist wohl etwas falsch gelaufen<br>".$err;
}
echo "<select name='q_altkat' size='1'>";
echo '<option value="kein">egal </option>';
for ($i = 1; $i <= mysql_num_rows($res); $i++){
$row = mysql_fetch_row($res);
$err = mysql_error();
if($err){ echo "Oh, da ist wohl etwas falsch gelaufen<br>".$err; }
echo "<option value=$row[0]>$row[1]</option>";
}
echo "</select>";
?>

My php-code in result.phtml is only able to search in one column:

example function on result.phtml:
function addCondition($colName, $condition){
global $haveConditions;
if ($haveConditions==1) {$retval = ' and ';}
else {$haveConditions = 1; $retval = ' where ';}
$retval = $retval." upper(".$colName.") = upper('".$condition."')";
return $retval;
}

example if-code:
$query = "select name, bsp_content, url, ID, vorname, ImageFileName from
tontransfer";
if ($q_altkat != 'kein') {$query = $query.addCondition('alternativkat',
$q_altkat);};

I think here must be the error. How can I manage, that a query looks even in "alternativkat2"
Thanks
joerg

[Anon]

Firstly it looks like you are building the ORDER BY clause and then appending another WHERE clause.

Echo out your query after each function jacks with the query string. When building extensive Where clauses, you should use parentheses to clarify.

The following is a basic example where we are searching several columns for multiple values.

if ($st) { $loc = "where (Ads.CatID = AdDirectory.ID and  AdDirectory.Category = '$cat' and Ads.State = '$st' and Approved = '1') || (Ads.CatID = AdDirectory.ID and AdDirectory.Category = 'cat' and State = 'All' and Approved = '1')";
if ($cnty) { $loc = "where (Ads.CatID = AdDirectory.ID and AdDirectory.Category = '$cat' and Ads.State = '$st' and County = '$cnty' and Approved = '1') || (Ads.CatID = AdDirectory.ID and AdDirectory.Category = 'cat' and State = 'All' and Approved = '1')";
if ($city) { $loc = "where (Ads.CatID = AdDirectory.ID and AdDirectory.Category = 'cat' and County = '$cnty' and State = '$st' and City = '$city' and Approved = '1')||(Ads.CatID = AdDirectory.ID and AdDirectory.Category = 'cat' and State = 'All' and Approved = '1')";
		}
	}
}else{
$loc = " where Ads.CatID = AdDirectory.ID and AdDirectory.Category = '$cat' and Approved = '1'";
}

$criteria .= $loc;
$query="select Ads.*, AdDirectory.Category from Ads, AdDirectory ".$criteria." group by Ads.Title";

Note the table.field syntax, the parentheses and the group by. Not always necassary but helps hold things together for readability and future enhancement.