JSON parse Mysql query result in php pdo

Question

I am trying to do following

$statement = $conn->prepare('SELECT * FROM myTable');
$statement->execute();

if(!($row = $statement->fetchAll(PDO::FETCH_ASSOC))) 
{
    return false;
}

$conn = null;
} catch(PDOException $e) {
    throw $e;
    return false;
}       

return  return json_encode(array('Result'=>$row);

Works and fetches all entries in a table make then JSON Array and send them,

However I want to make a query where selected ids must be send in a JSON Array

E.g 10, 20, 30

I assume that this will be done in a For loop perhaps

$statement = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
$statement->bindParam(':id', $id, PDO::PARAM_STR);
$statement->execute();

$row = $statement->fetch(PDO::FETCH_OBJ)))

Now suppose i have id's = 10,20,30 i want to append all of them in a JSON Array How can i do that?

just like return json_encode(array('Result'=>$row);

Edited Code

    function GetMyMembers() 
    {
        $myId = trim($_REQUEST['myId']);

        try {
            $conn = $this->GetDBConnection();

            $statement = $conn->prepare('SELECT valId FROM memList WHERE myId=:myId' );
            $statement->bindParam(':myId', $myId, PDO::PARAM_INT);
            $statement->execute();

            if(!($row = $statement->fetchAll(PDO::FETCH_ASSOC))) 
            {
                return false;
            }

// $row contains ALL THE ID'S

            $placeholders = str_repeat('?,', count($row));
            $placeholders = substr($placeholders, 0, -1);
            $sql = "SELECT id, * FROM players WHERE id IN ($placeholders)";
            $statement = $conn->prepare($sql);
            $statement->execute($row);
            $rows = $sth->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);

            $conn = null;
        } catch(PDOException $e) {
            throw $e;
            return false;
        }       
        return $rows;
    }

you need to be more certain in expressing your wishes

Your Common Sense
– Your Common Sense

2013-01-29 07:51:33 +00:00
Commented Jan 29, 2013 at 7:51 — Your Common Sense
– Your Common Sense, Commented Jan 29, 2013 at 7:51

katalin_2003 · Accepted Answer · 2014-09-21 19:27:30Z

1

$statement = $conn->prepare('SELECT * FROM myTable');
$statement->execute();
$data = array();
while($row = $statement->fetch(PDO::FETCH_ASSOC))) 
{
    $data[$row['id']] = $row;
}
return json_encode(array('Result'=>$data));

Btw, using raw API is not convenient. With Database abstraction library your code can be as short as 2 following lines:

$data = $db->getInd("id",'SELECT * FROM myTable');
return json_encode(array('Result'=>$data));

Edit:
if you have an array of ids, you need more complex code

$ids  = array(1,2,3);
$data = array();
$statement = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
foreach ($ids as $id) {
    $statement->bindValue(':id', $id, PDO::PARAM_STR);
    $statement->execute();
    $data[] = $statement->fetch(PDO::FETCH_OBJ);
}
return json_encode(array('Result'=>$data));

But while using Database abstraction library, there will be the same 2 lines:

$data = $db->getAll('SELECT * FROM myTable where id IN (?a)', $ids);
return json_encode(array('Result'=>$data));

Edit2:
if you need ids only

$statement = $conn->prepare('SELECT id FROM myTable');
$statement->execute();
$data = array();
while($row = $statement->fetch(PDO::FETCH_ASSOC))) 
{
    $data[] = $row['id'];
}
return json_encode(array('Result'=>$data));

while using Database abstraction library, it's still 2 lines:

$ids = $db->getCol('SELECT id FROM myTable');
return json_encode(array('Result'=>$ids));

edited Sep 21, 2014 at 19:27

katalin_2003

8091 gold badge18 silver badges30 bronze badges

answered Jan 29, 2013 at 7:33

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

2 Comments

Muhammad Umar Over a year ago

($row = $statement->fetchAll(PDO::FETCH_ASSOC)) i have this code to get list of all entries which will give me ids, now to make $ids = array(1,2,3); how will i do that? please point that out too –

Muhammad Umar Over a year ago

didn't get my point, with this above i am getting ids first then need to make a query to get entries of these ids, so my question now was $row = $statement->fetchAll(PDO::FETCH_ASSOC)) Conversion to $ids = array(1,2,3);

cetver · Accepted Answer · 2013-01-29 08:07:18Z

0

$ids = array(1,2,3);
$placeholders = str_repeat('?,', count($ids));
$placeholders = substr($placeholders, 0, -1);
$sql = "SELECT id, * FROM table WHERE id IN ($placeholders)";
$sth = $dbh->prepare($sql);
$sth->execute($ids);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);
echo json_encode(array('Result' => $rows));

Based on additional comments:
Best option:

$sql = '
SELECT *
FROM table1 AS t1
INNER JOIN table2 t2
ON  t2.foreign_key = t1.id
';

or

$sql = 'SELECT id FROM table1';
$sth = $dbh->prepare($sth);
$sth->execute();
$ids = $sth->fetchColumn();
//next look above

edited Jan 29, 2013 at 8:07

answered Jan 29, 2013 at 7:38

cetver

11.9k6 gold badges42 silver badges59 bronze badges

4 Comments

Muhammad Umar Over a year ago

($row = $statement->fetchAll(PDO::FETCH_ASSOC)) i have this code to get list of all entries which will give me ids, now to make $ids = array(1,2,3); how will i do that? please point that out too

cetver Over a year ago

I'm confused, you need only id's from query ?

Muhammad Umar Over a year ago

no, i am getting ids from another table, since i was hoping to use a for loop but here u used array(1,2,3), how to make my id list like this

cetver Over a year ago

as I understood: you have data from table1, and you need fetch data from table2 where table2.foreign_key in (table1.id, table1.id, ...). Correct ?

Collectives™ on Stack Overflow

JSON parse Mysql query result in php pdo

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related