Retrieve data from MySQL database after jQuery autocomplete

Question

I have implemented on my site the jQuery autocomplete function which works well. However, I would like to use the result from the autocomplete to retrieve the selected person's telephone number from the database.

The database structure is this;

id | name | type | tel | mobile | email | level
===============================================
1 | John Smith | judge | 01234 567890 | 07812 345678 | [email protected] | BS Two Star

Here is my updated code so far

Autocomplete function

<script>
jQuery(document).ready(function($) {
    $('#inputChiefJudge').autocomplete({
        source:'lookups/shows-sj-searchforjudge.php',
        change: function (event, ui) {
            $.ajax({
                type: POST,
                url: 'lookups/shows-sj-findtel.php',
                data: 'id='+ id,
                success: function(data) {
                    details = $.parseJSON(data);
                    $('#inputChiefJudge').text("hello");
                    $('#chiefjudgetel').text(details);
                },
            });
        },
        minLength:2});
});
</script>

lookups/shows-sj-findtel.php

<?php
include("config.php");
mysql_connect ($DbHost, $DbUser, $DbPass);
mysql_select_db ("equilive_manager");
$id = $POST["id"];
$result = mysql_query("SELECT tel, mob FROM officials WHERE id='{$id}'");
$judgerow = mysql_fetch_array($result, MYSQL_ASSOC);

$contactdetails[] = array(
'tel' => $row['tel'],
'mob' => $row['mob'],
);

echo json_encode($data);
flush();
?>

lookups/shows-sj-searchforjudge.php

<?php
// if the 'term' variable is not sent with the request, exit
if ( !isset($_REQUEST['term']) ) exit;

// connect to the database server and select the appropriate database for use
include("../config.php");
mysql_connect ($DbHost, $DbUser, $DbPass) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("equilive_manager");

// query the database table for name that match 'term'
$term = mysql_real_escape_string($_REQUEST['term']);
$rs = mysql_query("SELECT id, name, level FROM officials WHERE name LIKE '%{$term}%' ORDER BY name LIMIT 0,10");

// loop through each name returned and format the response for jQuery
$data = array();
if ( $rs && mysql_num_rows($rs) )
{
    while( $row = mysql_fetch_array($rs, MYSQL_ASSOC) )
    {
        $data[] = array(
            'label' => $row['name'] .', '. $row['level'],
            'value' => $row['name'],
            'id' => $row['id'],
        );
    }
}

// jQuery wants JSON data
echo json_encode($data);
flush();

Thanks in advance, Craig

Hi Craig, please refine your question to make it more specific so that you can get some specific answers. What exactly are you stuck on? Do you have any code? — Ozzy
– Ozzy, Commented May 2, 2013 at 10:51

Jens Wegar · Accepted Answer · 2013-05-02 12:14:57Z

1

You have one issue at least in the code, which is that in getChiefJudgeContactDetails() you're mixing javascript with php. Mixing the two works fine if it's the first time you output a page and the code is on a PHP page. But if you're expecting the javascript to run PHP code every time a change event is triggered from the auto-complete, then that won't work.

Use the select event as others have stated, inside that, make a ajax request to a similar end point as your autocomplete but send it the value of your option (e.g. the ID value 2). Then use SQL in a PHP script to fetch the row for that id and return it as a json object. Parse the result and update UI in the jquery ajax call result handler.

update:

Change your autocomplete to look like this

<script>
jQuery(document).ready(function($) {
    $('#inputChiefJudge').autocomplete({
        source:'lookups/shows-sj-searchforjudge.php',
        select: function (event, ui) {
            $.ajax({
                type: POST,
                url: 'lookups/shows-sj-findtel.php',
                data: {id:id},
                success: function(data) {
                    details = $.parseJSON(data);
                    $('#inputChiefJudge').text("hello");
                    $('#chiefjudgetel').text(details);
                },
            });
        },
        minLength:2});
});
</script>

Instead of using the change option of the autocomplete, use select (as stated by other answers to your question). Also, instead of using a string ("id="+id) as your data, use a js object ({id:id}). jquery will handle serializing it correctly before sending to the server, the result being that it actually shows up as a post variable in your php script.

Also, as more of a side note, I would suggest looking into using the PDO driver (http://www.php.net/manual/en/pdo.prepare.php) to access your database instead of using the mysql_* commands. It's object oriented and also automatically provides safety features that are not available in the old commands, such as prevention of SQL injection attacks.

edited May 2, 2013 at 12:14

answered May 2, 2013 at 11:10

Jens Wegar

4,9924 gold badges32 silver badges34 bronze badges

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

2 Comments

Craig Blackie Over a year ago

I have updated the code - as seen above - but I am obviously doing something wrong as it is not working. Can you see what it is or am I way off? Thanks, Craig.

Craig Blackie Over a year ago

Thanks Jens, I away all day today so I will try later on when I get in. Thanks again and I'll let you know how I get on.

Dipesh Parmar · Accepted Answer · 2013-05-02 10:54:02Z

0

You can do it in select option of autoComplete.

All you need to do is send new ajax request to get selected person number.

select: function (event, ui)
{
   //make $.ajax request and send selected value.
   //you can send selected value using => ui.item.value
}

answered May 2, 2013 at 10:54

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

2 Comments

Craig Blackie Over a year ago

Thanks for this and please excuse my ignorance but, the phone number is not part of the array. Do I have to add it to the array?

Craig Blackie Over a year ago

I have posted the lookup code which creates an array and passes it back to the autocomplete function. Does the phone number have to be added to the array?

Software Guy · Accepted Answer · 2013-05-02 10:55:26Z

0

You should use the "select" event of the autocomplete:

http://api.jqueryui.com/autocomplete/#event-select

answered May 2, 2013 at 10:55

Software Guy

3,2804 gold badges23 silver badges21 bronze badges

Collectives™ on Stack Overflow

Retrieve data from MySQL database after jQuery autocomplete

3 Answers 3

update:

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

update:

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related