Search with PHP & MySQL.

[Frederik]

Hello

 

I wonder if it is possible to make a search form with different fields where all conditions should be met if the field is set.

 

I have this code:

 

$sel = "SELECT * FROM database ";

  $sel .= "WHERE ((test1 = '$test1' ";

  $sel .= "AND test2 = '$test2' ";

  $sel .= "AND test3 = '$test3' ";

  $sel .= "ORDER BY id ";

  $query = mysql_query($sel) or die(mysql_error());

  if (mysql_num_rows($query) == 0){

    echo ("No result");

exit;

 

 

When I use this and leave for example 1 field empty I get the error:

No result.

 

 

Is it possible to make smothing so the condition only should be met if the field is set?

So If test1 and test3 are set these condition should be met..

 

 

I hope you can help!

 

 

Thanks in advance!

 

 

 

- Frederik

[JKG]

if(!empty($test3)){
  $sel .= "AND test3 = '$test3' ";
}

 

or

 

if(!isset($test3)){
  $sel .= "AND test3 = '$test3' ";
}

 

you may want to look into the LIKE operator too.

[Frederik]

Now I have this code:

 

  $sel = "SELECT * FROM brugergruppe ";
  $sel .= "WHERE ((postnummer = '$postnummer' ";
  if(!isset($town)){
  $sel .= "AND town = '$town' ";
  } else if(!isset($koen)){
  $sel .= "AND koen = '$koen' ";
  } else if(!isset($region)){
  $sel .= "OR region = '$region') ";
}
  $sel .= "AND (alder BETWEEN '$alder1' AND '$alder2')) ";
  $sel .= "ORDER BY id ";
  $query = mysql_query($sel) or die(mysql_error());

 

 

but I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id' at line 1.

 

 

Do you know what to do?

[Pikachu2000]

Echo the query string and make sure it contains the values you'd expect it to contain.

[JKG]

and check that you are using isset() in conjunction with $_POST or $_GET (a form).

also, even if the field is empty, it would still be classed as set.

empty() would probably be better.

 

and as for the mysql error, it could be something to do with the brackets if your ifelse statement isnt working.

print $sel;

[Frederik]

When I use print $sel; I get:

 

 

SELECT * FROM brugergruppe WHERE ((postnummer = '0000' AND (alder BETWEEN '2' AND '77')) ORDER BY id You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id' at line 1

 

 

I have set the fields "town", "koen" and "region", but they are now shown??

 

 

and is it possible to make the search not case sensitive??

[Pikachu2000]

Get rid of the parentheses, they're misplaced and unnecessary in this case.

[Frederik]

I tried to remove the () ind the BETWEEN statement but still getting the error:

 

SELECT * FROM brugergruppe WHERE ((postnummer = '0000' AND alder BETWEEN '2' AND '69') ORDER BY id You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id' at line 1

 

 

My whole code is like this:

 

<?php
require("config.php");
$search = $_POST['search'];
if(empty($search)) {
  echo "<br><a href='index.php?pageid=4'>";
  echo "Fejl!Ingen indtastning i søgefeltet1.</a>\n";
}
else {

  $fejl = false;
  foreach ($_POST as $nam => $val){
    if (empty($nam)){
      $fejl = true;
      break;
    }
  }
  if ($fejl){
    echo "<font color='red'><b>Fejl!</b></font>";
    echo "<br>Søgefeltet skal udfyldes.<br><br>";
    echo "<a href='index.php?pageid=4'>";
    echo "<b>Tryk her for at blive sendt til søgeformularen</b></a>\n";
    exit;
  }
  

  $postnummer = $_POST['postnummer'];
  $town = $_POST['town'];
  $koen = $_POST['koen'];
  $alder1 = $_POST['alder1'];
  $alder2 = $_POST['alder2'];
  $region = $_POST['region'];

  mysql_connect($mysql_host, $mysql_user, $mysql_pw);
  mysql_select_db($mysql_db);

$sel = "SELECT * FROM brugergruppe ";
  $sel .= "WHERE ((postnummer = '$postnummer' ";
  if(!isset($town)){
  $sel .= "AND town = '$town' ";
  } else if(!isset($koen)){
  $sel .= "AND koen = '$koen' ";
  } else if(!isset($region)){
  $sel .= "OR region = '$region') ";
}
  $sel .= "AND alder BETWEEN '$alder1' AND '$alder2') ";
  $sel .= "ORDER BY id ";
  print $sel;
  $query = mysql_query($sel) or die(mysql_error());

  if (mysql_num_rows($query) == 0){
    echo ("Søgningen gav desværre ingen resultater.");
exit;
  }
else{
         
echo <<<_END
   <div id='searchresult'>
      <form action="index.php?pageid=6" method="post">
_END;
         
      while($row = mysql_fetch_assoc($query)) 
         {
            $navn = $row['fornavn'] . " " . $row['efternavn'];
            $mail = $row['email'];
            $theid = $row['id'];
            
echo <<<_END
      <input type="checkbox" name="theid[]" value="$theid"> $navn, $mail <br/>
_END;
         }
echo <<<_END
       <input type="submit" value="Vælg"/>
      </form>
   </div>
_END;
  }
}

?>

[Pikachu2000]

I didn't say to just get rid of some of them . . . .

[Frederik]

I have tried something else now.

 

I read about the function empty() and now have this code instead:

 

  $sel = "SELECT * FROM brugergruppe WHERE";
  if (!empty($postnummer) $sel .= "postnummer = '$postnummer' ";
  if (!empty($town) $sel .= "AND town = '$town' ";
  if (!empty($koen) $sel .= "AND koen = '$koen' ";
  if (!empty($region) $sel .= "OR region = '$region' ";
  $sel .= "AND alder BETWEEN '$alder1' AND '$alder2' ";
  $sel .= "ORDER BY id ";
  print $sel;
  $query = mysql_query($sel) or die(mysql_error());

 

I think that makes more sense, but I get the error:

Parse error: syntax error, unexpected T_VARIABLE in ........ on line 37

 

 

line 37 is:

if (!empty($postnummer) $sel .= "postnummer = '$postnummer' ";

[JKG]

if (!empty($postnummer)) $sel .= "postnummer = '$postnummer' ";

 

if you open a bracket, you have to close it...in any language.

[Frederik]

of course!

 

I can get some results now.. But I'm still having problems...

 

If I leave the "postnummer" field blank I get the error:

 

SELECT * FROM brugergruppe WHERE AND town = 'test' AND koen = 'Kvinde' AND region = 'nordjylland' AND alder BETWEEN '1' AND '49' ORDER BY id

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND town = 'test' AND koen = 'Kvinde' AND region = 'nordjylland' AND alder BETWE' at line 1

 

 

The code:

  $sel = "SELECT * FROM brugergruppe WHERE ";
  if (!empty($postnummer)) $sel .= "postnummer = '$postnummer' ";
  if (!empty($town)) $sel .= "AND town = '$town' ";
  if (!empty($koen)) $sel .= "AND koen = '$koen' ";
  if (!empty($region)) $sel .= "AND region = '$region' ";
  $sel .= "AND alder BETWEEN '$alder1' AND '$alder2' ";
  $sel .= "ORDER BY id";
  print $sel;
  $query = mysql_query($sel) or die(mysql_error());

[JKG]

<?php 

$sel = "SELECT * FROM brugergruppe WHERE ";
  if (!empty($postnummer)) $sel .= "postnummer = '$postnummer' ";
  
  if (!empty($town) and !empty($postnummer)): $sel .= "AND town = '$town' ";
  elseif (!empty($town) and empty($postnummer)): $sel .= "town = '$town' ";
  endif;
  
  if (!empty($koen)) $sel .= "AND koen = '$koen' ";
  if (!empty($region)) $sel .= "OR region = '$region' ";
  $sel .= "AND alder BETWEEN '$alder1' AND '$alder2' ";
  $sel .= "ORDER BY id ";
  print $sel;
  $query = mysql_query($sel) or die(mysql_error());

 

you cant have WHERE AND

 

you may need to replicate the added statement so this doesnt happen further down the line.

[Frederik]

I'm not quite sure how to do that.  Du you have any suggestions to solve it?

 

The thing I need to do is use:

if (!empty($town) and !empty($postnummer)): $sel .= "AND town = '$town' ";

  elseif (!empty($town) and empty($postnummer)): $sel .= "town = '$town' ";

  endif;

 

 

and so on at the other fields?

[JKG]

i gave you the suggestion...

if (!empty($town) and !empty($postnummer)): $sel .= "AND town = '$town' ";
  elseif (!empty($town) and empty($postnummer)): $sel .= "town = '$town' ";
  endif;

 

oh, you edited...

yes, think about which fields may/can be left empty and how your query will need to change, bearing in mind you cannot give the query: WHERE AND

[Frederik]

The fields that can be empty is:

 

"postnummer" and "town".

 

 

So the code would be something like this:

 

  $sel = "SELECT * FROM brugergruppe WHERE ";
  if (!empty($postnummer)) $sel .= "postnummer = '$postnummer' ";
  
  if (!empty($town) and !empty($postnummer)): $sel .= "AND town = '$town' ";
  elseif (!empty($town) and empty($postnummer)): $sel .= "town = '$town' ";
  endif;
  
  if (!empty($koen) and !empty($town) and !empty($postnummer)): $sel .= "AND koen = '$koen' ";
  elseif (!empty($koen) and !empty($town) and empty($$postnummer)): $sel .= "AND koen = '$koen' ";
  elseif (!empty($koen) and empty($town) and !empty($postnummer)): $sel .= "AND koen = '$koen' ";
  elseif (!empty($koen) and empty($town) and empty($postnummer )): $sel .= "koen = '$koen' ";
  endif;
  
  if (!empty($region)) $sel .= "AND region = '$region' ";

  $sel .= "AND alder BETWEEN '$alder1' AND '$alder2' ";
  $sel .= "ORDER BY id";

 

 

Or is there a more appropriate way to do it?

[Pikachu2000]

That really isn't making much sense. Why does it matter if $postnummer is empty or not when it doesn't even get used in the query? You should validate that all required fields are not empty, and to the extent possible that they contain valid data, if a required field is empty, kick the user back to the pre-filled form with an error message. Build the query string with all of the required fields first, then add any optional fields.

[jcbones]

Should be what you need.

 

<?php

  $sel = "SELECT * FROM brugergruppe WHERE ";
  if(!empty($postnummer)) $where[] = "postnummer = '$postnummer' ";  
  if(!empty($town)) $where[] = "town = '$town'"; 
  if(!empty($koen)) $where[] = "koen = '$koen'"; 
  if (!empty($region)) $where[] = "region = '$region'"; 
  if(!empty($alder1) && !empty($alder2)) $where[] = "alder BETWEEN '$alder1' AND '$alder2'";
  
  $sel .= implode(' AND ',$where);
  $sel .= " ORDER BY id";
  
  echo $sel;

?>

[Frederik]

Great! Thank you all for helping me How can I mark the post as [solved]??

[jcbones]

Over there ->