CODEWITHSUNDEEP
phpmysql
denikov
denikov

Reputation: 869

MySQL select many tables, each table's results being it's own key/val pair

Hopefully this isn't a stupid question, was hard to explain in the title but...I have a certain amount of tables and most of them don't have the same structure...except startDate. For example, say there's three tables with column structures like this:

t1:
id   a   b   c   startDate

t2:
id   e   f   g   h   i    startDate

t3:
id   j   k   startDate

I want to get them all into a result which will look something like this json type format:

[{"t1":array({
    //all rows for t1
    })
 },{"t2":array({
    //all rows for t2
    })
 },{"t3":array({
    //all rows for t3
    })
 }]

I want to be able to do something like this in PHP:

foreach ($results as $key => $val) {
    if ($key == "t1") {
        //parse t1 $val array
    }elseif ($key == "t2") {
        //parse t2 $val array
    }elseif ($key == "t3") {
        //parse t3 $val array
    }
}

I know it's possible with UNION ALL but I would have to do NULL values for columns so if I had 6 or 7 tables, the query would be really messy. Is there a way to use GROUP_CONCAT to get the desired result, also with a startDate BETWEEN arg1 AND arg2?

Or...would it be best to do a for loop and query each table, then put it all together in PHP?

Upvotes: 1

Views: 65

Answers (1)

SteveTz
SteveTz

Reputation: 242

Please try this

//db connection

$json = array();
$tables = array("t1","t2","t3");
for($i=0;$i<count($tables);$i++){
    $rows = array();$row = array();
    $sql = "SELECT * FROM $tables[$i]";
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)){
        $rows[] = $row;
    }
    $json[$tables[$i]] = $rows;
}

//you can use print_r($json) to display the array or json like
echo json_encode($json)

Upvotes: 1

Related Questions