The sample of the two tables in the same query in different arrays?

Question

The sample of the two tables in the same query in different arrays?

Good afternoon.

There are two tables, head and body. They are the same string id, name.
How to make a single query and output the table in different arrays?

Something like:

$result = $conn->query("SELECT * FROM head body");

while ($row = $result->fetch_assoc()) {
 $arr['head'][] = $row['head'];
 $arr['body'][] = $row['body'];
}

Thank you all.

boris_Bernhard asked July 9th 19 at 10:14

Related questions

More answers about "The sample of the two tables in the same query in different arrays?"

3 answers

brionna.Champl answered on July 9th 19 at 10:18

for example

$result = $conn->query("SELECT t1.id as id1, t1.name as name1, t2.id as id2, t2.name as name2 
FROM head t1 body t2");

while ($row = $result->fetch_assoc()) {
 $arr['head'][] = $row['id1'] . $row[name1'] ;
 $arr['body'][] = $row['id2'] . $row[name2'] ;
}

then the duplicates from arrays to remove.

Tell me, and why do I get duplicates? I have a table of 2 rows and 4 displays, the double. - boris_Bernhard commented on July 9th 19 at 10:21

these 4 rows are identical the same. Some data is repeated, some not. - Baby51 commented on July 9th 19 at 10:24

glennie_Rempel10 answered on July 9th 19 at 10:20

Why don't you have relationships between tables ?

select h.id, h.name head_name, b.name body_name, from head h, body b where
where h.id=b.id

Baby51 answered on · Accepted Answer · 2019-07-09T10:16:15.265Z

Baby51 answered on July 9th 19 at 10:16

Solution

What is the problem to do 2 query? Why "perversion" with SQL'eat?

Heh well if you so desire...

$result = $conn->query('
(
 select id, name, "head" as table_name
 from head
 ) union (
 select id, name, "body" as table_name
 from body
)
');

while ($row = $result->fetch_assoc()) {
 $tableName = $row['table_name'];
unset($row['table_name']);
 $arr[$tableName][] = $row;
}

$result->free();

But this ride only if head.id and body.id have the same type, as well as head.name and body.name also have the same type.

But if such requests have to do 5 or 10? Easier and less of a burden from one query I think? Yes, id and name have the same type. - boris_Bernhard commented on July 9th 19 at 10:19

No, not less. Either it will be a lengthy union, either sly the join in which then without half-liter not to understand.
Less only in the case of the competent denormalization and placement of the indices in the denormalized table.
So better a few simple queries, which are easier to maintain the sampling result in each easy to cache, and the cache is more effective in reducing the number of queries and amount of retrieved data.
And finally, don't do premature optimization. That's when the battle with real users and data will see that these selections become your bottleneck, then you have to think what to do next: to use a cache, optimize a query, renormalizability table, change the RDBMS, or start to use nosql will become clear only when will start the real usage of the application on live Kazakh. - Baby51 commented on July 9th 19 at 10:22

: Got it, thanks. - boris_Bernhard commented on July 9th 19 at 10:25