query help AND OR

Hello,

When i use the following query:

$sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' and publi
NOT like 'no' order by jaar DESC LIMIT 0, 100";

I get what i want: all projects named '%$zoek%' where publi is not like
no.

However, when i want that '%$zoek%' is not only to be searched for in
project but also in locatie I use the next query:

$sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' OR
locatie like '%$zoek%' and publi NOT like 'no' order by jaar DESC LIMIT
0, 100";

This does not do the thing I want:
records where publi is no are shown where I want them to be hidden.

Where do things go wrong?

Rob

Re: query help AND OR

On 23 Apr, 15:24, Rob wrote:
> Hello,
>
> When i use the following query:
>
> $sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' and publi
> NOT like 'no' order by jaar DESC LIMIT 0, 100";
>
> I get what i want: all projects named '%$zoek%' where publi is not like
> no.
>
> However, when i want that '%$zoek%' is not only to be searched for in
> project but also in locatie I use the next query:
>
> $sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' OR
> locatie like '%$zoek%' and publi NOT like 'no' order by jaar DESC LIMIT
> 0, 100";
>
> This does not do the thing I want:
> records where publi is no are shown where I want them to be hidden.
>
> Where do things go wrong?
>
> Rob

Your problem is operator precedence.
See: http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.h tml
What you need to do is:

$sql = "SELECT * FROM table WHERE 1 AND (project like '%$zoek%' OR
locatie like '%$zoek%') and publi NOT like 'no' order by jaar DESC
LIMIT
0, 100";

Note the brackets.

Re: query help AND OR

On 23 Apr, 16:45, Captain Paralytic wrote:
> On 23 Apr, 15:24, Rob wrote:
>
>
>
>
>
> > Hello,
>
> > When i use the following query:
>
> > $sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' and publi
> > NOT like 'no' order by jaar DESC LIMIT 0, 100";
>
> > I get what i want: all projects named '%$zoek%' where publi is not like
> > no.
>
> > However, when i want that '%$zoek%' is not only to be searched for in
> > project but also in locatie I use the next query:
>
> > $sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' OR
> > locatie like '%$zoek%' and publi NOT like 'no' order by jaar DESC LIMIT
> > 0, 100";
>
> > This does not do the thing I want:
> > records where publi is no are shown where I want them to be hidden.
>
> > Where do things go wrong?
>
> > Rob
>
> Your problem is operator precedence.
> See:http://dev.mysql.com/doc/refman/5.0/en/operator-preceden ce.html
> What you need to do is:
>
> $sql = "SELECT * FROM table WHERE 1 AND (project like '%$zoek%' OR
> locatie like '%$zoek%') and publi NOT like 'no' order by jaar DESC
> LIMIT
> 0, 100";
>
> Note the brackets.- Hide quoted text -
>
> - Show quoted text -

By the way, I find queries are far easier to read when they're laid
out like:
$sql = "
SELECT
*
FROM `table`
WHERE
(`project` LIKE '%$zoek%' OR `locatie` LIKE '%$zoek%')
AND `publi` NOT LIKE 'no' ORDER BY `jaar` DESC
LIMIT
0, 100";

Re: query help AND OR

In article <1177343157.087985.259750@b75g2000hsg.googlegroups.com>,
paul_lautman@yahoo.com says...
> On 23 Apr, 15:24, Rob wrote:
> > Hello,
> > When i use the following query:
> > $sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' and publi
> > NOT like 'no' order by jaar DESC LIMIT 0, 100";
> > I get what i want: all projects named '%$zoek%' where publi is not like
> > no.
> > However, when i want that '%$zoek%' is not only to be searched for in
> > project but also in locatie I use the next query:
> > $sql = "SELECT * FROM table WHERE 1 AND project like '%$zoek%' OR
> > locatie like '%$zoek%' and publi NOT like 'no' order by jaar DESC LIMIT
> > 0, 100";
> > This does not do the thing I want:
> > records where publi is no are shown where I want them to be hidden.
> > Where do things go wrong?
> > Rob
>
> Your problem is operator precedence.
> See: http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.h tml
> What you need to do is:
>
> $sql = "SELECT * FROM table WHERE 1 AND (project like '%$zoek%' OR
> locatie like '%$zoek%') and publi NOT like 'no' order by jaar DESC
> LIMIT
> 0, 100";
>
> Note the brackets.
>
Thanx, Captain!