multiple search parameters

[gigido]

hi i have 3 drop down fields, when a drop down is left blank (which i have given no value, so "") i seem to get an error on my search results. how do i make it so when there is no value in one of the fields that all values are retrieved for that particular drop down menu, so that its not searching for "" .

[jeremyphphaven]

put your code so we can see what you have... need to see if this is in code, or sql statement, where is the prob bob?

 

=O)

[AP81]

You should have some JavaScript on your form submit, i.e.

 

<form method="POST" action="do_something.php" onSubmit="return ValidateFields();">
...
</form>

 

Your ValidateFields() function will need to check all the fields contain valid values.  Then on your backend (do_something.php), you should also check that values submitted from the form are valid, i.e.

 

$val1 = $_POST["value1"];
$val2 = $_POST["value2"];
$val3 = $_POST["value3"];

if ( (!empty($val1)) && (!empty($val2)) && (!empty($val3)) ){
// it is valid
} else {
  die("Please fill in all form fields!);
}

[gigido]

hi, i dont see a need for java or validation. its a drop down menu, so its okay to have an empty value

<select name="state" id="state">
      <option value=""></option>
  <option value="ma">ma</option>
      <option value="ca">nh</option>
      <option value="nh">nh</option>
      <option value="vt">vt</option>
    </select>
number:
<select name="number" id="number">
    <option value="*"></option>
<option value="1">1</option>
  	<option value="2">2</option>
  	<option value="3">3</option>
  	<option value="4">4</option>
</select>
<input name="title" type="text" id="title" />
<input name="Search" type="submit" id="Search" value="search" />
</div>
  </label>
</form>

 

 

 

the php

$result = mysql_query ("SELECT * FROM listings WHERE state= ".$_POST['state']." and number =".$_POST['number]." and title = ".$_POST['title']."" );

 

 

so you have option to select a state, number from a drop down menu, as well as search via the text search .. so if you just want to search by state you select state from the drop down menu and the other values remain empty. so for my query to search the database i get an error if the values are empty. so how do a make it so when the drop down menu is empty that its not searching for an empty value in my database.

[Malevolence]

I would imagine that all you have to do is replace all those mySQL query AND statements to 'OR' because if someone leaves a field blank, it's returned as 'null'. So you're saying that that field in that row in the database MUST be null AND must have this title and this state etc.

 

You've also got loads of Syntax errors:

 

Replace:

$result = mysql_query ("SELECT * FROM listings WHERE state= ".$_POST['state']." and number =".$_POST['number]." and title = ".$_POST['title']."" );

 

With:

$result = mysql_query ("SELECT * FROM `listings` WHERE state= \".$_POST['state'].\" or number =\".$_POST['number].\" or title = \".$_POST['title'].\"" );

 

Because you are ending the query at every quotation! I was gonna just replace mysql_query("") with ('') but you have inverted commas in $_POST['state'] so I used a backslash (\) which avoids php from reading the quotation mark after it.

 

I've also added ` around your table name as this is better syntax for SQL.

 

Happy coding!

Dan.

[gigido]

there are no null values in my database, but are you saying when i us the "or" it will ignore any fields that are left blank? cause if it was "select from the table where title= 1 or number = 2"... I would only want the value returned that would have the same number and title.... not values that have 1 or the other.

[gigido]

.

[Malevolence]

No, what 'and' is doing is this:

 

Say the user left the state dropdown blank, the number field 2 and typed title as "bob":

 

OK troops! I want you to find the record WHERE the state = nothing, the number = "2" and title = "bob", KAPEESH!?

 

Now really, nothing is gonna turn up because NONE of the data in your table has a state value that is null.... See what I mean? They SHOULD have found what they were looking for but because the state HAS to be blank in order to meet the search criteria, nothing is returned.

 

Your Query should be this:

 

$result = mysql_query ("SELECT * FROM `listings` WHERE state= \".$_POST['state'].\" or number =\".$_POST['number].\" and title = \".$_POST['title'].\"" );

 

So now it's saying that the state can be this OR the number can be this, however BOTH have to match whatever row has that title. (or, and instead of and, and)

 

Also, if you want the title field to be like a real search e.g.:

 

The actual field is bob Marley

and the user has searched for 'bob'

 

You'd say:

 

SELECT * FROM `listings` WHERE state= ".$_POST['state']." or number =".$_POST['number']." and title LIKE ". $_POST['title'] ."

 

Regards,

Dan.

 

PS: Did it work?

[gigido]

makes perfect sence. thank you for clarifying.  umm... still getting an error of "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

 

heres what the code is:

$result = mysql_query ("SELECT * FROM `listings` WHERE number= ".$_POST['number']." or state =".$_POST['state']." and title LIKE ". $_POST['title'] ."");

while ($row = mysql_fetch_array($result)) {
$title = $row['title'] ;
echo "<center>title: <b>".$row['title']."</b></center>";   
  }

 

 

also changed it to just $row = mysql_fetch_array($result))  as opposed to the while statement, and also had an error.

[Jeremysr]

Don't you need quotes when using LIKE?

 

$result = mysql_query ("SELECT * FROM `listings` WHERE number= ".$_POST['number']." or state =".$_POST['state']." and title LIKE '". $_POST['title'] ."'");

[gigido]

un certain, but will give it a try, however just to be sure i took out searching for title and using LIKE until i can isolate the cause. But with taking out the title line, i still get that same error.

[Malevolence]

You forgot about the " replaced with \" didn't you? Probably my fault actually.... when I was explaining: 

 

$result = mysql_query("SELECT * FROM `listings` WHERE number= \"".$_POST['number']."\" or state =\"".$_POST['state']."\" and title LIKE \"". $_POST['title'] .'\"'");

[gigido]

hmmm negative.. this comes up with that same error.

[Jeremysr]

This should help you debug. Replace the $result = mysql_query() line with this:

 

echo "SELECT * FROM `listings` WHERE number= \"".$_POST['number']."\" or state =\"".$_POST['state']."\" and title LIKE \"". $_POST['title'] ."\"";
$result = mysql_query("SELECT * FROM `listings` WHERE number= \"".$_POST['number']."\" or state =\"".$_POST['state']."\" and title LIKE \"". $_POST['title'] ."\"") or die(mysql_error());

 

The first line will echo the query that you're trying to do, the second line will echo the mysql error if there's something wrong with the query.

[Malevolence]

[pre]$number = $_POST['number'];

$state = $_POST['state'];

$title = $_POST['title'];

 

$result = mysql_query("SELECT * FROM `listings` WHERE number= \"".$number."\" or state =\"".$state."\" and title LIKE \"". $title .'\"'");[/pre]

 

Any better?

[Jeremysr]

I think there's something wrong with your quotes at the very end of the query... it's OK to use single quotes in the query, you know. Then you don't have to make it look confusing with all the escaped quotes.

[gigido]

arrr... nope... really doesnt make much sence why it wouldnt. but same error .

[gigido]

im starting to think its the way im handling and displaying my results... when i remove the

while ($row = mysql_fetch_array($sql)) {
$title2 = $row['title'] ;
echo "<b>title:</b> $title2 <br>";
}

or only have my retrieval statement the errors go away.

[gigido]

Thanks all for the help.. problem solved...