problem with jquery autocomplete and mySql

Question

search.php

$text = $mysqli->$_POST['term'];
$query = "SELECT name FROM males WHERE name LIKE '%" . $text . "%' ORDER BY name ASC";
$result = $mysqli->query($query);
$json = '[';
$first = true;
while($row = $result->fetch_assoc())
{
    if (!$first) { $json .=  ','; } else { $first = false; }
    $json .= '{"value":"'.$row['name'].'"}';
}
$json .= ']';
echo $json;

index.php

1) HTML

<body>
Text: <input type="text" id="autocomplete" />
</body>

2) jQuery

    $( "#autocomplete" ).autocomplete({
        source: function(request, response) {
            $.ajax({ url: "http://localhost/testing/auto/search.php",
            data: { term: $("#autocomplete").val()},
            dataType: "json",
            type: "POST",
            success: function(data){
                response(data);
            }
        });
    },
    minLength: 2
    });

When I type 2 letters, it gives me all the names in my database even if these two letters do not match any of the names.

How does that happen and how do I fix it?

how does your json looks like? you should use json_encode function to produce json in php — Dalen
– Dalen, Commented Jun 10, 2011 at 6:57
What does $mysqli->$_POST['term'] do? I think you should have $text = $_POST['term'];. This should work — Rakesh Sankar
– Rakesh Sankar, Commented Jun 10, 2011 at 7:34

Rakesh Sankar · Accepted Answer · 2011-06-10 08:00:27Z

1

Looks like my comment worked as an answer, hence this answer.

What does $mysqli->$_POST['term'] do? I think you should have $text = $_POST['term'];. This should work.

answered Jun 10, 2011 at 8:00

Rakesh Sankar

9,4255 gold badges45 silver badges67 bronze badges

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

Comments

Markus Hedlund · Accepted Answer · 2011-06-10 07:43:40Z

1

Change the PHP to

$text = $_POST['term'];

$query = "SELECT name FROM males WHERE name LIKE '%" . $mysqli->real_escape_string($text) . "%' ORDER BY name ASC";
$result = $mysqli->query($query);

echo json_encode($result->fetch_all(MYSQLI_ASSOC));

You forgot to escape the input to prevent SQL injections. Additionally, use @mu's answer to fix your front-end code.

answered Jun 10, 2011 at 7:43

Markus Hedlund

24.4k22 gold badges82 silver badges113 bronze badges

3 Comments

mu is too short Over a year ago

Nice, I didn't even notice the $text mistake until Swell pointed it out to me.

Markus Hedlund Over a year ago

@mu And I didn't spot the JS errors at first. Guess we focus on different things ;-)

mu is too short Over a year ago

To be fair, the JS issues are more stylistic and about using the API as intended than "real" errors.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Your source callback isn't doing what you think it is. The current contents of the autocompleter are in request.term, not in $("#autocomplete").val(). You're sending an empty value for term back to your PHP and then your SQL looks like:

SELECT name FROM males WHERE name LIKE '%%' ORDER BY name ASC

which of courses matches everything in your males table. Your source callback should look like this:

source: function(request, response) { 
    $.ajax({
        url: "http://localhost/testing/auto/search.php",
        data: { term: request.term },
        dataType: "json", 
        type: "POST", 
        success: function(data) { 
            response(data);  
        }
    });
}

The jQuery-UI widget probably manipulates the DOM a fair bit so the #autocomplete input element may not have anything useful in it until the autocompleter has a final value for it.

For reference, here's what the fine manual has to say:

The third variation, the callback, provides the most flexibility, and can be used to connect any data source to Autocomplete. The callback gets two arguments:

A request object, with a single property called term, which refers to the value currently in the text input. For example, when the user entered "new yo" in a city field, the Autocomplete term will equal "new yo".

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 10, 2011 at 7:29

mu is too short

436k71 gold badges863 silver badges822 bronze badges

4 Comments

mu is too short Over a year ago

What does $text look like in your PHP?

Swell Over a year ago

its worked also with data: { term: $("#autocomplete").val()},, the problem was $mysqli->$_POST['term'].

mu is too short Over a year ago

@Swell: Really? I didn't even notice that. So Znarkus gets the checkmark then. Even so, you should use request.term as that's the specified way and it saves you from all the extra work that $().val() has to do behind the scenes, it is probably more future proof as well.

Swell Over a year ago

thank you so much i would like to accept your answer but your answer not the right one regarding this issue, also i take your advice and i replaced $().val() with request.term , thank you again.

Goran Jurić · Accepted Answer · 2011-06-10 07:35:32Z

0

First make sure that your search.php is working. Switch to $_GET['term'] and run the page directly until you get the data you wanted.

I would personally change the query from from LIKE '%text%' to LIKE 'text%'.

After that use Firebug to examine the parameters that are transferred to the search.php with an AJAX call. BTW, are you sure that your jquery code is correct?

answered Jun 10, 2011 at 7:35

Goran Jurić

1,83913 silver badges17 bronze badges

Comments

froditus · Accepted Answer · 2011-06-10 08:40:50Z

0

I think is your sql query. U were using

$query = "SELECT name FROM males WHERE name LIKE '%$text%' ORDER BY name ASC"

if your name list like

Aaron Alexander Maartha ...

If you type 'AA', the above query will result : Aaron, Maartha. Because the query will search matching given string between the whole strings. % text % means the query result will ignore the left and right result.

if you're looking for names field data you supposed to use query

$query = "SELECT name FROM males WHERE name LIKE '$text%' ORDER BY name ASC"

If you type 'AA, it will result : Aaron.

Note that jQuery only returns what you're asking to the database.

edited Jun 10, 2011 at 8:40

answered Jun 10, 2011 at 8:01

froditus

1492 silver badges14 bronze badges

Comments

Pascal Belloncle · Accepted Answer · 2013-02-28 03:03:42Z

0

Use $_REQUEST['term'] instead $_POST['term'].

edited Feb 28, 2013 at 3:03

Pascal Belloncle

11.4k3 gold badges59 silver badges56 bronze badges

answered Feb 28, 2013 at 2:46

Gerardo

1

Collectives™ on Stack Overflow

problem with jquery autocomplete and mySql

index.php

6 Answers 6

Comments

3 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

index.php

6 Answers 6

Comments

3 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related