I’m trying to make a searchscript where I can search for a record depending on country and state.
I have 3 records in my table like this: id title con sta
1 test1 213
2 test2 213 455
3 test3 213 389
Now the issue is that when I only use the country parameter in the search it shows all records in result as it should, but when I then take the state parameter, lets say 455, it only shows the record with the sta 455. It should also show the record without the sta.
Hope this makes some sense?!?
Here is how I do now:
if(!$state){
$data="SELECT * FROM ".$prefix."_roundrequest WHERE con=$country";
} else {
$data="SELECT * FROM ".$prefix."_roundrequest WHERE con=$country AND sta=$state";
}
I have an idea that I have to change the “AND” in the second query but dont know to what???
It does. That is, if the value of the state column in that case is ‘’. If it’s NULL, try
SELECT *
FROM ".$prefix."_roundrequest
WHERE con=$country
AND sta IN (NULL, $state)
If it’s zero, try
SELECT *
FROM ".$prefix."_roundrequest
WHERE con=$country
AND sta IN (0, $state)
The point is, the IN statement selects all rows that have the values listed in the sta column. So putting the user inputted value, AND the value the column has in the ‘country’ row, it will always return the ‘country’ row.
What I am trying to acomplish is that user has made an request to the board in which he is able to target into a certain direction. Either to a country or to a certain state in a country.
Now, when another user is searching for a request he can use 2 parameters. Country and state.
Now. in the first record the user has only targeting the country, but that has not excluded the record to be showned if the searching user also uses the state parameter in his search. So no matter what state his search, the records with only the correct country and no state should also be showned…
$data="SELECT * FROM " .$prefix ."_roundrequest WHERE con=$country
OR WHERE con=$country AND sta=$state ";
>>> It should also show the record without the sta.
I am confused. Why bother searching for the “sta” if you only want the all the results from the country?