Zaid Yasyaf Zaid Yasyaf - 2 years ago 79
MySQL Question

PHP Repeat loop until no matching record

I have one table that contains :

TICKET | VOLUME | REFF
------------------------
111111 | 1 |
111112 | 0.5 | 111111
111113 | 2 |
111114 | 1 | 111112
111115 | 4 | 111114
111116 | 2 | 111113
111117 | 1 | 111116
and so on..


The result that i want to show (in array format) is:

[
[ 'TICKET' => 111112, 'VOLUME' => 0.5 ],
[ 'TICKET' => 111114, 'VOLUME' => 1 ],
[ 'TICKET' => 111115, 'VOLUME' => 4 ]
]


So, all the answers are parenting to ticket no. 11111.
How to do it?

Many thanks!

What i've tried so far :

$parent_ticket = 111111;
$res = [];

$cek = DB::table('data_ticket')->where('ticket', $parent_ticket)->first();

if($cek){
$cek_child1 = DB::table('data_ticket')->where('ticket', $cek->REFF)->first();

if($cek_child1){
$res[] = [ 'TICKET' => $cek_child1->TICKET, 'VOLUME' => $cek_child1->VOLUME ];
$cek_child2 = DB::table('data_ticket')->where('ticket', $cek_child1->REFF)->first();

if($cek_child2){
$res[] = [ 'TICKET' => $cek_child2->TICKET, 'VOLUME' => $cek_child2->VOLUME ];
$cek_child3 = DB::table('data_ticket')->where('ticket', $cek_child2->REFF)->first();

if($cek_child3){
// and so on....
}

}
}
}

Answer Source

You should use recursion, this is the easiest way to do what you want.

<?php

$parent_ticket = 111111;
$res = [];

$cek = DB::table('data_ticket')->where('ticket', $parent_ticket)->first();

if ($cek) {
    findAllTicketsAndReferences($cek, $res);
}

function findAllTicketsAndReferences($cek, array &$res)
{
    $cek_child1 = DB::table('data_ticket')->where('ticket', $cek->REF)->first();

    if (!$cek_child1) {
        return false;
    }

    $res[] = ['TICKET' => $cek_child1->TICKET, 'VOLUME' => $cek_child1->VOLUME];

    findAllTicketsAndReferences($cek_child1, $res);
}

This should work, the idea is to return false when there's no child anymore, otherwise it goes on and changes the array of datas.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download