Search with PHP/MySQL and dynamically update with jQuery

Question

I was wondering how to do the following best with PHP/MySQL and jQuery:

There is a basic search mask where you enter a city and a from-to-date. You process to the search-result page, where you then can narrow your search results with certain parameters (checkboxes, jQuery slider, text-input, ...). The search-results should then update on the fly without the whole page being reloaded...

I manage to use jQuery ajax and load to send information to another php file, perform e.g. a SELECT and return the results to the search detail page, but I don't know how to combine different changes that narrow the search results.

Furthermore, there are already results on the detail page, so I do not need to add more results but "delete" the results that do not fit anymore...

The thing is that each parameter to narrow the search is connected to another table in the database. Do I have to and how do I add joins to the original query...? Or am I thinking in the wrong direction?

Helmut · Accepted Answer · 2012-04-15 19:51:45Z

2

Yes, this is absolutely the right direction. Use

$(document).ready(function() {  

    $('#ID_OF_YOUR_ELEMENT_TO_LOAD_INTO').load("load.php?parameter1=<?php echo $parameter1; ?>&parameter2=<?php echo $parameter2; ?>");

});

to get the results when the user gets on the page for the first time, to get the results according to your city and your dates.

Check in the load.php which parameters are set and use the ones that are set to build your query. Then, when the form (or forms, depending) are updated, you have to use .load again, like this:

$('#ID_OF_YOUR_FORM_BEING_UPDATED').change(function() {

    $('#ID_OF_YOUR_ELEMENT_TO_LOAD_INTO').load("load.php?parameter1=<?php echo $parameter1; ?>&parameter2=<?php echo $parameter2; ?>&parameter3=<?php echo $parameter3; ?>");

});

answered Apr 15, 2012 at 19:51

Helmut

1,3771 gold badge15 silver badges25 bronze badges

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

1 Comment

Chris Over a year ago

yes, I see... thank you Helmut for the detailed answer with a practical example too! It is a great idea to load on document ready, too! Thanks a ton!

Basti · Accepted Answer · 2012-04-13 15:47:41Z

1

Get the initial tuples via PHP/MySQL, save them into some Javascript structure and create the html needed to display the data with javascript from this structure.

Any time you want to filter the data you rewrite the html and check the filter condition on the fly, e.g. don't write tuples from the structure that don't match your filter condition.

You can see who this is done at http://www.wowhead.com

This is of course just one way. ;-)

answered Apr 13, 2012 at 15:47

Basti

4,0511 gold badge21 silver badges22 bronze badges

2 Comments

Chris Over a year ago

so, I need to rewrite the html each time? this was what I was wondering... thank you Basti, the link is nice! :) Did you do this?

Basti Over a year ago

No this is not my site. ;-) Rewriting the html on each change may sound inefficient but it's no problem since the rewriting happens in the user's browser. you only need to server the hole data package once. you can even use compression.

Los Frijoles · Accepted Answer · 2012-04-13 15:47:55Z

You could always write some code to generate an SQL query based on passed arguments.

You ajax could query the page with a bunch of arguments in addition to your basic city and from-to date based on what the user has selected. If your page preserves the previous search options selected, it should be able to just let the user add on more options and keep processing them in the same way. Your php would then test to see if the arguments are set in the $_POST or $_GET variable ($_POST is more secure for ajax generally, but my example will use $_GET for simplicity) and build the query like that.

Example:

Javascript generates a query like searchAjaxHandler.php?city=Chicago&from=2012-03-01&to=2012-03-05&someColumnLowerRange=500&someColumnUpperRange=700

Your php script then processes as follows:

$query = "SELECT * FROM Data WHERE City=? AND Date > ? AND Date < ?";
$arguments = array($_GET['city'], $_GET['from'], $_GET['to']);
if (isset($_GET['someColumnLowerRange'])) {
    $query .= " AND someColumn > ?";
    $arguments[] = $_GET['someColumnLowerRange'];
}
if (isset($_GET['someColumnUpperRange'])) {
    $query .= " AND someColumn < ?";
    $arguments[] = $_GET['someColumnUpperRange'];
}
//execute the query
//using PDOs (google them...they are a good way to prevent sql injection and
//support multiple database types without modifying code too much), create a
//statement with the above query in put the statement in $statement

$statement->execute($arguments); //this uses the $arguments array to fill in the prepared statement's ?'s

//then do the stuff to get the retrieved rows out of the result returned

After all that, the javascript side would just to the same thing you were doing before by replacing all the previous results with the results that you got back.

Collectives™ on Stack Overflow

Search with PHP/MySQL and dynamically update with jQuery

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related