I'm working in Content management systems software, that instantly creates a question to be used inside a google suggest like input area.

The provided fields works fine, using the query they will use which utilizes an inner join over two tables.

I'm attempting to make a question that will grab information from only one table, and can returns results on either firstname, lastname or passport number.

Right now, it really works sometimes, and never in other people, and I am unable to discover why. The reason is certainly related to the query, since the incorporated queries don't have this issue.

Right now, basically input 'r', it shows recent results for visitors robert wilson and ray barone, with passport number, name and surname all matching the right fields.

Basically key in 'wi', it returns recent results for robert wilson, but when I input 'wil' it returns nothing.

Furthermore, typing just 'wi' causes the passport number to become displayed rather than the lastname.

Aside from the query no longer working right, you will find some strange errors. Basically input '%', or 'b' i quickly obtain a javascript alert in regards to a syntax error.

Using '%' or 'b' within the incorporated query works fine, the mistake is certainly associated with my query, I'm just getting trouble exercising what's leading to it.

The totally instantly produced from some more compact queries saved inside a table, which i've formerly published today on SO. If anybody could point me in direction of why my produced query has such sporadic results, I'd be rather grateful.

SELECT COUNT(IF(guests.passport               != '', CONCAT(guests.passport , IF
       (guests.lastname                                            != ''
OR     guests.lastname              != '', CONCAT(' (',IF(guests.lastname != '',
       guests.lastname, '{blank}'),', ',IF(guests.firstname        != '',
       guests.firstname, '{blank}'),')'), '')), IF(guests.lastname != ''
OR     guests.lastname                                             != '',
       CONCAT(IF(guests.lastname                                   != '',
       guests.lastname, '{blank}'),', ',IF(guests.passport         != '',
       guests.passport, '{blank}')), ''))) AS thecount
FROM   guests
WHERE  (
              guests.id!=0
       )
AND
       (
              (
                     guests.firstname LIKE 's%'
              OR     guests.lastname  LIKE 's%'
              OR     guests.passport  LIKE 's%'
              )
       OR
              (
                     (
                            guests.firstname LIKE 's%'
                     OR     guests.firstname LIKE '% s%'
                     OR     guests.lastname  LIKE 's%'
                     OR     guests.lastname  LIKE '% s%'
                     OR     guests.passport  LIKE 's%'
                     OR     guests.passport  LIKE '% s%'
                     )
              )
       )
SELECT DISTINCT IF(guests.passport             != '', CONCAT(guests.passport ,IF
                (guests.lastname                                           != ''
OR              guests.lastname     != '', CONCAT(' (',IF(guests.lastname != '',
                guests.lastname, '{blank}'),', ',IF(guests.firstname      != '',
                guests.firstname, '{blank}'),')'), '')), IF(guests.lastname !=
                ''
OR              guests.lastname          != '', CONCAT(IF(guests.lastname != '',
                guests.lastname, '{blank}'),', ',IF(guests.passport!= '',
                guests.passport, '{blank}')), '')) AS display  ,
                `guests`.`uuid`                    AS value    ,
                1                                  AS secondary,
                guests.id                          AS classname
FROM            guests
WHERE           (
                                guests.id!=0
                )
AND
                (
                                (
                                                guests.firstname LIKE 's%'
                                OR              guests.lastname  LIKE 's%'
                                OR              guests.passport  LIKE 's%'
                                )
                OR
                                (
                                                (
                                                                guests.firstname
                                                                LIKE 's%'
                                                OR
                                                                guests.firstname
                                                                LIKE '% s%'
                                                OR              guests.lastname
                                                                LIKE 's%'
                                                OR              guests.lastname
                                                                LIKE '% s%'
                                                OR              guests.passport
                                                                LIKE 's%'
                                                OR              guests.passport
                                                                LIKE '% s%'
                                                )
                                )
                )
ORDER BY        IF(guests.passport             != '', CONCAT(guests.passport ,IF
                (guests.lastname                                           != ''
OR              guests.lastname     != '', CONCAT(' (',IF(guests.lastname != '',
                guests.lastname, '{blank}'),', ',IF(guests.firstname      != '',
                guests.firstname, '{blank}'),')'), '')), IF(guests.lastname !=
                ''
OR              guests.lastname          != '', CONCAT(IF(guests.lastname != '',
                guests.lastname, '{blank}'),', ',IF(guests.passport!= '',
                guests.passport, '{blank}')), ''))
LIMIT           0, 8

I actually do hope the formatting is alright...I made use of a formatting tool, but I'm not really clear on the conventional method to format SQL....

If it's any help, all this relates to a module for phpBMS.

The above mentioned query evolved as the result of typing 's'