Tracing an ajax error to a sql query

I am working within the confines of CMS software, that automatically generates a query for use in a google suggest like input field.

The provided fields works fine, with the query they use which uses an inner join over two tables.

I am trying to make a query which will grab information from just one table, and will returns results on either firstname, lastname or passport number.

At the moment, it works in some instances, and not in others, and I can not find out why. The cause is definitely to do with the query, because the included queries do not have this problem.

At the moment, if I input 'r', it displays results for guests robert wilson and ray barone, with passport number, first name and last name all matching the correct fields.

If I type in 'wi', it returns results for robert wilson, but if I input 'wil' it returns nothing.

Additionally, inputting just 'wi' causes the passport number to be displayed instead of the lastname.

Apart from the query not working exactly right, there are some strange errors. If I input '%', or 'b' then I get a javascript alert about a syntax error.

Using '%' or 'b' in the included query works fine, the error is definitely related to my query, I am just having trouble working out what is causing it.

The query is automatically generated from a set of smaller queries stored in a table, which I have previously posted today on SO. If anyone could point me in the direction of why my generated query has such sporadic results, I would be quite 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%开发者_如何学Go'
                                                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 do hope the formatting is OK...I used a formatting tool, but I am not really certain of the standard way to format SQL....

If it is any help, all of this is related to a module for phpBMS.

The above query was the result of inputting 's'

---

I do something similar in my site, and I've found using REGEXP instead of LIKE to be preferable because you don't have to worry about the % symbols for beginning and end, etc.

A simple how to:

SELECT * FROM table WHERE value REGEXP 'searchval'

One final gotcha on names--make sure to search for first name, last name, and first/last concatenated. I searched on just first and last independently and couldn't figure out why a full name would result in 0 records found.