Fetch multiple rows in mysql single query in php with different id

Question

I have different id's, i am getting the values of these id from users

$id=array();
$id[0]=$_GET["id0"];
$id[1]=$_GET["id1"];
$id[2]=$_GET["id2"];

now to fetch data from database i am using following query:

for($j=0;$j<count($id);$j++)
{
  $res=mysql_query("SELECT * FROM mutable WHERE id='$id[$j]'")
  while($row=mysql_fetch_array($res))
  {
     $row[]=array("email"=>$row[2],"name"=>$row[3],"address"=>$row[5]);
     echo JSON_encode($row);
  }
}

now i am getting proper result from this query using for loop but the result is not in proper JSON format, is there any way to do it more efficentyly and getting proper result in JSON array and JSON object format

Ditch those old `mysql_` functions. Use mysqli's prepared statements with placeholders for security reasons. `$result` doesn't exist. — mickmackusa, Feb 17 '18 at 07:55

mickmackusa · Answer 1 · 2018-02-17T08:28:59.353

Place json_encode() outside of your loops.

Let's modernize and refine things...

*Unfortunately prepared statements that use an IN clause suffer from convolution. pdo does not suffer in the same fashion.

Code: (untested)

if(isset($_GET['id0'],$_GET['id1'],$_GET['id2'])){
    $params=[$_GET['id0'],$_GET['id1'],$_GET['id2']];  // array of ids (validation/filtering can be done here)
    $count=count($params);  // number of ids
    $csph=implode(',',array_fill(0,$count,'?'));  // comma-separated placeholders
    $query="SELECT * FROM mutable WHERE id IN ($csph)";

    $stmt=$mysqli->prepare($query);  // for security reasons

    array_unshift($params,str_repeat('s',$count));  // prepend the type values string
    $ref=[];  // add references
    foreach($params as $i=>$v){
        $ref[$i]=&$params[$i];  // pass by reference as required/advised by the manual
    }
    call_user_func_array([$stmt,'bind_param'],$ref);    

    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM))
        $rows=["email"=>$row[2],"name"=>$row[3],"address"=>$row[5]];
    }
    $stmt->close();
    echo json_encode($rows);
}

You wrote the `json_encode()` after your `for` loop? Post your _improper_ json in your question and show us your expected result. — mickmackusa, Feb 17 '18 at 08:01
If you don't know how many `id` values a user may send, then your form needs to improvement. The name attribute on your `id` input field should look like this: `name='id[]'`, then your `$_GET['id']` will be an array of ids that are ready for processing. — mickmackusa, Feb 19 '18 at 07:12

hunteke · Answer 2 · 2018-02-17T08:30:05.630

0

Three things:

Always, always, always used prepared statements and bound parameters when dealing with untrusted (i.e., $_GET) input. Just do it.

As regards your problem with JSON, you only need to run json_encode once:

$results = [];
for($j=0;$j<count($id);$j++) {
    ...
    while($row=mysql_fetch_array($res)) {
        results[] = ...
    }
}
json_encode( $results );

Use a single SQL statement, since you have a known number of IDs to collect:
```
$dbh = new PDO($dsn, $user, $password);
$sql = "SELECT * FROM mutable WHERE id IN (?, ?, ?)";
$sth = $dbh->prepare( $sql );
foreach ( $sth->execute( [$_GET['id0'], $_GET['id1'], $_GET['id2']] ) as $row ) {
    ...
```
This is more efficient then multiple round trips to the database. For this contrived case it probably doesn't matter, but getting into good habits now will serve you in the long run.

edited Feb 17 '18 at 08:30

answered Feb 17 '18 at 08:13

hunteke

3,648
1
7
17

i'm very sure OP does not know what `$dsn` means ;) – Rotimi Feb 17 '18 at 08:14
@Akintunde007 fair. On the other hand, OP successfully connected with the `mysql*` functions, and will hopefully Google the unfamiliar details. I'm less concerned with that part of the question, however. – hunteke Feb 17 '18 at 08:17
@hunteke If i don't know the number of IDs i am getting from user, suppose 1st time he sends 1 value and other time may be 3,4 or 5 values, i will manage to collect these different IDs, but how to use these different IDs in query--- – Sami Feb 17 '18 at 08:21
In that case, either fall back to the "one at a time" approach you already know, or build up the `$sql` string. For example, say you have `$ids = ["1", "3", "5"]` as the IDs passed by the user. Then you might build the bind part as: `$bind = implode(',', array_fill(0,count($ids),'?'));` and then your sql as `$sql = "... id IN ($bind)"`. Then finally execute with `$sth->execute( $ids )` – hunteke Feb 17 '18 at 08:29

score 0 · Answer 3 · answered Feb 17 '18 at 09:47

you have used $row wrongly, declare array variable outside of loop like

    $json_response = array();
    for($j=0;$j<count($id);$j++) {
        $res=mysql_query("SELECT * FROM mutable WHERE id='$id[$j]'")
        while($row=mysql_fetch_array($res)) {
            $json_response[]=array("email"=>$row[2],"name"=>$row[3],"address"=>$row[5]);
            echo json_encode($json_response); // not good to echo here
        }
    }
    // echo json_encode($json_response); // ideally echo should be here

Fetch multiple rows in mysql single query in php with different id

3 Answers3