My PHP search in my MySQL is returning the whole table? Why?

Question

information being passed from jquery post to my php function but this is returning all of the information in my table even when I dont submit any data

if ( $_REQUEST['startsearch'] ) {
   $shipper = $_POST['postshipper'];
   $dealer = $_POST['postdealer'];
   $customer = $_POST['postcustomer'];
   $serial = $_POST['postserial']; 

   $sql = "SELECT * FROM `orders` WHERE shipper LIKE '%".$shipper."%' and active = '1' OR dealer     LIKE '%".$dealer."%' and active = '1' OR upper(customer) LIKE upper('%".$customer."%') and active = '1' OR serial LIKE '%".$serial."%' and active = '1'";
    $search = mysql_query($sql);
    while ($row = mysql_fetch_array($search)) {
        echo $row['dealer'];
    }
}

I want to be able to search even when only 1 of the 4 are filled or if all 4 are filled.

If one of your `$_POST` fields is absent you get a clause with `LIKE '%%'` which will match anything. — , Nov 15 '14 at 06:29
echo values of of $shipper,$dealer etc... and check whether u are getting correct results before using them in sql!! — Sudhir kumar, Nov 15 '14 at 06:31
did that Mikes right with the empty posts. might just make the value change it the post is emppty so that way its not looking up the '%%' rather a ' ' — Joey Feilmeier, Nov 15 '14 at 06:33
OK What do you think about this answer? http://stackoverflow.com/a/26943328/3176270 — Mohammad Kermani, Nov 15 '14 at 06:46

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

You could dynamically add your query. If that value is unset/empty don't include it. Else, include.

Obligatory Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

_{Ref: https://stackoverflow.com/a/12860140/3859027}

This is by way of PDO with prepared statements:

Of course this is untested, but this should give you the basic idea of the inclusion of those that are set. Those fields that are not set will not be included in the query.

$db = new PDO('mysql:host=localhost;dbname=DBNAME', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (isset($_REQUEST['startsearch'])) { // if submitted

    $params = array(); // hold the container which the values will be put in
    $base_sql = "SELECT * FROM `orders` WHERE"; // the starting query
    $sql = array(); // this will contain those queries that will be glued after its done

    // if shipper is set
    if(isset($_POST['postshipper'])) {
        // push inside the container
        $sql[] = " shipper LIKE :postshipper AND active = '1' ";
        $params[':postshipper'] = '%' . $_POST['postshipper'] . '%'; // push also the value
    }

    if(isset($_POST['postdealer'])) {
        $sql[] = " dealer LIKE :postdealer AND active = '1' ";
        $params[':postdealer'] = '%' . $_POST['postdealer'] . '%';
    }

    if(isset($_POST['postcustomer'])) {
        $sql[] = " upper(customer) LIKE upper(:customer) AND active = '1' ";
        $params[':postdealer'] = '%' . $_POST['customer'] . '%';
    }

    if(isset($_POST['postserial'])) {
        $sql[] = " serial LIKE :postserial AND active = '1' ";
        $params[':postserial'] = '%' . $_POST['postserial'] . '%';
    }

    // after its done, glue/implode them with `OR`
    $sql = implode(' OR ', $sql);

    $select = $db->prepare($sql);
    $select->execute($params);

    $results = $select->fetchAll(PDO::FETCH_ASSOC);
    print_r($results);
}

score 0 · Answer 2 · answered Nov 15 '14 at 06:32

0

You can use isset() function for it, if(isset($var)), then this query will generate, otherwise echo a message, when you use LIKE '%$var%', & keep $var blank, it matches everything with it.

answered Nov 15 '14 at 06:32

Jobayer

1,221
1
14
22

score 0 · Answer 3 · edited Nov 15 '14 at 06:52

the issue here you are putting to much "or" so it passing all conditions
problem is in your query try to change it something like this according to your requirement .

SELECT * FROM `orders` WHERE 
shipper LIKE '%".$shipper."%' and (active = '1' OR dealer  
LIKE '%".$dealer."%' and (active = '1' OR upper(customer) LIKE upper('%".$customer."%') )
and (active = '1' OR serial LIKE '%".$serial."%') and active = '1'

Sajib Acharya · Answer 4 · 2014-11-15T07:08:56.283

If you want a more refined search according to the $_POST information submitted, you can try this :

if ( $_REQUEST['startsearch'] ) {
    $shipper = isset($_POST['postshipper']) ? $_POST['postshipper'] : '';
    $dealer = isset($_POST['postdealer']) ? $_POST['postdealer'] : '';
    $customer = isset($_POST['postcustomer']) ? $_POST['postcustomer'] : '';
    $serial = isset($_POST['postserial']) ? $_POST['postserial'] : ''; 

    $search_term = '';

    if(!empty($shipper)){
         $search_term = 'shipper = "' . $shipper . '"';
    }
    if(!empty($dealer)){
         if(!empty($search_term)){
               $search_term = $search_term . ' AND ';
         }
         $search_term = $search_term . 'dealer = "' . $dealer . '"';
    }
    if(!empty($customer)){
         if(!empty($search_term)){
               $search_term = $search_term . ' AND ';
         }
         $search_term = $search_term . 'customer = "' . $customer . '"';
    }
    if(!empty($serial)){
         if(!empty($search_term)){
               $search_term = $search_term . ' AND ';
         }
         $search_term = $search_term . 'serial = "' . $serial . '"';
    }

    if(!empty($search_term))
    {
          $sql = 'SELECT * FROM orders WHERE ' . $search_term;

          $search = mysql_query($sql);
          while ($row = mysql_fetch_array($search)) {
              echo $row['dealer']."<br />";
         }
     }
 }

My PHP search in my MySQL is returning the whole table? Why?

4 Answers4