Multiple drop down filters to search form data

Question

kumar.papneja 0 Newbie Poster

12 Years Ago

hi there. I created HTML and PHP file to filter web form data based on multiple drop down filters. Here goes the form. when i run the form and PHP, i don't see any results in the browser. There is no error either. I am working on a example posted by another forum member.Pls. help. Thanks in advance.

    <form action="showJobs_new.php" method="post">
    <select name="Locations">
    <option value="" selected="selected">All Locations</option>
    <option value="arizona">Arizona</option>
    <option value="alaska">Alaska</option>
    </select>
    <select name="Jobs">
    <option value="" selected="selected">All jobs</option>
    <option value="Carpenter">Carpenters</option>
    <option value="Plumbers">Plumbers</option>
    </select>
    <input type="submit" value="search jobs" />
    </form>

    showJobs_new.php:

    <html>
    <body>

    <?php

    $username="root";
    $password="password";
    $database="test";

    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");

    $whereClauses = array(); 
    if (! empty($_POST['Locations'])) $whereClauses[] ="Locations='".mysql_real_escape_string($_POST['Locations'])."'"; 
    if (! empty($_POST['Jobs'])) $whereClauses[] ="Jobs='".mysql_real_escape_string($_POST['Jobs'])."'"; 
    $where = ''; 
    if (count($whereClauses) > 0) { $where = ' WHERE '.implode(' AND ',$whereClauses); } 

    $sql = mysql_query("SELECT * FROM mytable ORDER BY id DESC $limit" .$where);  

    $result=mysql_query($sql);

    while ($row = mysql_fetch_assoc($result)) {
    echo $row['Locations'];
    echo $row['Jobs'];
    }

    ?>
    </body>
    </html>

php

4 Contributors
13 Replies
5K Views
12 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by simplypixie

leviathan185 14 Junior Poster

12 Years Ago

sounds like the query may be incorrectly constructed. I like to put put a bit of debugging after my SQL queries

<?php
...
    $result = mysql_query($sql)
        or die("Error: ".mysql_error()."<br />Query: ".$sql);
?>

That will output any SQL errors you have and also the query you used. I find that helps me.

radow 7 Junior Poster in Training

12 Years Ago

Firstly will check availability rows in the table for which you execute search.

simplypixie 123 Posting Pro in Training

12 Years Ago

echo your query and then copy and paste into into your phpmyadmin and see what happens there

simplypixie 123 Posting Pro in Training

12 Years Ago

In your showJobs_new.php page, change this

$sql = mysql_query("SELECT * FROM mytable ORDER BY id DESC $limit" .$where);

To this

$sql = "SELECT * FROM mytable ORDER BY id DESC $limit" .$where;

In fact I can see where your query is wrong now as well - your $where clause must come before your Order By etc, so actually change the code to this

$sql = "SELECT * FROM mytable '".$where."' ORDER BY id DESC '".$limit."'";

As you can see I have temporarily removed the mysql_query from around your query so that you can echo it, therefore underneath this line add echo $sql; and comment out all code beneath so that when you run the page you will get your query displaying in the page. You can then copy the query that has been echo'd out and run it in phpmyadmin (click the SQL tab in phpmyadmin).

kumar.papneja commented: thx, it worked in PHPmyadmin but not in sql code +0

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

leviathan185 14 Junior Poster · Answer 1 · 2012-10-22T06:23:44+00:00

leviathan185 14 Junior Poster

12 Years Ago

the above will point that out

simplypixie 123 Posting Pro in Training · Answer 2 · 2012-10-22T07:01:38+00:00

If you print_r($_POST); you should see what is being passed to the next page and it will hopefully help you understand why your quesry isn't working (because you are assigning the whole of these arrays to your new array when you need to run them through a foreach):

$whereClauses = '';
$numLocations = count($_POST['Locations']);
$numJobs = count($_POST['Jobs']);
$i = 0;
    if (! empty($_POST['Locations'])) {
      foreach ($_POST['locations'] as $location) {
        $whereClauses .="Locations='".mysql_real_escape_string($location)."'";
        if ($i++ == $numLocations) {
         $whereClauses .= " AND";
        }
      }
    }
    if (! empty($_POST['Jobs'])) {
      foreach ($_POST['Jobs'] as $job) {
        $whereClauses .="Jobs='".mysql_real_escape_string($job)."'";
      }
      if ($i++ == $numJobs) {
         $whereClauses .= " AND";
      }
    }

This is not tested but hopefully it will give you an idea of what you need to do. You can remove the implode(' AND ',$whereClauses) and replace with just $whereClauses

kumar.papneja 0 Newbie Poster · Answer 3 · 2012-10-22T11:21:39+00:00

Dear both

Thanks a lot for such a quick response. This forum rocks. I tried all suggestions but still no luck with displaying query results. Pls. help.

kumar.papneja 0 Newbie Poster · Answer 4 · 2012-10-22T12:19:31+00:00

thx simplypixie. I am a newbie to PHP and not sure what command to copy and paste in PHPmyadmin. Sorry. I can type query in PHPmyadmin. but not sure which one to copy.

simplypixie 123 Posting Pro in Training · Answer 5 · 2012-10-22T14:45:46+00:00

Can you reply rather than leave your findings as a comment :)

So basically, your query runs in the database without any errors but you are still not getting anything displayed on your page? Let's try and put everything together:

$whereClauses = '';
$numLocations = count($_POST['Locations']);
$numJobs = count($_POST['Jobs']);
$i = 0;
    if (! empty($_POST['Locations'])) {
      foreach ($_POST['locations'] as $location) {
        $whereClauses .="Locations='".mysql_real_escape_string($location)."'";
        if ($i++ == $numLocations) {
         $whereClauses .= " AND";
        }
      }
    }
    if (! empty($_POST['Jobs'])) {
      foreach ($_POST['Jobs'] as $job) {
        $whereClauses .="Jobs='".mysql_real_escape_string($job)."'";
      }
      if ($i++ == $numJobs) {
         $whereClauses .= " AND";
      }
    }
$sql = "SELECT * FROM mytable '".$where."' ORDER BY id DESC '".$limit."'";

$result=mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
echo $row['Locations'];
echo $row['Jobs'];
}

I am presuming that your column names are correct in the echo $row['....'] so try the new code and let me know what happens.

kumar.papneja 0 Newbie Poster · Answer 6 · 2012-10-22T14:57:41+00:00

Thanks. i copied and tried again, but same results....no results. here is full code.

<form action="showJobs_new.php" method="post">
<select name="Locations">
<option value="" selected="selected">All Locations</option>
<option value="Arizona">Arizona</option>
<option value="Alaska">Alaska</option>
</select>
<select name="Jobs">
<option value="" selected="selected">All jobs</option>
<option value="Carpenters">Carpenters</option>
<option value="Plumbers">Plumbers</option>
</select>
<input type="submit" value="search jobs" />
</form>

showJobs_new.php

<html>
    <body>

    <?php

$mysqli = new mysqli("localhost", "root", "password", "test");

$whereClauses = '';
$numLocations = count($_POST['Locations']);
$numJobs = count($_POST['Jobs']);
$i = 0;
    if (! empty($_POST['Locations'])) {
      foreach ($_POST['locations'] as $location) {
        $whereClauses .="Locations='".mysql_real_escape_string($location)."'";
        if ($i++ == $numLocations) {
         $whereClauses .= " AND";
        }
      }
    }
    if (! empty($_POST['Jobs'])) {
      foreach ($_POST['Jobs'] as $job) {
        $whereClauses .="Jobs='".mysql_real_escape_string($job)."'";
      }
      if ($i++ == $numJobs) {
         $whereClauses .= " AND";
      }
    }
$sql = "SELECT * FROM mytable '".$where."' ORDER BY id DESC '".$limit."'";
$result=mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo $row['Locations'];
echo $row['Jobs'];
}

?>
</body>
</html>

kumar.papneja 0 Newbie Poster · Answer 7 · 2012-10-22T15:02:12+00:00

can you try on your browser please? Possibly issue with my MAC set up. here is insert code. Thx a ton.

$sql = "INSERT INTO `test`.`mytable` (`ID`, `Locations`, `Jobs`) VALUES (\'7\', \'Arizona\', \'Plumbers\'), (\'6\', \'Arizona\', \'Carpenters\');";

radow 7 Junior Poster in Training · Answer 8 · 2012-10-22T17:51:18+00:00

In Your query string is enclosed in double-quotes so for query values don't add the backslash

 $sql = "INSERT INTO `test`.`mytable` (`ID`, `Locations`, `Jobs`) VALUES ('7', 'Arizona', 'Plumbers'), ('6', 'Arizona', 'Carpenters');";

simplypixie 123 Posting Pro in Training · Answer 9 · 2012-10-22T18:36:51+00:00

Sorry if I did this wrong but your query needs to include your $whereClauses variable, so it should be:

$sql = "SELECT * FROM mytable '".$whereClauses."' ORDER BY id DESC '".$limit."'";

Again, echo out your sql to see if the query is correct.