Filter Recordset by More Than One Field

Report · Jun 17, 2010

I have an index page with a dynamic drop-down menu where you select the state. Then you hit submit and it goes to my results page where a dynamic table displays all record from my table with the selected state. This works perfectly.

Now, I want to add a second field to filter the recordset: year. The user shoul dbe able to select the state (ex: CA) and the year (ex:2006), hit submit and get their results. I just can't figure out how to get this to work.

On the index page, I added the second drop-down menu and have it displaying the distinct year values.

On the results page, I opened the recordset that the results are coming from. This was the code for just the first filter field:

SELECT *
FROM fiber_intake
WHERE `State` = colname

This worked just fine. The variable colname had this info associated with it:

Name: colname

Type: Text

Default value: -1

Run-time Value: $_GET['state']

I tried to write add the code for the second field, but I'm obviously doing in wrong (I'm not too familiar with MySQL):

SELECT *
FROM fiber_intake
WHERE `State` = colname AND WHERE `Year` = colname2

I had defined a second variable colname2, even though I'm not sure if I was supposed to. Here's the info associated with colname2:

Name: colname2

Type: Numeric

Default value: -1

Run-time Value: $_GET['year']

When I viewed this in the browser my index page appears with my drop-down menus. I selected a year and hit submit. This is the error code I got:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `Year` = 2006 LIMIT 0, 10' at line 1

Can anyone help me out?

Thanks!

Report · Jun 17, 2010

Only use the Where keyword once in the Where clause:

WHERE `State` = colname AND `Year` = colname2

Report · Jun 17, 2010

Ah, thanks for that bregent.

Now, I no longer get that error message, but it still doesn't work.

When I select a year, and hit submit it returns empty results.

I also tried to just select a state and now that returns empty reults too.

The info is being passed to the url (ex: http://10.100.136.29/csqConnect/results.php?state=&year=2006&submit=Submit) but it seems like it's just not getting through to the recordset.

Any suggestions?

Report · Jun 17, 2010

Look at your querystring and see that the value for state is blank; so the problem is in the form page. You might want to paste the code for that page here.

Report · Jun 17, 2010

That last time I had only selected a year, which is why the state value was empty. Apparently that what was causing the problem.

When I select a state and a year, it works great. (I just noticed this and I'm pretty excited that it works.)

But, I would like the user to have options on the form. If they just want to just select a year and not filter the state I'd like that to work too. I just don't know how to go about doing this.

I just tried to add a static option to the state menu ('--Select One--') with it's value set to null. I opened the page in the browser, selected --Select One-- and a year from teh other menu and hit submit. It still returns empty results.

Do you know how I could make this work?

The code from the form on the index page is below:

Again thanks fo rall your help!

<form action="http://10.100.136.29/csqConnect/results.php" method="get" name="form1" id="form1">
Select the state
<label for="state">:</label>
<p>
    <select name="state" id="state" tabindex="1">
      <option value="null">--Select One--</option>
      <?php
do {
?><option value="<?php echo $row_rsFiberIntakeState['State']?>"><?php echo $row_rsFiberIntakeState['State']?></option><?php
} while ($row_rsFiberIntakeState = mysql_fetch_assoc($rsFiberIntakeState));
$rows = mysql_num_rows($rsFiberIntakeState);
if($rows > 0) {
      mysql_data_seek($rsFiberIntakeState, 0);
      $row_rsFiberIntakeState = mysql_fetch_assoc($rsFiberIntakeState);
}
?>
    </select>
</p>
<p> </p>
<p>Select the year:</p>
<p>
    <label for="year">year</label>
    <select name="year" id="year">
      <?php
do {
?>
      <option value="<?php echo $row_rsFiberIntakeYear['Year']?>"><?php echo $row_rsFiberIntakeYear['Year']?></option>
      <?php
} while ($row_rsFiberIntakeYear = mysql_fetch_assoc($rsFiberIntakeYear));
$rows = mysql_num_rows($rsFiberIntakeYear);
if($rows > 0) {
      mysql_data_seek($rsFiberIntakeYear, 0);
      $row_rsFiberIntakeYear = mysql_fetch_assoc($rsFiberIntakeYear);
}
?>
    </select>
</p>
<p>   </p>
<label for="submit">Submit</label>
<input type="submit" name="submit" id="submit" value="Submit" tabindex="2" />
</form>

Report · Jun 17, 2010

>I just tried to add a static option to the state menu

That won't work because you are using 'AND' with your where clause. You could use OR depending on the results you want. If they fill out both fields, do you want it to return results where only both conditions are satisfied, or if either condition is satisfied?

Report · Jun 17, 2010

If they fill out both fields, I would want it to return results where only both conditions are satisfied.

But if they only fill out one field and leave the other field as the default value (some sort of null) then I owuld want it to return results that satisfy that one condition. Right now if this is done, it returns empty results.

Report · Jun 17, 2010

>If they fill out both fields, I would want it to return

>results where only both conditions are satisfied.

Then you will need to write a script that evaluates both fields and builds the SQL accordingly. Test the fields and if they contain text, use them in the where clause, otherwise leave them out.