PHP looping results into array

Question

I'm trying to figure out how to save SQL query results into array:

This loop only produces 1 result in array:

<?php

$term = $_GET['term'];


$query = "SELECT * FROM Bob WHERE h_city LIKE '%".$term."%' ORDER BY 
h_city ASC";
$result = mysqli_query($conn, $query);

   while($row = mysqli_fetch_assoc($result)) {
        $result = $row['h_city'];
        array_push($result);
    }
    $json = json_encode(array($result));
    echo $json;
    ?>

result is: ["example1"] How to save query into array like ["example1","example2"]. Any thoughts what I might be doing wrong, or anyone can point me to the right direction?

EDIT as it was mentioned in the comments this way of doing queries is prone to SQL injections: more info here more info on converting results to arrays: more info here

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Sep 16 '20 at 12:22
`array_push($result);` makes little sense. You need to supply at least two parameters, starting with the second one, those are the values you actually want to push into the array. — CBroe, Sep 16 '20 at 12:24
`$result` is your query result then on first loop iteration you destroy it `$result = $row['h_city'];` — AbraCadaver, Sep 16 '20 at 12:25
`$result` is already being used as the resource for your query, and you're overwriting `$result`. The array push is just wrong here. — Jay Blanchard, Sep 16 '20 at 12:25
Please go and enable proper PHP error reporting, **NOW!** PHP would have already told you about this (“Warning: array_push() expects at least 2 parameters, 1 given in […]”). It should really not be necssary that _we_ alert you to this kind of mistake/inattention, when PHP by itself can do that already. — CBroe, Sep 16 '20 at 12:25

Jay Blanchard · Accepted Answer · 2020-09-16T12:33:47.837

I think a simple code change will do you well here, just put all of the row info in an array called items (or any other name that makes sense).

while($row = mysqli_fetch_assoc($result)) {
    $items[] = $row['h_city']; // add to an array called items
}
$json = json_encode($items);
echo $json;

I think likely your query is wrong too because you are limiting the list of cities here:

WHERE h_city LIKE '%".$term."%'

Which should only return a match for a single city.

WARNING You should listen to those who provided a critique of your code in the comments under your question. You made a lot of fundamental mistakes that you could likely have solved yourself if you had error reporting on and visible.

Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe!

PHP looping results into array

1 Answers1