suggestive auto search not working properly

Question

i'm using suggestive search in my site but somehow its not workign.i have a table named as customers :

id     customername      location
1       ram                delhi
2       monty             new delhi
3       sandeep            noida

now i want to auto-search in this table for name and location so here is my code for that:

<?php
include("config.php"); 
$term=$_GET["term"];

$query=mysql_query("SELECT * FROM customers where customername like '%".$term."%' or location like '%".$term."%'");
$json=array();

    while($customer=mysql_fetch_array($query)){
         $json[]=array(

                    'value'=> $customer["customername "],
                    'label'=>$customer["customername "]." - ".$customer["id"],
                    'value'=> $customer["location"],
                    'label'=>$customer["location"]." - ".$customer["location"],

                        );
    }

 echo json_encode($json);

?>

with the help of this query i'm not able to auto search for customername and location both at one time means if i want to search customer name then it should give customer name and if i put location in search field it should give location.currently its giving me the last value as mentioned in above code this time its only giving me location.

see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/14110189#14110189) — NullPoiиteя, Sep 16 '13 at 05:43
Have you checked the link ,this is suggestion to not use deprecated mysql_* function And your code is ___vulnerable to SQL Injection___ — NullPoiиteя, Sep 16 '13 at 05:49
yes i have checked itnd will follow it but for now i want the answer for my question posted above. — Montiyago, Sep 16 '13 at 05:51

score 1 · Answer 1 · answered Sep 16 '13 at 06:10

Your array is not constructed properly. You have duplicate key names, so they will be overwritted.

Let's say you have:

$json[] = array(
    'value' => 'x',
    'label' => 'x-y',
    'value' => 'y',
    'label' => 'y-x'
);


var_dump($json);

The output is:

array (size=1)
  0 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)

Even [] in $json, doesn't automatically make you to have 0=>array(x,y),1=>array(y,x), you need to specify the keys.

Because I don't know how many rows the MySQL is returning, my example will be with static while loop:

$max=5; //max iterations
$i = 0; //first key
$k = $max+1; //second key
while ($i<=$max) {
    $json[$i] = array(
        'value' => 'x',
        'label' => 'x-y'
    );
    $json[$k] = array(
        'value' => 'y',
        'label' => 'y-x'
    );
    $i++;
    $k++;
}

ksort($json); //this line is only to return the array sorted by keys asc, not necessary, for the testing purpose
var_dump($json);

The second key $k should never be equal to $i, that's why I use the maximum $i can reach, to starting point of $k. Output:

array (size=12)
  0 => 
    array (size=2)
      'value' => string 'x' (length=1)
      'label' => string 'x-y' (length=3)
  1 => 
    array (size=2)
      'value' => string 'x' (length=1)
      'label' => string 'x-y' (length=3)
  2 => 
    array (size=2)
      'value' => string 'x' (length=1)
      'label' => string 'x-y' (length=3)
  3 => 
    array (size=2)
      'value' => string 'x' (length=1)
      'label' => string 'x-y' (length=3)
  4 => 
    array (size=2)
      'value' => string 'x' (length=1)
      'label' => string 'x-y' (length=3)
  5 => 
    array (size=2)
      'value' => string 'x' (length=1)
      'label' => string 'x-y' (length=3)
  6 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)
  7 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)
  8 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)
  9 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)
  10 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)
  11 => 
    array (size=2)
      'value' => string 'y' (length=1)
      'label' => string 'y-x' (length=3)

thanks for ur precious help nd sorry bro i'm newby to json so unable to get required result. can u please tell me that how can i get the different values after puting customername and location. — Montiyago, Sep 16 '13 at 06:28
If this doesn't work, you might want to change the keys? Not only value and label, but i.e. "value, label, value1, label1", so they all will be different — Royal Bg, Sep 16 '13 at 06:34
i have changed the label to label1 and value to value1 but its always takes the second value means its taking the value1 label1 which is location and i want that if i put customersname it give that and if i put location it will give me that so what should i have to do now. — Montiyago, Sep 16 '13 at 06:50
I mean you have 4 keys, name 4 of them differently, first-> `value`, second -> `label`, third -> `value1`, fourth -> `label1`, thus if you want to show only what is set -> `if(empty($json['value'])) { echo $json['value1']; }` You get the idea -> if the key value is empty then print value1, otherwise print the requested one — Royal Bg, Sep 16 '13 at 06:52
if(empty($json['value'])) { echo $json['value1']; } this condition doesn't fulfill in my case coz all data coming from one table and any value can't be empty coz when i search for any term it search in whole row and in any row it will never find the empty field. — Montiyago, Sep 16 '13 at 07:07
So the condition should return what you want depending on what is send by the user, you can test with what is $_POST-ed or however the interaction is done :) — Royal Bg, Sep 16 '13 at 07:14
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/37445/discussion-between-monty-and-royal-bg) — Montiyago, Sep 16 '13 at 07:16

score 0 · Answer 2 · answered Sep 16 '13 at 07:53

0

Just try this

<?php
include("config.php"); 
$term=$_GET["term"];
$query=mysql_query("SELECT * FROM customers where customername like '%".$term."%' or location like '%".$term."%'");
$json=array();
    while($customer=mysql_fetch_array($query)){
         $json[]=array(                   
                  $customer["id"]=>$customer["customername "]." - ".$customer["location"]); 
        } 
        echo json_encode($json);    
?>

answered Sep 16 '13 at 07:53

user2775080

73
3

thanks for ur help but i dont want both filed together i want that when i search for name it will give me name in search box and when i put location in search field it gives me the location in search box – Montiyago Sep 16 '13 at 08:05

suggestive auto search not working properly

2 Answers2