searching MYSQL Table using php HTML front end 1

[richfield]

Hi,

I've just developed a database in mysql and I need to allow the user to be able to search on it using HTML form driven input, i.e use of text boxes, drop down list etc, I have searched the web and cannot find anything similar can anyone help?

Say the table contains the values

house
street
area
price

what is the sql search statement if the user selects one or all these values do i need to do seperate search statements for each possible pairing

i.e house and street, house and area or can one statement do it all?

Please help!!!

Thanks

Rich

 

[RISTMO]

Do you need an exact match or just anything "close"?

Rick

http://www.ristmo.com/

 

[DRJ478]

You will have to construct your query from whatever the user provides - but all is possible within one query. You should also ask the user if you want to search with a logical AND or an OR. With OR any of the matches will show, with AND only when all matches are present in the record.

# all records that have either value in the respective column
$SQL = 'SELECT * FROM myTable WHERE house="whatever" OR street="whatever"';

# all records that have both values in the respective column
$SQL = 'SELECT * FROM myTable WHERE house="whatever" AND street="whatever"';

SO, just inspect what is posted in the $_POST array and if a value is entered, add that portion to the query statement.

 

[richfield]

I need an exact match for the items i wish the user to search,

so the statement:

$SQL = 'SELECT * FROM myTable WHERE house="whatever" OR street="whatever"';


will search for both values if they have been provided or just one value if only one of them has been provided?

what if say street has been left blank and it is a text field does it take the a blank field as a search criteria? or will this just be ignored?

Thanks

 

[DRJ478]

Here's a little clearer explanation:
You need to check if the values are set and then construct the query. The query needs to be dynamic itself to do what you want.
Let's say you have a form with the fields 'house', 'street;, The form posts to a PHP search script. In the script you do something like this:

<?php
# examine POST
$queryItem = array();
if (isset($_POST['house'])){
   $queryItem[] = 'house="'.mysql_escape_string($_POST['house']).'"';
}
if (isset($_POST['street'])){
   $queryItem[] = 'street="'.mysql_escape_string($_POST['street']).'"';
}
# now you create your query statement (I assume AND)
$querySQL = "SELECT * FROM myTable WHERE ".implode(' AND ',$queryItem);
echo($querySQL);
?>

This is the basic idea.

 

[richfield]

Hi,

Thanks for your post, I sort of almost get it just a few points,

$queryItem[] = 'house="'.mysql_escape_string($_POST['house']).'"';

what does .mysql_escape_string do?

and also
$querySQL = "SELECT * FROM myTable WHERE ".implode('AND ',$queryItem);
echo($querySQL);

The Where ".implode, I take it this sets to the values that have been set in the array queryItem[], so if house was set alone it would be house? but if street was set it would also include this also???

Finally the echo($querySQL); what is this for?

Many Thanks

Rich

 

[RISTMO]

mysql_escape_string(); takes a variable and adds backslashes before other backslashes, quotation marks, and other characters that could break the query. For example if you wanted to say "....WHERE business_name='$business_name'..." and the variable $business_name was set to "Nick's Auto Shop", the ' in the variable would close the business_name='...' because mysql would think that was the closing ' instead of part of the value. So escaping it fixes that problem.

http://us2.php.net/manual/en/function.mysql-escape-string.php

implode() goes through the array of values lists each one with the seperator set in the first set of ' '. In this case, it implosed the array $queryItem and lists every field with " AND" in between each value.

http://us2.php.net/manual/en/function.implode.php

echo($querySQL); simply writes the query to the screen so you can see it. You won't use that much except for debugging the script. What you'll want to do is comment that line out (or delete it) and replace it with mysql_query($querySQL); and all your other code to output the results.

Rick

http://www.ristmo.com/

 

[richfield]

Hi,

Just ran the script and it works fine just a couple of things though,

$querySQL = "SELECT * FROM myTable WHERE ".implode('AND ',$queryItem);


If none of the fields are set i get an error, because of WHERE, as this is always executed, is it possible to dynamically change this so it only comes into action when one or more values is put in?

Final thing for certain values say rent I would like to search for any value less than or equal to the value entered as opposed to the exact amount entered how would this be done or am I going to far ahead of myself?

Thanks

Rich

 

[richfield]

Hi,

Just ran the script and it works fine just a couple of things though,

$querySQL = "SELECT * FROM myTable WHERE ".implode('AND ',$queryItem);


If none of the fields are set i get an error, because of WHERE, as this is always executed, is it possible to dynamically change this so it only comes into action when one or more values is put in?

Final thing for certain values say rent I would like to search for any value less than or equal to the value entered as opposed to the exact amount entered how would this be done or am I going to far ahead of myself?

Thanks

Rich

 

[richfield]

Hi,

Just ran the script and it works fine just a couple of things though,

$querySQL = "SELECT * FROM myTable WHERE ".implode('AND ',$queryItem);


If none of the fields are set i get an error, because of WHERE, as this is always executed, is it possible to dynamically change this so it only comes into action when one or more values is put in?

Final thing for certain values say rent I would like to search for any value less than or equal to the value entered as opposed to the exact amount entered how would this be done or am I going to far ahead of myself?

Thanks

Rich