jquery autocomplete sql query list based on another input value

Question

I have two autocomplete fields in my form. I populate the first field's list by using the following code...

$result = mysql_query("SELECT * FROM exercise_strength"); 
$arr_autoCompleteExerciseStr=array();
$s=0;
while($row = mysql_fetch_array($result)){
$autoCompleteExerciseStr = "\"".ucfirst($row['exercise'])."\", ";
$arr_autoCompleteExerciseStr[$s] = $autoCompleteExerciseStr;
$s++;
}

and...

$(function() {
    var availableTags = [
        <?php for($k=0; $k<sizeof($arr_autoCompleteExerciseStr); $k++) {echo $arr_autoCompleteExerciseStr[$k]; } ?>
    ];
    $( "#inputExercise" ).autocomplete({
        source: availableTags,
        minLength: 0
    });
    $( "#inputExercise" ).focus(function(){           
        $(this).autocomplete("search");
    });
});

The same code with a different mysql_query is used for the other field. What I want to do is change the list of the second field based on what's typed in the first. For instance, if the user types Chest in the first field, a list of relevant Exercises is shown in the second field, selected from my sql database.

What is the best way to do this?

I would prefer if I wouldn't have to leave the page, cause then the user have to refill the rest of the form..

Please help! :)

-- UPDATE --

Based on your advice about JSON my code now looks like this.

Script:

$(document).ready(function(){  
  $.post('json_exercise.php', { /**/ }, showExercise, "text");  
});

function showExercise(res){
    var list = JSON.parse(res);
    $("#inputExercise").autocomplete({
        source: list,
        minLength: 0
    });
    $( "#inputExercise" ).focus(function(){           
        $(this).autocomplete("search");
    });
}

PHP file:

$con = mysql_connect(***);
if (!$con) {die('Could not connect: ' . mysql_error());}
mysql_select_db("test", $con);

$result = mysql_query("SELECT * FROM exercise_strength");
$i = 0;
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
  {
      //add the row to the $chat array at specific index of $i
      $exercise[$i] = $row['exercise'];
      $i += 1;
  }
echo json_encode($exercise);

Now I just need to change the php filed based on whats selected in the first autocomplete field.

thezboe · Accepted Answer · 2012-04-02 18:45:03Z

3

If you want to autocomplete based on results from a SQL query without having to leave the page, you might want to look into AJAX and JSON.

Also, please use PDO instead of the mysql_* functions for interfacing with DBMS's with PHP.

answered Apr 2, 2012 at 18:45

thezboe

5521 gold badge5 silver badges13 bronze badges

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

1 Comment

Virik Over a year ago

Thank you, I've updated my question with some new code. Do you have any input about that?

Collectives™ on Stack Overflow

jquery autocomplete sql query list based on another input value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related