0

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>
4
Contributors
13
Replies
19
Views
4 Years
Discussion Span
Last Post by simplypixie
0

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.

0

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

0

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

0

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.

0

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.

1

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).

Votes + Comments
thx, it worked in PHPmyadmin but not in sql code
0

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.

0

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>
0

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\');";
0

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');";
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.