php mysql - select all rows from multiple tables then save as array of objects (json)

Question

I have this, which works perfectly:

include '../sql.php';
$d = $_POST['forum'] or die('no forum name was given');
$p = new PDO('mysql:host=localhost; dbname=mydb; charset=utf8', $user, $pw);
$s = $p -> query('SELECT * FROM `_' . $d . '`');
echo json_encode($s -> fetchAll(PDO::FETCH_ASSOC));

In the above example, $d is a string (the name of table I want to get all the results from) and I use it to get all the rows of that table and put it into an array (as an object) so that I can retrieve it client-side.

That code produces something like this:

[{"id":"1", "name":"bob"}, {"id":"2", "name":"frank"}]

What I'd like to do is make $d an array of table names. Then retrieve all rows from all of those tables like a loop or simply select all of those tables at the same time and return the results.

The result should look like this:

[
    [{"id":"1", "name":"bob"}, {"id":"2", "name":"frank"}], //table 1
    [{"id":"1", "color":"red"}, {"id":"2", "color":"blue"}], //table 2
    [{"id":"1", "style":"conservative"}, {"id":"2", "style":"gothic"}], //table 3
]

How do I do this?

It doesn't look like the author found an acceptable answer there. The table names are dynamic and will change. The table names in that one look static. — Shazboticus S Shazbot
– Shazboticus S Shazbot, Commented Oct 15, 2014 at 21:10
They select all table names in the DB. Nothing static there. — andy
– andy, Commented Oct 15, 2014 at 21:13

Shazboticus S Shazbot · Accepted Answer · 2014-10-15 21:22:58Z

1

try something like:

include '../sql.php';
$d = $_POST['tables'] or die('no forum name was given');
$p = new PDO('mysql:host=localhost; dbname=mydb; charset=utf8', $user, $pw);

$ret = [];
foreach ($d as $t){
    $s = $p->query('SELECT * FROM `_' . $t . '`');
    $ret[$t] = $s -> fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($ret);

edited Oct 15, 2014 at 21:22

Shazboticus S Shazbot

1,3292 gold badges16 silver badges27 bronze badges

answered Oct 15, 2014 at 21:06

user1269942

3,8521 gold badge25 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

php mysql - select all rows from multiple tables then save as array of objects (json)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related