php select and json encode issue

Question

I'm building a website to learn PHP and am making an autosuggest from Jquery Ui.

Here's my code ( I took this code from a post on SO a long time ago, and I'm not 100% what it does, so if anyone could explain, it would be helpful!) This code is from suggest.php, which I call to from my jquery code (which I think is working, so I didn't post it, but can If you need it!)

<?
include("config.php");

$queryString = strtolower($_GET["q"]);

$return = array();
$query = mysql_query("SELECT name FROM company WHERE name LIKE '$queryString%' UNION SELECT cat FROM cat WHERE cat LIKE '$queryString%' UNION SELECT subcat FROM subcat WHERE subcat LIKE '$queryString%' LIMIT 10");
while ($row = mysql_fetch_array($query)) {
     array_push($return,array('label'=>$row['name'],'value'=>$row['name']));
}
echo(json_encode($return));

?>

Right now this is making the autosuggest work, but only with the same results (example, if I type "Johns" it comes up with "Johns Hot Dogs" as a suggestion, but If I type "fjfjdjf669959" then it comes up with "Johns Hot Dogs" as well.

I'm doing a Mysql Union because I'm trying to populate my autosuggest with the name row from company table, the cat row from cat table, and the subcat row from subcat table.

Why is this not working?

Thanks for any and all help!!

My JQUERy code looks like this:

<script>
    $(function() {
        $( "#search" ).autocomplete({
            source: "suggest.php"
        });
    });
</script>

Not sure why "Johns Hot Dogs" comes up with fjfjf .. what happens when you run the query manually? You also need to alias cat and subcat as name if you want them to be added to return. — Explosion Pills
– Explosion Pills, Commented Aug 10, 2012 at 17:40
@Explosion, thanks for the help. Does running the query manually mean going to suggest.php?q= ? If it does then it echoes out [["Johns Hot Dogs","Johns Hot Dogs"], and it repeats for all of my rows in my tables — Muhambi
– Muhambi, Commented Aug 10, 2012 at 17:45
well I meant running it with the mysql program on the command line, but that way would work too I guess. It does this even if q=fjfjfj? — Explosion Pills
– Explosion Pills, Commented Aug 10, 2012 at 17:49
@ExplosionPills, no if q=fjfjfj, then its just echoes " [] " — Muhambi
– Muhambi, Commented Aug 10, 2012 at 17:55

phihag · Accepted Answer · 2012-08-12 21:36:46Z

4

+50

First of all your php code is vulnerable to SQL injection attacks. Furthermore, the mysql_* functions are deprecated. Instead, use PDO.

Your code fails because you're reading the wrong query variable. $_GET['q'] is empty since the jQuery UI autocomplete plugin uses the parameter term for the search query. With an empty $queryString, you execute the SQL query

SELECT name FROM company WHERE name LIKE '%'   -- UNION ...

which of course just returns everything. You want:

<?php
include("config.php");
$db = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (!isset($_GET['term'])) {
    header('HTTP/1.0 400 Bad Request');
    echo 'Missing term parameter in request';
    exit();
}
$queryString = strtolower($_GET["term"]);
$query = $db->prepare("SELECT name FROM company WHERE name LIKE :qs" .
  " UNION SELECT cat AS name FROM cat WHERE cat LIKE :qs" .
  " UNION SELECT subcat AS name FROM subcat WHERE subcat LIKE :qs " .
  " LIMIT 10");
$query->execute(array(':qs' => $queryString . '%'));
$query->setFetchMode(PDO::FETCH_NAMED);
$result = array_map(function($row) {
    return array('label'=>$row['name'],'value'=>$row['name']);
}, $query->fetchAll());

header('Content-Type: application/json');
echo(json_encode($result));

Here is a live, downloadable (incl. database) demo.

edited Aug 12, 2012 at 21:36

answered Aug 12, 2012 at 21:06

phihag

289k75 gold badges475 silver badges489 bronze badges

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

9 Comments

Madara's Ghost Over a year ago

Make sure to set prepared statement emulation to false too.

Madara's Ghost Over a year ago

Also, I'm pretty sure you can't use the same placeholder name twice in the same prepared statement.

phihag Over a year ago

@Truth What's the problem with prepared statement emulation? And I'm testing it with multiple tables, updating ...

Madara's Ghost Over a year ago

Setting it to false solves some edge cases.

phihag Over a year ago

@Truth That looks to be like a workaround for a bug in php or PDO. I think someone may have tested that strings work ;)

|

Collectives™ on Stack Overflow

php select and json encode issue

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related