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%'
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'