Searching four fields ?

[higara]

Please can you help on this matter. I have four boxes on the submit page passing values to the results page that is opening a data base for results. On the search it works fine if i fill all four baxes out, but returns nothing if i leave two empty. The form only asks for two to be filled, the others are optional, but as said if they are left blank no reult in the WHERE is return as though it is looking for any blank feilds?

$SQL="SELECT * FROM tblusers WHERE 1 AND world
LIKE '$HTTP_POST_VARS[world]' AND sector
LIKE '$HTTP_POST_VARS[sector]' AND country
LIKE '$HTTP_POST_VARS[country]' AND service
LIKE '$HTTP_POST_VARS[service]' OR world
LIKE '$HTTP_POST_VARS[world]' AND sector
LIKE '$HTTP_POST_VARS[sector]'
ORDER BY id ASC";

I am not great at PHP but learing fast. Can you offer simple to follow solution to this matter?

Thanks Jason

[zone16]

mmmm,
you need to add the wildcard character (%) so that it returns evrything if your forms fields are empty...

$SQL="SELECT * FROM tblusers WHERE 1 AND world
LIKE '$HTTP_POST_VARS[world]%' AND sector
LIKE '$HTTP_POST_VARS[sector]'% AND country
LIKE '$HTTP_POST_VARS[country]%' AND service
LIKE '$HTTP_POST_VARS[service]%' OR world
LIKE '$HTTP_POST_VARS[world]%' AND sector
LIKE '$HTTP_POST_VARS[sector]%'
ORDER BY id ASC";

la'ers,

zone16

[higara]

Thanks for the reply but lets say i only fill field1 and field2 in even though i don't fill out 3 and 4 i only want the answers that match 1 and 2?

Have i explained this OK?

[zone16]

that will return evrything that matches your 2 fields.

it might be an idea to clearly state your logic, just to be on the safe side-it can be a pain to debug:

$SQL="SELECT *
FROM tblusers
WHERE 1
(AND world LIKE '$HTTP_POST_VARS[world]%'
AND sector LIKE '$HTTP_POST_VARS[sector]%'
AND country LIKE '$HTTP_POST_VARS[country]%'
AND service LIKE '$HTTP_POST_VARS[service]%')
OR
(world LIKE '$HTTP_POST_VARS[world]%'
AND sector LIKE '$HTTP_POST_VARS[sector]%' )
ORDER BY id ASC";

hope it helps,

zone16