CODEWITHSUNDEEP
phpsearch
Dave Piersma
Dave Piersma

Reputation: 81

Searching multiple columns in DB

I have a searching script, now I want to let it search multiple columns. Like now I have "plaats" as a database column. But I also want track, straat, postcode, plaats to be found in the search option.

I have added the AND / OR rule but then it shows ALL rows in the database.

Can someone help me out with this one :) 

<?php
if (!empty($_REQUEST['term'])) {

    $term = mysql_real_escape_string($_REQUEST['term']);

    $sql = "SELECT * FROM tt WHERE plaats LIKE '%".$term."%'";
    $r_query = mysql_query($sql);

    while ($row = mysql_fetch_array($r_query)){
        echo "<tr class=\"border_bottom\">";
        echo "<td><a href=\"https://jouw.postnl.nl/#!/track-en-trace/".$row['track']."/".$row['land']."/".$row['postcode']."\">Bekijk Status</a>";
        echo "<td>".$row['track']."</td>";
        echo "<td>".$row['straat']."</td>";
        echo "<td>".$row['postcode']."</td>";
        echo "<td>".$row['plaats']."</td>";
        echo "<td>".$row['land']."</td>";
        echo "<td>".$row['datum']."</td>";
        echo "<td>".$row['klantnummer']."</td>";
        echo "<td><a href=\"edit.php?id=$$row[id]\">Wijzigen</a></td>";
    }
}
?>

Upvotes: 1

Views: 64

Answers (3)

Hassaan
Hassaan

Reputation: 7672

Change from

$term = mysql_real_escape_string($_REQUEST['term']);

$sql = "SELECT * FROM tt WHERE plaats LIKE '%".$term."%'";

To

$term = mysql_real_escape_string($_REQUEST['term']);

$searchTerms = "plaats LIKE '%".$term."%'
        OR track LIKE '%".$term."%'
        OR straat LIKE '%".$term."%'
        OR postcode LIKE '%".$term."%'
    ";

$sql = "SELECT * FROM tt WHERE $searchTerms";

Warning: MYSQL extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

Upvotes: 0

stackers
stackers

Reputation: 385

you can do it by adding this code.

  $qry = "select * from  tt where 
  plaats LIKE '%".$term."%' or  track, LIKE '%".$term."%' or straat LIKE   '%".$term."%' or postcode LIKE '%".$term."%' or plaats  LIKE '%".$term."%'";
echo $qry;

Upvotes: 0

Burki
Burki

Reputation: 1216

Your query should look like this:

$sql = "SELECT * FROM tt WHERE 
    plaats LIKE '%".$term."%' 
    OR straat LIKE '%".$term."%' 
    OR track LIKE '%".$term."%' 
    OR land LIKE '%".$term."%' 
    OR datum LIKE '%".$term."%'";

That means: you need to compare every column with the term you are looking for.

Please keep in mind that you should not use mysql_* functions. They are deprecated and will be removed in the future.

Also, you amy want to consider a somewhat more complex stragety: i strongly doubt that there really is a scenario where the same term might be found either in "street" or in "datum". But that is just a side note.

Upvotes: 1

Related Questions