Help with search

[rakuci]

Hi guys
I really need your help I have a search form with 2 text files and 2 select boxes

<form name="search" action="" method="post">
<table width="480" border="1" cellpadding="3" cellspacing="0" bordercolor="#CC3300">
<tr valign="top">
<td width="180" bgcolor="#FF5F00">Keywords:</td>
<td bgcolor="#FF9966" class="bodyzwart">
<input name="keywords" type="text" id="keywords" size="30">
</td>
</tr>
<tr valign="top">
<td width="180" class="kopwit">Images:</td>
<td bgcolor="#FF9966">
<select name="soort_foto">
<option selected value="">Other</option>
<?
$sql_status = "SELECT * FROM soort_foto order by soort_foto_id asc";
$result_status = mysql_query($sql_status);

while ($row_status = mysql_fetch_array($result_status)) {

$soort_foto_id = $row_status["soort_foto_id"];
$soort_foto_naam = $row_status["soort_foto_naam"];

if($soort_foto == $soort_foto_id){
?>
<option selected value='<?=$soort_foto_id?>'><?=$soort_foto_naam?></option>
<? } else { ?>
<option value='<?=$soort_foto_id?>'><?=$soort_foto_naam?></option>
<?
}
}
?>
</select>
</td>
</tr>
<input name="Search" type="submit" value="Search">
<input type="hidden" name="zoek" value="yes">
</td>
</tr>
</form>
<?
if(isset($zoek)){ if ($zoek=="yes"){ ?> <p>Results:<br>
<br>
</p>
Here is the trouble I want that user can search on keywords or Images
This is my query
$sql_zoek = "SELECT * FROM foto WHERE keywords LIKE '%$keywords%' OR soort_foto_FK = '$soort_foto’ ";

But if the user don’t search on keywords only Images the query shows everything
I want to combine those two
Any ideas ???

[bad76]

Hi

when user don't search for keyword your sql statement is "... WHERE keywords LIKE '%%' ..." that equals to "all".

Just check if $keywords isset:

$sql_zoek = "SELECT * FROM foto WHERE soort_foto_FK = '$soort_foto’ "; 
if (isset($keywords))
 $sql_zoek.=" OR keywords LIKE '%$keywords%'";

see you

[rakuci]

I tryed with this
search.php
<form name="zoeken" action="zoek_result.php" method="post">
<table width="480" border="1" align="center" cellpadding="3" cellspacing="0" bordercolor="#CC3300">
<tr valign="top">
<td width="180" bgcolor="#FF5F00" class="kopwit">Trefwoord(en):</td>
<td bgcolor="#FF9966" class="bodyzwart">
<label>
<input name="trefwoord" type="text" id="trefwoord" size="30">

						  </label></td>
						  </tr>
						  <tr valign="top"> 
						   <td width="180" class="kopwit">Soort foto:</td>
						   <td bgcolor="#FF9966" class="kopzwart"><span class="bodyzwart"> 
							<select name="soort_foto" class="bodyzwart">
							<option selected value="">Geen voorkeur</option>
							<?
							$sql_status		=	"SELECT * FROM soort_foto order by soort_foto_id asc";
							$result_status	=	mysql_query($sql_status);
							while ($row_status = mysql_fetch_array($result_status)) {
							$soort_foto_id     = $row_status["soort_foto_id"];
             				$soort_foto_naam   = $row_status["soort_foto_naam"]; 
							if($soort_foto == $soort_foto_id){
						   ?>
							<option selected value='<?=$soort_foto_id?>'><?=$soort_foto_naam?></option>
							<? } else { ?> 
							<option value='<?=$soort_foto_id?>'><?=$soort_foto_naam?></option>

						  <?
						  }
						  }
						  ?>
						  </select>
						  </span></td>
						  </tr>
						  <tr valign="top"> 
						   <td width="180" class="kopwit">Van:</td>
						   <td bgcolor="#FF9966" class="kopzwart"><span class="bodyzwart"> 
							<select name="onderwerp" class="bodyzwart">
							<option selected value="">Geen voorkeur</option>
							<?
							$sql_status		=	"SELECT * FROM onderwerp order by onderwerp_id asc";
							$result_status	=	mysql_query($sql_status);
							while ($row_status = mysql_fetch_array($result_status)) {
							$onderwerp_id   = $row_status["onderwerp_id"]; 
							$onderwerp_naam = $row_status["onderwerp_naam"];

							if($onderwerp == $onderwerp_id){								
						   ?>
							<option selected value='<?=$onderwerp_id?>'><?=$onderwerp_naam?></option>
							<? } else { ?> 
							<option value='<?=$onderwerp_id?>'><?=$onderwerp_naam?></option>

							<?
							}
							}
							?>
						  </select>
						  </span> </td>
					  </tr>
					  <tr> 
						<td valign="middle" class="kopwit">Rechten:</td>
						<td valign="middle" class="kopzwart" bgcolor="#FF9966"><select name="rechten" class="bodyzwart">
							<option selected value="">Geen voorkeur</option>
							<?
							$sql_status		=	"SELECT * FROM rechten order by rechten_naam asc";
							$result_status	=	mysql_query($sql_status);
							while ($row_status = mysql_fetch_array($result_status)) {
							$rechten_id   = $row_status["rechten_id"]; 
							$rechten_naam = $row_status["rechten_naam"];

							if($rechten == $rechten_id){
							?>
						     <option selected value='<?=$rechten_id?>'><?=$rechten_naam?></option>
							<? } else { ?> 
							<option value='<?=$rechten_id?>'><?=$rechten_naam?></option>

							<?
							}
							}
							?>
						  </select></td>
						</tr>
						<tr> 
						<td valign="middle" class="kopwit">Kleur:</td>
						<td valign="middle" class="kopzwart" bgcolor="#FF9966">
						<select name="kleur" class="bodyzwart">
						<option selected value="">Geen voorkeur</option>
						<?
						$sql_status		=	"SELECT * FROM kleur order by kleur_naam asc";
						$result_status	=	mysql_query($sql_status);
						while ($row_status = mysql_fetch_array($result_status)) {
						$kleur_id   = $row_status["kleur_id"]; 
						$kleur_naam = $row_status["kleur_naam"];

							if($kleur == $kleur_id){
							?>
						     <option selected value='<?=$kleur_id?>'><?=$kleur_naam?></option>
							<? } else { ?> 
							<option value='<?=$kleur_id?>'><?=$kleur_naam?></option>

							<?
							}
							}
							?>
						  </select></td>
						</tr>
						<tr> 
						<td valign="middle" class="kopwit">Gebruikt voor/in:</td>
						<td valign="middle" class="bodyzwart" bgcolor="#FF9966">
						<input type="text" name="gebruikt_voor"></td>
						</tr>
						<tr valign="top"> 
						<td class="kopwit">&nbsp;</td>
						<td class="bodyzwart"><label> 
						<input name="Toon resultaten" type="submit" id="Toon resultaten"  value="Toon resultaten">
						<input type="hidden" name="zoek" value="yes">
						</label> </td>
						</tr>
						</table>
	       </form>

result.php
<?
if(isset($zoek)){
if ($zoek=="yes"){ ?>
<p><span class="bodyzwart">heeft de volgende resultaten opgeleverd:<br>
<br><br>
</span></p>
<table cellspacing="0ohhhh" cellpadding="5" border="1" width="652" bordercolor="#CC3300">
<tr>
<td>
<table width="649" border="0" align="center" cellpadding="6" cellspacing="1" bgcolor="#FF5F00">
<tr>

        <td width="635" height="66" colspan="2" valign="top" class="kopzwart"> 
          <table width="33%" border="0">
           <? 
				  $count =1;  

				 $sql_zoek = "SELECT * FROM foto WHERE "; 
				  if(isset($trefwoord)){
				 $sql_zoek.=" trefwoorden LIKE '%$trefwoord%'";
				 }

				 if (isset($soort_foto)) 
				  $sql_zoek.=" AND soort_foto_FK = '$soort_foto'";

				 if(isset($onderwerp)){
				 $sql_zoek.=" AND onderwerp_FK = '$onderwerp'";
				 }
				 if((isset($rechten))&&($rechten<>"")){
				 $sql_zoek.=" AND rechten_FK = '$rechten'";
				 }
				 if((isset($kleur))&&($kleur<>"")){
				 $sql_zoek.=" AND kleur_FK = '$kleur'";
				 }
				  if((isset($gebruikt_voor))&&($gebruikt_voor<>"")){
				 $sql_zoek.=" AND gebruikt_voor = '$gebruikt_voor'";
				 }

echo $sql_zoek;

					$result_zoek = mysql_query($sql_zoek);

					if (!mysql_num_rows($result_zoek)==0){
					 while($rowArray = mysql_fetch_array($result_zoek)){
					 if($count %4 ==1)
					 print("<tr>");
					 print("<td align='center'>");
						$foto_id   = $rowArray["foto_id"];
						$foto      = $rowArray["foto"];

?>
But the problem is if you don't search on one of the fields he can not finde nothing I want comination of one or another if user don't want to search on trefwoorden then soort_foto with others is also a option

[bad76]

Hi,
you must check when to put "AND" or not.
The first no, each other yes.

[rakuci]

$sql_zoek = "SELECT * FROM foto";

if((isset($trefwoord))&&($trefwoord<>"")){
$sql_zoek.=" WHERE trefwoorden LIKE '%$trefwoord%'";
}
if ((isset($soort_foto))&&($soort_foto<>"")){
$sql_zoek.=" WHERE soort_foto_FK = '$soort_foto'";
} else {
$sql_zoek.=" AND soort_foto_FK = '$soort_foto'";
}

The problem is if user search on first filed then is:
SELECT FROM foto WHERE trefwoorden LIKE '%vogel%' this is Ok also when the user search only on the second field I get SELECT FROM foto WHERE soort_foto_FK = '1' this OK but if I combine both there is error SELECT FROM foto WHERE trefwoorden LIKE '%vogel%' WHERE soort_foto_FK = '1'
I can't make combination where to use WHERE AND
For example if he search on both then should be SELECT FROM foto WHERE trefwoorden LIKE '%vogel%' AND soort_foto_FK = '1'
I can't make it work
Regards

[bad76]

Try this:
Init a variable $first=1. When add a string to query set it to 0. Check if is 0 or 1 to know if put " AND " or not:

$sql_zoek = "SELECT * FROM foto"; 
$first=1;

if((isset($trefwoord))&&($trefwoord<>""))
if($first==1){ 
$sql_zoek.=" WHERE trefwoorden LIKE '%$trefwoord%'"; 
$first=0;
} else {
$sql_zoek.=" AND trefwoorden LIKE '%$trefwoord%'"; 
}

if ((isset($soort_foto))&&($soort_foto<>""))
if($first==1){ 
$sql_zoek.=" WHERE soort_foto_FK = '$soort_foto'"; 
$first=0;
} else { 
$sql_zoek.=" AND soort_foto_FK = '$soort_foto'"; 
} 

// and so on...

This code need many optimization, but is clearest.

[rakuci]

Got it thanx mate