Mysql querie problems

[avandenbroeck]

The following code does not select the appropiate fields, it select
everything with in a zip code ( as an example)and so on. I have tried to
modify but I am a rookie and everything I tried has failed. Help please

$query = "Select xxx, xxx, xxx, xxx, xxx, xxx, xxx From
`list_data`
where (keyword like '$keyword%' and zip='$zip'
or key2 like '$key2%' and zip='
$zip'
or key3 like '$key3%' and zip='$zip'
or keyword like '$keyword%' and city='$city' and state='$state'
or key2 like '$key2%' and city='$city' and state='$state'
or key3 like '$key3%' and city='$city' and state='$state'
or name like '$name%' and zip='$zip'
or name like '$name%' and city='$city' and state='$state'
or keyword like '$keyword%' and city='$city' and country='$country'
or key2 like '$key2%' and city='$city' and country='$country'
or key3 like '$key3%' and city='$city' and country='$country'
or name like '$name%' and city='$ity' and country='$country'
or name like '$name%' and city='$city' and country='$country'
or name like '$name%' and city='$city' and country='$country')";

[Bill Karwin]

avandenbroeck wrote:

The following code does not select the appropiate fields, it select
everything with in a zip code ( as an example)and so on. I have tried to
modify but I am a rookie and everything I tried has failed. Help please .. . .
where (keyword like '$keyword%' and zip='$zip'
or key2 like '$key2%' and zip='$zip'
or key3 like '$key3%' and zip='$zip'
or name like '$name%' and zip='$zip'

I would guess that one of the following variables are a blank string:
$keyword, $key2, $key3, $name

For instance, if $key2 is blank, then you'll have a term in your query:
key2 LIKE '%' AND zip='01234'

A LIKE predicate comparing with '%' is always true.

So print the resulting string $query after you've interpolated all your
variables into it, and look for occurrances of '%'.

Regards,
Bill K.

[noone]

Bill Karwin wrote:

avandenbroeck wrote:

The following code does not select the appropiate fields, it select
everything with in a zip code ( as an example)and so on. I have tried to
modify but I am a rookie and everything I tried has failed. Help please .. . .

where (keyword like '$keyword%' and zip='$zip'
or key2 like '$key2%' and zip='$zip'
or key3 like '$key3%' and zip='$zip'
or name like '$name%' and zip='$zip'

I would guess that one of the following variables are a blank string:
$keyword, $key2, $key3, $name For instance, if $key2 is blank, then you'll have a term in your query:
key2 LIKE '%' AND zip='01234' A LIKE predicate comparing with '%' is always true. So print the resulting string $query after you've interpolated all your
variables into it, and look for occurrances of '%'. Regards,
Bill K.

I agree with Bill - also is it not logically the same to write the query
as;

where
(keyword like '$keyword%'
or key2 like '$key2%'
or key3 like '$key3%'
or name like '$name%')
and zip='$zip'

[Bill Karwin]

noone wrote:

I agree with Bill - also is it not logically the same to write the query
as;

where (keyword like '$keyword%' or key2 like '$key2%'
or key3 like '$key3%'
or name like '$name%')
and zip='$zip'

There is a lot of rewriting that could be done to simplify the boolean
expressions in this query. There are even three lines that are
identical. And a typo of '$ity' where it should be '$city'.

But I'm not sure if the query is being generated automatically, or if
it's structured the way it is to simplify alterations to it.

Anyway, if we want to simplify the query:

WHERE
(
zip = '$zip'
OR city = '$city' AND (state = '$state' OR country = '$country')
)
AND
(
keyword LIKE '$keyword%'
OR key2 LIKE '$key2%'
OR key3 LIKE '$key3%'
OR name LIKE '$name%'
)

That logically equivalent to the OP, and it has only one test for each
column, which may speed up the query significantly.

As a side note, avandenbroeck should make sure to understand the risks
of SQL injection:
http://en.wikipedia.org/wiki/Sql_injection

Regards,
Bill K.