Where is this query going wrong? Something to do with the WHERE part....

[richrock]

Hi,

 

Got a wierd prob with a query string, and here it is:

 

SELECT bi.id, bi.author_id, bi.firstname, bi.secondname, bi.thirdname, bi.fourthname, bi.fifthname, bi.surname, bi.dates, bi.biography, bi.created, au.name AS author, au.id AS auth_id
FROM jos_yearbook_biographies bi
LEFT JOIN jos_yearbook_authors au ON bi.author_id = au.id
WHERE bi.author_id = '1'
AND bi.surname LIKE 'cuy%'
OR bi.firstname LIKE 'cuy%'
OR bi.secondname LIKE 'cuy%'
OR bi.thirdname LIKE 'cuy%'
OR bi.fourthname LIKE 'cuy%'
OR bi.fifthname LIKE 'cuy%'
OR bi.biography LIKE '%cuy%'

 

The problem is with the author_id - it seems to ignore it completely, and shows a result regardless.  In the DB I've got two items, one with author_id 1, and the other with 2.

 

It shows the item with an author_id of two every time - I've checked the code, and it's sending 1 as seen in the statement.

 

I've also tried it using this statement:

 

SELECT bi.*, au.name AS author, au.id AS auth_id FROM #__yearbook_biographies bi 
				LEFT JOIN #__yearbook_authors au ON bi.author_id = au.id WHERE bi.surname LIKE 'cuy%'
			OR bi.firstname LIKE 'cuy%' 
			OR bi.secondname LIKE 'cuy%' 
			OR bi.thirdname LIKE 'cuy%' 
			OR bi.fourthname LIKE 'cuy%' 
			OR bi.fifthname LIKE 'cuy%' 
			OR bi.biography LIKE '%cuy%' AND bi.author_id = '1'

 

(I know one has jos_ and the other has #__ - I was testing the variant in phpmyadmin)

 

Any ideas why I always get items with author_id of '2'???  Puzzled.

[Mchl]

WHERE bi.author_id = '1' AND (bi.surname LIKE 'cuy%' OR ... OR... OR ...)

[richrock]

Excellent - did the trick!

 

Was part way there with putting the author id first.  Thanks 

[Mchl]

Remember, AND is like multiplicatin, OR like addition. If you want to multiply 5 by sum of 2 and 3 you need to write

5 * (2+3) (=25)

not

5*2+3 (=13)