Using PHP to search a MySQL DB !
Moderator: General Moderators
Using PHP to search a MySQL DB !
Really struggling with this one. I am trying to create a search facility for my website. Users will be searching 1 table but numerous rows will be returned. I have 5 input boxes that represent the 5 fields in the db. I want the user to be able to input a value into one or more of the textboxes and as a result a check take place and appropriate rows returned. I was going to have a submit button for each input box but this really limits the search. Any ideas are appreciated. Thanks.
You could send the results of your input boxes and write the sql accordingly. You will need to set any var not set with a value to default to "%".
This obviously get more difficult but possible if you wish to have "OR" values in the code.
Code: Select all
$var1 = $_POSTї'inputbox1'];
$var2 = $_POSTї'inputbox2'];
$var3 = $_POSTї'inputbox3'];
$var4 = $_POSTї'inputbox4'];
$var5 = $_POSTї'inputbox5'];
sSql = "Select *
From datatable
Where col1 like '$var1'
And col2 like '$var2'
And col3 like '$var3'
And col4 like '$var4'
And col5 like '$var5'";<b>This obviously get more difficult but possible if you wish to have "OR" values in the code.</b>
Wouldn't you just replace the AND's with OR's? I'm writing my first search script right now so would be grateful to learn what the complications of using OR's are. My first thought on the above is that AND's are bad because they return only rows with EVERY search term rather than rows with ANY search term - although that might be what is intended.
Also, it might be better to do the query string as:
".... LIKE '%$varX%' ...." etc (ie the % "wild-string specifier" before and after the search term - see mysql manual, mysql.com). My own search script doesn't work well without them.
Wouldn't you just replace the AND's with OR's? I'm writing my first search script right now so would be grateful to learn what the complications of using OR's are. My first thought on the above is that AND's are bad because they return only rows with EVERY search term rather than rows with ANY search term - although that might be what is intended.
Also, it might be better to do the query string as:
".... LIKE '%$varX%' ...." etc (ie the % "wild-string specifier" before and after the search term - see mysql manual, mysql.com). My own search script doesn't work well without them.
Depending on the order of the "OR" values you would need to group them
(ie,
And (col2 like '$var2' OR col3 like '$var3' )
And col4 like '$var4'
) columns.
So I think the code required to group the cols in the correct query sting gets more complicated.
The '%' wildcard would only be used if the input box was left blank. You would allow the user to put the wildcard wherever necessary.
So if any input box that you did not fill out on the sending page would be defaulted to '%' to allow you to specify any or all column values and return the expected results.
Yes I did take from the question to limit rows returned to only those that matched all values.
(ie,
And (col2 like '$var2' OR col3 like '$var3' )
And col4 like '$var4'
) columns.
So I think the code required to group the cols in the correct query sting gets more complicated.
The '%' wildcard would only be used if the input box was left blank. You would allow the user to put the wildcard wherever necessary.
So if any input box that you did not fill out on the sending page would be defaulted to '%' to allow you to specify any or all column values and return the expected results.
Yes I did take from the question to limit rows returned to only those that matched all values.