php - Should I use a JOIN function or run several queries in a loop structure? -

i have 2 mysql tables: tablea , tableb

tablea
* columnaid
* columna1
* columna2
tableb
* columnbid
* columnaid
* columnb1
* columnb2

in php, wanted have multidimensional array format

$array = array(     array(         'columnaid' => value,         'columna1' => value,         'columna2' => value,         'tableb' => array(             array(                 'columnbid' => value,                 'columnaid' => value,                 'columnb1' => value,                 'columnb2' => value             )         )     ) ); 

so can loop in way

foreach($array $i => $tablea) {     echo 'columnaid' . $tablea['columnaid'];     echo 'columna1' . $tablea['columna1'];     echo 'columna2' . $tablea['columna2'];     echo 'tableb\'s';     foreach($value['tableb'] $j => $tableb) {         echo $tableb['...']...         echo $tableb['...']...     } } 

my problem that, best way or proper way of querying mysql database can achieve goal?

solution1 --- 1 i'm using

$array = array(); $rs = mysqli_query("select * tablea", $con); while ($row = mysqli_fetch_assoc($rs)) {     $rs2 = mysqli_query("select * table2 columnaid=" . $row['columnaid'], $con);     // $array = result in array     $row['tableb'] = $array2; } 

i'm doubting code cause querying database.

solution2

$rs = mysqli_query("select * tablea join tableb on tablea.columnaid=tableb.columnaid"); while ($row = mysqli_fet...) {     // code } 

the second solution query once, if have thousand of rows in tablea , thousand of rows in tableb each tableb.columnaid (1 tablea.columnaid = 1000 tableb.columnaid), solution2 takes time solution1?

neither of 2 solutions proposed optimal, solution 1 unpredictable , inherently flawed!

one of first things learn when dealing large databases 'the best way' query dependent upon factors (referred meta-data) within database:

• how many rows there are.
• how many tables querying.
• the size of each row.

because of this, there's unlikely silver bullet solution problem. database not same database, need benchmark different optimizations if need best performance available.

you find applying & building correct indexes (and understanding native implementation of indexes in mysql) in database lot more you.

there golden rules queries should broken:

• don't them in loop structures. tempting is, overhead on creating connection, executing query , getting response high.
• avoid select * unless needed. selecting more columns increase overhead of sql operations.
• know thy indexes. use explain feature can see indexes being used, optimize queries use what's available , create new ones.

because of this, of 2 i'd go second query (replacing select * columns want), but there better ways structure query if have time optimize.

however, speed should not consideration in this, there great reason not use suggestion one:

predictability: why read-locks thing

one of other answers suggests having table locked long period of time bad thing, , therefore multiple-query solution good.

i argue this couldn't further truth. in fact, i'd argue in many cases predictability of running single locking select query greater argument running query optimization & speed benefits.

first of all, when run select (read-only) query on myisam or innodb database (default systems mysql), happens table read-locked. prevents write operations happening on table until read-lock surrendered (either our select query completes or fails). other select queries not affected, if you're running multi-threaded application, continue work.

this delay thing. why, may ask? relational data integrity.

let's take example: we're running operation list of items in inventory of bunch of users on game, join:

select * `users` join `items` on `users`.`id`=`items`.`inventory_id` `users`.`logged_in` = 1; 

what happens if, during query operation, user trades item user? using query, see game state when started query: item exists once, in inventory of user had before ran query.

but, happens if we're running in loop?

depending on whether user traded before or after read details, , in order read inventory of 2 players, there 4 possibilities:

1. the item shown in first user's inventory (scan user b -> scan user -> item traded or scan user b -> scan user -> item traded).
2. the item shown in second user's inventory (item traded -> scan user -> scan user b or item traded -> scan user b -> scan user a).
3. the item shown in both inventories (scan user -> item traded -> scan user b).
4. the item shown in neither of user's inventories (scan user b -> item traded -> scan user a).

what means we unable predict results of query or ensure relational integrity.

if you're planning give $5,000 guy item id 1000000 @ midnight on tuesday, hope have $10k on hand. if program relies on unique items being unique when snapshots taken, possibly raise exception kind of query.

locking because increases predictability , protects integrity of results.

note: force loop lock transaction, still slower.

oh, , finally, use prepared statements!

you should never have statement looks this:

mysqli_query("select * table2 columnaid=" . $row['columnaid'], $con); 

mysqli has support prepared statements. read them , use them, avoid something terrible happening database.