MYSQL search query

[Xpo3]

Hello I am trying to create a search query that ignore blank fields, and will search for fields that have information in them. I created this... but it is wrong, I need help.

 

    $query = "SELECT *

    FROM  residential

    WHERE bathrooms LIKE  \"$bathrooms\" OR `bathrooms`IS NULL

    AND  bedrooms LIKE  \"$bedrooms\" OR `bedrooms`IS NULL

    AND  location LIKE  \"%$location%\" OR `location`IS NULL

    AND  size LIKE  \"%$size%\" OR `size`IS NULL

    AND  cost LIKE \"%$cost%\" OR `cost`IS NULL

    AND  description LIKE\" %$description%\" OR `description`IS NULL

    AND  sold LIKE  \"%$sold%\" OR `sold`IS NULL

    AND  pool LIKE  \"%$pool%\" OR `pool`IS NULL

    AND  cargarage LIKE  \"$cargarage\" OR `cargarage`IS NULL

    AND  stories LIKE  \"$stories\" OR `stories`IS NULL

    AND  lotsize LIKE  \"%$lotsize%\" OR `lotsize`IS NULL

    AND  year LIKE  \"$year\" OR `year`IS NULL

    ORDER BY  id"; // EDIT HERE and specify your table and field names for the SQL query

 

[AMcHarg]

You need to use brackets around your "OR" statements, like this:

 

AND (something='$something' OR something='$something')

AND ... etc

 

Also: be consistant with where you use quotes

[AMcHarg]

You also need to make sure the empty cells in your database are actually NULL and not just simply empty, otherwise they are neither full of text that you want, nor NULL.

[ebmigue]

...while I would recommend to NOT USE nulls at all.

 

 

Hope it helps.

[Xpo3]

Thanks very much for your reply, if I am understanding correctly it should look like this ?

$query = "SELECT *

FROM  residential

WHERE bathrooms LIKE  \"$bathrooms\" OR `bathrooms` LIKE '0'

AND  (bedrooms LIKE  \"$bedrooms\" OR `bedrooms` LIKE '0')

AND  (location LIKE  \"%$location%\" OR `location` LIKE '0')

AND  (size LIKE  \"%$size%\" OR `size` LIKE '0')

AND (cost LIKE \"%$cost%\" OR `cost` LIKE '0')

AND  (description LIKE\" %$description%\" OR `description` LIKE '0')

AND  (sold LIKE  \"%$sold%\" OR `sold` LIKE '0')

AND  (pool LIKE  \"%$pool%\" OR `pool` LIKE '0')

AND  (cargarage LIKE  \"$cargarage\" OR `cargarage`LIKE '0')

AND  (stories LIKE  \"$stories\" OR `stories` LIKE '0')

AND  (lotsize LIKE  \"%$lotsize%\" OR `lotsize` LIKE '0')

AND  (year LIKE  \"$year\" OR `year` LIKE '0')

ORDER BY  id"; // EDIT HERE and specify your table and field names for the SQL query

 

( still does not quite work)

 

I made it so if they select nothing then it will be 0

[ebmigue]

I meant that your columns should be non-nullable columns, by design.

 

If that is not possible with your setup (i.e., you might be maintaining an old application which relies on the "NULL mechanism"),

then your original query, w/ minor revisions, might do the trick.

 

For readability purposes, and for correctness I suggest that your query be like this:

 

$query = "SELECT *
FROM  residential
WHERE
        bathrooms LIKE  '%$bathrooms%'
OR   bedrooms LIKE  '%$bedrooms%'
OR   location LIKE  '%$location%'
OR   size LIKE  '%$size%'
....etc, etc
ORDER BY  id";

 

Replace "..etc, etc" with the remaining attributes/columns to be searched.

 

Hope it helps.

[AMcHarg]

He still needs the AND statements, otherwise the SQL only requires one of the criteria to be satisfied before it pulls something out, and I don't think that's what he wants.