CODEWITHSUNDEEP
phpif-statementmysqli
Chris Yates
Chris Yates

Reputation: 65

elseif search query not working

I have a search form that has 3 elements keywords, Industry ( Dropdown ) & location

I am trying to create search function that will query the DB if all 3 elements are selected or based on individual values OR a combination of any elements having $_POST data submitted

Its just not working

My code for the queries is below 

        if(isset($_POST['keywords']))
    {
    $keywords = $_POST['keywords'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') LIMIT 0,50");
    $resultsfor = $_POST['keywords'];
    }
    elseif(isset($_POST['keywords']) && isset($_POST['location']))
    {
    $keywords = $_POST['keywords'];
    $location = $_POST['location'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') AND (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $keywords .' jobs in '.$location;
    }
    elseif(isset($_POST['keywords']) && isset($_POST['location']) && !empty($_POST['industry']))
    {
    $keywords = $_POST['keywords'];
    $location = $_POST['location'];
    $catno = $_POST['industry'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') AND (`location` LIKE '%$location%') AND (`catno` = '$catno') LIMIT 0,50");
    $resultsfor = $keywords .' jobs in '.$location;
    }
    elseif(isset($_POST['industry']) && empty($_POST['location']))
    {
    $industry = $_POST['industry'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE `catno` = '$industry' LIMIT 0,50");
    $resultsfor = $_POST['industry']. ' jobs';
    }
    elseif(isset($_POST['industry'])&& isset($_POST['location']))
    {
    $industry = $_POST['industry'];
    $location = $_POST['location'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`catno` = '$industry') AND (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $_POST['industry']. ' jobs in '.$location;
    }
    elseif(isset($_POST['location']))
    {
    $location = $_POST['location'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $_POST['location']. ' jobs';
    }

    $view = mysqli_fetch_assoc($viewq);

Would really appreciate some help, Ive spent loads of time on this Thanks in advance

** It does pull data although not correct, If i search for warehouse ( leave industry dropdown blank ) but set location to Leicester it still queries ALL results not Leicester If I select an Industry from the dropdown menu and leave other fields blank, I get all jobs in all categories not just the category selected

Getting myself confused now

Upvotes: 0

Views: 55

Answers (5)

Tom Wright
Tom Wright

Reputation: 2861

I imagine your issues are due to the complicated way you have structured your if-else conditions. A much simpler way would be to do it as follows...

// Get the input variables
$keywords = (isset($_POST['keywords']) && strlen($_POST['keywords']) > 0) ? $_POST['keywords'] : null;
$location = (isset($_POST['location']) && strlen($_POST['location']) > 0) ? $_POST['location'] : null;
$catno = (isset($_POST['industry']) && strlen($_POST['industry']) > 0) ? $_POST['industry'] : null;

$whereUsed = false;
$whereString = "";
$resultsfor = "";

// Add to the WHERE clause if keywords exists.
if ($keywords !== null) {
    if (! $whereUsed) {
        $whereString .= 'WHERE ';
        $whereUsed = true;
    } else {
        $whereString .= ' AND ';
    }
    $whereString .= "(title LIKE '%{$keywords}%' OR description LIKE '%{$description}%')";
    if ($catno === null) {
        $resultsfor .= $keywords;
    }
}
// Add to the WHERE clause if catno exists.
if ($catno !== null) {
    if (! $whereUsed) {
        $whereString .= 'WHERE ';
        $whereUsed = true;
    } else {
        $whereString .= ' AND ';
    }
    $whereString .= "(catno = '{$catno}')";
    $resultsfor .= $catno;
}
// Add to the WHERE clause if location exists.
if ($location !== null) {
    if (! $whereUsed) {
        $whereString .= 'WHERE ';
        $whereUsed = true;
    } else {
        $whereString .= ' AND ';
    }
    $whereString .= "(location LIKE '%{$location}%')";
    if ($catno === null && $keywords === null) {
        $resultsfor = "{$location} jobs";
    } else {
        $resultsfor .= " jobs in {$location}";
    }
}

// Build the SQL query using the WHERE clauses we've built up
$sqlQuery = "SELECT * FROM listings {$whereString} LIMIT 0, 50";

// Execute the query and fetch the response
$viewq = mysqli_query($con, $sqlQuery);
$view = mysqli_fetch_assoc($viewq);

There is no need for the if-else-if format you have in your original code. You are simply adding WHERE clauses based on whether or not a variable is set... so you should do exactly that.

Please note that in your code, and in my example the SQL queries are vulnerable to SQL injection. I strongly suggest looking into prepared statements.

Upvotes: 2

Phoenix404
Phoenix404

Reputation: 1058

I tried to minimize your code and check this and tell if it works correct or not

<?php

    //Enter your code here, enjoy!
    $query          = "SELECT * FROM listings ";
    $eQuery         = "";
    $resultsfor     = "";

    if(isset($_POST['keywords']) && !empty($_POST['keywords']){
        $eQuery     .= " (title LIKE '%".addslashes($_POST['keywords'])."%' OR description LIKE '%".addslashes($_POST['keywords'])."%') ";
        $resultsfor .= $_POST['keywords'];
    }

    if(isset($_POST['location']) && !empty($_POST['location']){
        $eQuery     .=  " (`location` LIKE '%".addslashes($_POST['location'])."%') ";
        $resultsfor .= ' jobs in '.$location;
    }

    if(isset($_POST['industry']) && !empty($_POST['industry'])){
        $eQuery     .= " (`catno` = '".$_POST['industry']."')";
        $resultsfor = (isset($_POST['keywords']) && (isset($_POST['location'])))? $_POST['keywords'] .' jobs in '.$_POST['location'] ;
    }

    $query = mysql_query($con, $query.$eQuery." LIMIT 0,50");

    $view = mysqli_fetch_assoc($query);

Upvotes: 0

tibu
tibu

Reputation: 1

  if (isset($_POST['keywords']) && isset($_POST['location']) && 
     isset($_POST['industry'])) {
     $keywords = $_POST['keywords'];
     $location = $_POST['location'];
     $catno = $_POST['industry'];
     $viewq = mysqli_query($con, "SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') AND (`location` LIKE '%$location%') AND (`catno` = '$catno') LIMIT 0,50");
     $resultsfor = $keywords . ' jobs in ' . $location;
  } elseif (isset($_POST['keywords']) && isset($_POST['location']) && 
    empty($_POST['industry'])) {
           $keywords = $_POST['keywords'];
      $location = $_POST['location'];
      $viewq = mysqli_query($con, "SELECT * FROM `listings` WHERE (`title` 
     LIKE '%$keywords%' OR `description` LIKE '%$keywords%') AND (`location` 
      LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $keywords . ' jobs in ' . $location;
    } elseif (isset($_POST['keywords']) && empty($_POST['location']) && 
     empty($_POST['industry'])) {
      $keywords = $_POST['keywords'];
      $viewq = mysqli_query($con, "SELECT * FROM `listings` WHERE (`title` 
      LIKE '%$keywords%' OR `description` LIKE '%$keywords%') LIMIT 0,50");
      $resultsfor = $_POST['keywords'];
   } elseif (empty($_POST['keywords']) && isset($_POST['industry']) && 
   empty($_POST['location'])) {
     $industry = $_POST['industry'];
     $viewq = mysqli_query($con, "SELECT * FROM `listings` WHERE `catno` = 
    '$industry' LIMIT 0,50");
     $resultsfor = $_POST['industry'] . ' jobs';
  } elseif (empty($_POST['keywords']) && isset($_POST['industry']) && 
     isset($_POST['location'])) {
     $industry = $_POST['industry'];
       $location = $_POST['location'];
     $viewq = mysqli_query($con, "SELECT * FROM `listings` WHERE (`catno` = 
     '$industry') AND (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $_POST['industry'] . ' jobs in ' . $location;
} elseif (empty($_POST['keywords']) && empty($_POST['industry']) && 
isset($_POST['location'])) {
       $location = $_POST['location'];
           $viewq = mysqli_query($con, "SELECT * FROM `listings` WHERE 
     (`location` LIKE '%$location%') LIMIT 0,50");
      $resultsfor = $_POST['location'] . ' jobs';
   }

     $view = mysqli_fetch_assoc($viewq);

please try this

Upvotes: 0

Vishnu
Vishnu

Reputation: 137

There should be nine combinations to filter your query. Your conditions should be

if(isset($_POST['keywords']) && !isset($_POST['location']) && !isset($_POST['industry'])){}
elseif(isset($_POST['keywords']) && !isset($_POST['location']) && isset($_POST['industry'])){}
elseif(isset($_POST['keywords']) && isset($_POST['location']) && !isset($_POST['industry'])){}
elseif(isset($_POST['keywords']) && isset($_POST['location']) && isset($_POST['industry'])){}
elseif(!isset($_POST['keywords']) && !isset($_POST['location']) && isset($_POST['industry'])){}
elseif(!isset($_POST['keywords']) && isset($_POST['location']) && !isset($_POST['industry'])){}
elseif(!isset($_POST['keywords']) && isset($_POST['location']) && isset($_POST['industry'])){}

Upvotes: 1

Peter Grundmann
Peter Grundmann

Reputation: 129

I see some erros in your if statements. You should test from most specifc to low restricted. Your first elseif for example will never happen, because in this case the first if always occours.

Upvotes: 1

Related Questions