Still consider even if?!?!

PHP
1

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???

2

V.Nice. :eye:

3

The record is 0 but still it shows no records?

4

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.

5

Do an echo of $data and see what the actual query looks like

6 

SELECT * 
FROM ".$prefix."_roundrequest 
WHERE con=$country 
AND sta IN ('', $state)
7

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…

Hope this makes some sense :expressionless:

By the way. The above code didnt work :expressionless:

8

But one problem though… If the search for state doesnt have any record it shows no records at all… It should still show the country record!

9

Sorry, my bad… Found the error… Works like a charm. Thanks alot Guido :slight_smile:

10

When trying to do so I get this error:
Supplied argument is not a valid MySQL result resource

Any other ideas?

11

Try this:



 $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?

12

Try this:



$data=
"
   SELECT * 
   FROM  
        ".$prefix."_roundrequest 
   WHERE 
       con=$country 
   OR WHERE 
       (con=$country AND sta=$state); 
"