Mysql - 选择具有相同值的多个行

Question

I've got a table called 'data', and some columns:
user,unit,price,discount,description.

 -----   -----  ------  ---------   ----------- 
| user | unit |  price | discount | description |
 -----   -----  ------  ---------   -----------
| test | unit |  100   |    50    |     des     |
-----   -----  -------   --------   -----------
| test2| unit2 |  200  |    20    |     des     |
 -----   -----  -----    --------   -----------

<?php 
 if($_SERVER['REQUEST_METHOD']=='GET'){
 $id  = $_GET['id'];
 require_once('dbConnect.php');
 $sql = "SELECT * FROM data WHERE description='".$id."'";
 $r = mysqli_query($con,$sql);
 $res = mysqli_fetch_array($r);
 $result = array();
 array_push($result,array(
     "user"=>$res['user'],
     "unit"=>$res['unit'],
     "price"=>$res['price'],
     "discount"=>$res['discount']
 )
 );
 echo json_encode(array("result"=>$result));
 mysqli_close($con);
 }

From this code, I get:

{"result":[{"user":"test","unit":"unit","price":"100","discount":"50"}]}

so it's just the first row. I want to get both of them like this:

{"result":[{"user":"test","unit":"unit","price":"100","discount":"50"}]}
{"result2":[{"user":"test2","unit":"unit2","price":"200","discount":"20"}]}

so there will be 2 arrays.

Answer 1

You will need to write a loop containing calls of mysqli_fetch_assoc(). As you iterate, store the rows of data.

while($row = mysqli_fetch_assoc($r)){
    array_push($result, array(
         "user" => $row['user'],
         "unit" => $row['unit'],
         "price" => $row['price'],
         "discount" => $row['discount']
         )
     );
}

Or if you replace the * in your SELECT clause to nominate your desired rows, you can use the top-voted comment on the mysqli_fetch_assoc() manual page...

for ($result = []; $row = mysqli_fetch_assoc($r); $result[] = $row);

This compact one-liner will convert your resultset into a multidimensional array with the same structure as the previous code block without the iterated array_push() function calls.