Jquery UI Autocomplete from multiple data sources using UNION query

Question

The following query works properly to pull company names from one table into jquery UI autocomplete:

SELECT name FROM company WHERE name LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%"

The result is looped into an array and encoded to JSON then returned to jquery and displays autocomplete suggestions properly.

I am trying to modify it to pull from two or more tables:

SELECT name
FROM (
SELECT name
FROM company
UNION ALL SELECT CONCAT( fname,  ' ', lname ) 
FROM contact
) AS name
WHERE name LIKE  "'. mysql_real_escape_string($_REQUEST['term']) .'%"

The goal in this case would be to have the autocomplete list include company names and contact names. When I run the query independently of my application (just using PhpMyAdmin console) with a sample search term, it successfully displays the desired results. However in the context of my jquery ui autocomplete form, it does not return any autocomplete suggestions.

I would appreciate any suggestions. Thanks!

EDIT: Sample SQL results

Here is the result I get when I run each of these queries in PhpMyAdmin with test query "mi".

My original one-table source query:

Generation Time: Jul 20, 2011 at 01:40 AM
Generated by: phpMyAdmin 3.3.9 / MySQL 5.5.8
SQL query: SELECT name FROM company WHERE name LIKE "mi%" LIMIT 0, 30 ; 
Rows: 6

name
[rows removed]

Mr. Wanda's suggested modification:

Generation Time: Jul 20, 2011 at 01:50 AM
Generated by: phpMyAdmin 3.3.9 / MySQL 5.5.8
SQL query: SELECT temptable.name FROM ( SELECT name as name FROM company UNION ALL SELECT CONCAT( fname, ' ', lname ) as name FROM contact ) AS temptable WHERE temptable.name LIKE "mi%" ; 
Rows: 15

name
[rows removed]

Both are valid SQL that result in a table of one column with rows containing names, but only the first one works in jquery ui. =(

I'm sort of following, although I don't know how your UI autocomplete form works or what it expects. How many columns do you get in the result set when you union the tables? How many columns does your UI expect? — JustBeingHelpful
– JustBeingHelpful, Commented Jul 19, 2011 at 23:11
Ah sorry, was just trying to keep it simple. Both queries above are resulting in one column called "name". The jquery is expecting an array with a label and a value, both of which I am assigning the "name" array to. Not sure if this is the best practice, but I got it from an example I found online and it worked fine for the simpler query. Here is the relevant php: — Joe M.
– Joe M., Commented Jul 19, 2011 at 23:17
while( $row = mysql_fetch_array([THE SQL QUERY], MYSQL_ASSOC) ) { $data[] = array( 'label' => $row['name'] , 'value' => $row['name'] ); } — Joe M.
– Joe M., Commented Jul 19, 2011 at 23:17
@jmiller: Do you get any javascript errors? Have you opened firebug to see the differences in the data being retrieved via AJAX? — Andrew Whitaker
– Andrew Whitaker, Commented Jul 20, 2011 at 1:36
Andrew - no javascript errors with either query. The first (simpler) query works, the second one just does not produce any autocomplete suggestions, although it does not show any errors. The autocomplete source in the jquery code is another php file with code to make the SQL query and then return the JSON encoded array.. so I don't think in production it could show an error. I will try the firebug suggestion tomorrow. Thanks! — Joe M.
– Joe M., Commented Jul 20, 2011 at 5:58

JustBeingHelpful · Accepted Answer · 2011-07-20 01:06:03Z

1

See bold text below.

SELECT temptable.[name]
FROM (

SELECT name as [name]
FROM company

UNION ALL

SELECT TRIM(ISNULL(fname,'') + ' ' + ISNULL(lname,'')) as [name]
FROM contact

) AS temptable
WHERE
temptable.name LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%"

edited Jul 20, 2011 at 1:06

answered Jul 19, 2011 at 23:30

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Joe M. Over a year ago

Thanks for your help... tried this change but still no luck. I posted the results of running these SQL queries independently of jquery above in my original post.

JustBeingHelpful Over a year ago

why is there a LIMIT clause on the first one? Also, if you run the contact query independently, how many rows do you get in the result set?

Joe M. Over a year ago

Oops, PhpMyAdmin added the LIMIT automatically.. it does not go in my application, but the results are the same anyways.. the contact query independently is resulting in 9 rows, although for some reason it says "Showing rows 0 - 8 (9 total)"

JustBeingHelpful Over a year ago

and the contact query (running independently) shows perfectly in the jquery ui?

Joe M. Over a year ago

Ah.. no! It does not... interestingly, I tried just the "fname AS name" without the CONCAT and it did work (still independent from the first query)... I wonder if the CONCAT is causing jquery a problem...

|

Collectives™ on Stack Overflow

Jquery UI Autocomplete from multiple data sources using UNION query

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related