PHP Select multiple tables as array

Question

I have multiple tables with options for my software, I want to request them in a single request(Database postgress, php pdo) and return it like in the following example.

--- table userStatus ---
-- id --     -- name --
    1         loggedIn
    2         loggedOut

--- table customerStatus ---
-- id --     -- name --
    1         active
    2         nonActive

The response should be formatted like:

O => 
  userStatus=>
    1 = loggedIn,
    2 = loggedOut
  customerStatus =>
    1 = active,
    2 = nonActive

Tried it with different fetch methods but nothing came to this solution. In the end I done now 20 requests and combine them manually : )

You can try to use multi-query [multi statement](http://php.net/manual/en/mysqli.quickstart.multiple-statement.php) [multi-query](http://php.net/manual/es/mysqli.multi-query.php) — fxlacroix, Jun 09 '17 at 09:49
Ty will try to find a equivalent in pdo. Is it possible to store the result from select as a array, there the first column is the index ? — Doomenik, Jun 09 '17 at 10:02
@Doomenik you have to do some stuff on php side also for manage array — Ahmed Ginani, Jun 09 '17 at 10:11
@AhmedGinani I just tought its no problem to select a table as an array — Doomenik, Jun 09 '17 at 10:26
@Doomenik i think you looking for $q->fetchAll(PDO::FETCH_KEY_PAIR); https://stackoverflow.com/questions/1387563/php-pdostatement-fetch-a-row-as-the-first-column-as-the-key-of-an-array — Ahmed Ginani, Jun 09 '17 at 10:29

score 0 · Answer 1 · answered Jun 09 '17 at 10:00

0

Perform a single UNION query.

SELECT * FROM userStatus
UNION ALL
SELECT * FROM customerStatus

Rows will be combined in a single result set.

answered Jun 09 '17 at 10:00

Chris Lam

3,526
13
10

This wouldn´t work, not all tables have the same ammount of columns and it would stil return them as multiple rowsets and not combined by table. – Doomenik Jun 09 '17 at 10:10

Ahmed Ginani · Accepted Answer · 2017-06-09T10:31:56.780

Use multi query for it Below is simple example for that :

$query  = "SELECT * from userStatus;";
$query .= "SELECT * from customerStatus";

/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

If You want to get first column as a key you can achieve with pdo take a look PHP PDOStatement: Fetch A Row, as the First Column as the Key of an Array

Will try it out. Thank you. – Doomenik Jun 09 '17 at 10:12 — Doomenik, Jun 09 '17 at 10:12

PHP Select multiple tables as array

2 Answers2