0

Okay I am using prepared statement to get all the cities.

this is my php file

<?php
include_once '../includes/db_connect.php';
$search = $_GET['term'];
if($stmtgetstore = $mysqli->prepare("SELECT * FROM cities WHERE city LIKE '%$search%'"))
{
    //$stmtgetstore->bind_param("s",$search);
    $stmtgetstore->execute();
    $getstore = $stmtgetstore->get_result();
    $stmtgetstore->close();
}
else
{
    echo $mysqli->error;
}
$array = array();

$json = '[';
$first = true;
while($store = $getstore->fetch_assoc())
{
    if (!$first) { $json .=  ','; } else { $first = false; }
    $json .= '{"value":"'.$store['city'].'"}';
}
$json .= ']';

?>

And this is my script and html

<script type="text/javascript">
$(document).ready(function()
{
    $('#autoCity').autocomplete(
    {
        source: "scripts/search_store_by_city.php",
        minLength: 2
    })/*.data( "autocomplete" )._renderItem = function( ul, item ) 
    {
      return $( "<li></li>" )
      .data( "item.autocomplete", item )
      .append( item.city )
      .appendTo( ul );
    };*/
});
</script>




  <div class="container">

    <form action="" method="GET">
      <input type="text" id="autoCity">
    </form>

  </div>

But somehow when I enter letters in textbox I see no result coming in console and no error also but when I run query in database it gives me rows

This query

SELECT * FROM cities WHERE city LIKE '%Kara%'

Any idea what me doing wrong?

Alexxxx
  • 15
  • 4
  • Have you watched the AJAX request / response in the browser's developer tools? Have you included the jQuery library in the project? Are there any errors reported in the console? Are you running this on a web-server? Have you checked your server-side error logs? – Jay Blanchard Jan 04 '17 at 19:12
  • Yess I have checked ajax request and response. I have seen there is nothing returning back. I have used jQuery libraries and also there are no errors returning back neither is any data – Alexxxx Jan 04 '17 at 19:14
  • Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. – RiggsFolly Jan 04 '17 at 19:16
  • Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Jan 04 '17 at 19:17
  • 1
    Dont try and generate a JSON String manually!!! Use `json_encode()` and it will be done right and in one simple function call. [The manual](http://php.net/manual/en/function.json-encode.php) – RiggsFolly Jan 04 '17 at 19:19
  • I used `bind_param()` but it doesn't bind! Well I will check that later but I want to have my autocomplete working – Alexxxx Jan 04 '17 at 19:19
  • try `$stmtgetstore->bind_param('s', "%$search%");` and `city LIKE ?` – RiggsFolly Jan 04 '17 at 19:21
  • @RiggsFolly I believe you have to place `%` inside the SQL query and bind only the variable as it will not pass correctly e.g. `LIKE %?%` & `bind_param('s', "$search")` – Kitson88 Jan 04 '17 at 19:53
  • I did this `$searcher = '%'.$search.'%';` and passed it as a parameter in bind_param works perfectly – Alexxxx Jan 04 '17 at 20:25
  • @Alexxxx Yeah either way works fine, you just can't pass the `%` directly when binding. – Kitson88 Jan 04 '17 at 21:03

1 Answers1

0

Okay I forgot to echo my json at the end of the script

<?php
include_once '../includes/db_connect.php';
$search = $_GET['term'];
if($stmtgetstore = $mysqli->prepare("SELECT * FROM cities WHERE city LIKE '%$search%'"))
{
//$stmtgetstore->bind_param("s",$search);
$stmtgetstore->execute();
$getstore = $stmtgetstore->get_result();
$stmtgetstore->close();
}
else
{
echo $mysqli->error;
}
$array = array();

$json = '[';
$first = true;
while($store = $getstore->fetch_assoc())
{
if (!$first) { $json .=  ','; } else { $first = false; }
$json .= '{"value":"'.$store['city'].'"}';
}
$json .= ']';
echo $json;

?>

Alexxxx
  • 15
  • 4