Swee Hong Swee Hong - 2 months ago 13
MySQL Question

Foreach in a foreach? show uncorrect data

I have 2 table.

t1
and
t2
.

t1
data as below:

+--------+---------------+--------------+
| mid | name | desc |
+---------------------------------------+
| 1 | Fris | Helo |
| 2 | Kenn | Wow! |
| 3 | Henny | CCC |
+---------------------------------------+


t2
data as below:

+--------+---------+----------------+
| tid | mid | tcode |
+-----------------------------------+
| 1 | 1 | 1 |
| 2 | 1 | GG |
| 3 | 1 | TTTTTT! |
+-----------------------------------+


Now my php coding is like this:

$xzdtall = DB::fetch_all("SELECT * FROM t1 ORDER BY mid ASC");
foreach($xzdtall as $xzdt){
$testing = DB::fetch_all("SELECT * FROM t2 WHERE mid = $xzdt['mid'] ORDER BY tid ASC");
foreach($testing as $test){
$testlist[] = $test;
}
$xzdtlist[] = $xzdt;
}


And now my looping as below:

#my looping
<!--{loop $xzdtlist $xzdt}-->
<div>$xzdt[name] <!--{loop $testlist $test}--><b>$test[tcode]</b><!--{/loop}--></div>
<!--{/loop}-->


My final output:

<div>Fris <b>1</b><b>GG</b><b>TTTTTT!</b></div>
<div>Kenn <b>1</b><b>GG</b><b>TTTTTT!</b></div>
<div>Henny <b>1</b><b>GG</b><b>TTTTTT!</b></div>


And actually I need it be like this since
Kenn
's AND
Henny
's
mid
does not inside t2:

<div>Fris <b>1</b><b>GG</b><b>TTTTTT!</b></div>
<div>Kenn</div>
<div>Henny</div>

Answer

$testlist variable has different values for every $xzdt, so you loop should be:

$xzdtall = DB::fetch_all("SELECT * FROM t1 ORDER BY mid ASC");
foreach ($xzdtall as $xzdt){
    // empty $testlist
    $testlist = array();
    $testing = DB::fetch_all("SELECT * FROM t2 WHERE mid = $xzdt['mid'] ORDER BY tid ASC");
    foreach($testing as $test){
        $testlist[] = $test;
    }

    // add `$testlist` to $xzdt
    $xzdt['t2s'] = $testlist;

    $xzdtlist[] = $xzdt;
}

Then in your template (don't know what engine it is) something like that should work:

<!--{loop $xzdtlist $xzdt}-->
<div>$xzdt[name] <!--{loop $xzdt[t2s] $test}--><b>$test[tcode]</b><!--{/loop}--></div>
                                 ^ notice variable here
<!--{/loop}-->

Also you can test if $xzdt[t2s] is not empty.

In the end, I advise you to learn about mysql JOINs and replace your cyclic queries with a single query with JOIN.