Posted on: 09/06/04 07:54pm
By: machinari
I know this isn't a mysql dev shed help forum or anything like that, but I didn't feel like registering for yet another forum and waiting for yet another password.... I have enuf.
let's start with the sql. here it is:
SELECT q.Quotes, q.Quoted, q.Date, s.Name AS Contr, c.Name FROM gl_dailyquote_quotes q, gl_dailyquote_submitter s, gl_dailyquote_category c, gl_dailyquote_lookup l WHERE q.ID=l.QID AND s.UID=l.UID AND c.ID=l.CID AND q.Status='1' AND ((q.Quotes like '%term1%' OR q.Quotes like 'term1%' OR q.Quotes like '%term1') OR (q.Quoted like '%term1%' OR q.Quoted like 'term1%' OR q.Quoted like '%term1') OR (s.Name like '%term1%' OR s.Name like 'term1%' OR s.Name like '%term1')) OR ((q.Quotes like '%term2%' OR q.Quotes like 'term2%' OR q.Quotes like '%term2') OR (q.Quoted like '%term2%' OR q.Quoted like 'term2%' OR q.Quoted like '%term2') OR (s.Name like '%term2%' OR s.Name like 'term2%' OR s.Name like '%term2')) LIMIT 0, 1000
with that statement, i am searching all relevant fields checking for 'any' of the search terms.
if I only use one search term, no prob--i get a limited amount of results and they are correct. as soon as i add a 2nd term, i get the loop and it outputs the first result it finds over and over and over. i had to set a limit for debugging cuz my page wouldn't return.
sql statement causing real bad loop
Posted on: 09/06/04 08:11pm
By: Anonymous (tokyoahead)
(q.Quotes like '%term1%' OR q.Quotes like 'term1%' OR q.Quotes like '%term1')
This seems unnecessary to me. AFAIK the first %term1% covers also the two other cases.
maybe its not a loop but just too long statement. for debigging, I would have it write intermediary results into a table and go step by step through it.
sql statement causing real bad loop
Posted on: 09/06/04 10:19pm
By: machinari
[QUOTE BY= tokyoahead] AFAIK the first %term1% covers also the two other cases.[/QUOTE]You are correct AFAIK. I dunno what i was thinkin.
anyway, i fixed it. i was missing an outer parenthesis around one of the WHERE clauses.
nasty business this mysql. actually it's pretty easy once you get used to it. thanks dude.