StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL & AJAX Predictive Search Implementation Options
text
Body
copied!<p>I have addresses in an "address" table in a MySQL database. The table contains an address ID column and the usual assortment of address-related columns - name, line 1, line 2, suburb, state, postcode, etc. A number of the fields allow NULL.</p> <p>This is used in a client side web-based interface - the user can find and select an address from the table by typing any part of it into a text box. Matches are displayed and the user can select one.</p> <p><img src="https://i.stack.imgur.com/S2FRD.png" alt="The Airport is listed because of the 'str' in Australia"></p> <p>The terms in the text box are treated as a space-delimited series of search terms, and that each one of the terms must be present in any of the address fields for it to show up for selection.</p> <p><img src="https://i.stack.imgur.com/uZOUF.png" alt="Now there are two search terms to match..."></p> <p>I'm torn between a few implementation methods for this:</p> <p>The current method:</p> <ol> <li>On page load, an asynchronous HTTP request ("AJAX") is used to retrieve ALL addresses in a nicely formatted list (all fields combined into a single line, accounting for blank fields).</li> <li>jQuery is used to find and display matches in this list whenever the text box receives input.</li> </ol> <p>The benefit of this approach is that only one simple query needs to be sent, and there's no delay between typing into the search field and seeing the responses since the searching is done on the client side (although this may not scale well - see below). It also avoids the need for complex SQL searching (which I'm not averse to, I just wanted to implement something as a proof of concept and this method was quicker).</p> <p>The downside is of course that the page must retrieve every address when the page is loaded, and the database is likely to end up storing thousands of addresses.</p> <p>The alternative method would be to send a HTTP request whenever the user types into the text box, which would return the addresses matching a search of the table using SQL. Requires more requests and greater delays, but only needs to retrieve and transfer a subset of addresses each time. I can easily tweak the minimum term length and polling frequency as needed.</p> <p>I'm wondering about the best way to implement the SQL side of this...</p> <p>Would I be best off creating a view which concatenates all searchable address columns and using a query with a WHERE clause along the lines of "concatcolumn LIKE '%term1%' AND concatcolumn LIKE '%term2%' AND concatcolumn LIKE '%termN%'"?</p> <p>Any ideas or suggestions would be much appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload