Rows with same id in same div

Question

I have a db-table called vm_kupon. It looks a little like this:

|id  |uid  |bet  |
|1   |20   |1    |
|2   |20   |2    |
|1   |21   |X    |
|2   |21   |1    |
|1   |22   |X    |
|2   |22   |X    |

I would like to show these bets, but I would like it to group the rows with the same uid. Right now I'm doing it like this:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$date = Date('j');

$query
    ->SELECT(array('a.*', 'b.*', 'c.*'))
    ->FROM($db->quoteName('vm_kampe', 'a'))
    ->JOIN('INNER', $db->quoteName('vm_kuponer', 'b') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.id') . ')')
    ->JOIN('INNER', $db->quoteName('vm_tabel', 'c') . ' ON (' . $db->quoteName('b.uid') . ' = ' . $db->quoteName('c.uid') . ')')
    ->WHERE($db->quoteName('a.dato') . ' = ' . $db->quote($date) . ' AND ' . $db->quoteName('c.liga') . ' = ' . $db->quote('bs'));

$db->setQuery($query);
$results = $db->loadObjectList();

Then I tried adapting the answer to my code, but I'm not that good.

//INSTANTIATE NEW EMPTY ARRAY
$arr = array();

//LOOP THROUGH RESULTS AND GROUP THEM INTO NEW ARRAY BASED ON UID AS ARRAY KEY
foreach($results as $row){
    $arr[$row->uid][] = $row;
}

//SORT UIDS ARRAY BASED ON KEY VALUE ASCENDING... so keys are 20, 21, 22, etc...
ksort($arr);

//LOOP THROUGH NEW ARRAY TO CREATE YOUR DISPLAY
foreach ($arr as $uidGroup => $bets){
    echo "HERE IS THE GROUP YOU ARE CURRENTLY DISPLAYING<br />";
    echo 'Group - '.$uidGroup.'<br />';
    foreach($bets as $b){
        echo '<div class="dagens_kampe_kupon">ID: '.$b->id.' - Bet: '.$b->bet.'</div>';
    }
};

Answer 1

//MULTIDIMENSIONAL ARRAY OF BETS FROM EXAMPLE
$results = array(
    array('id' =>1, 'uid'=> 20, 'bet' => '1'),
    array('id' =>2, 'uid'=> 20, 'bet' => '2'),
    array('id' =>1, 'uid'=> 21, 'bet' => 'X'),
    array('id' =>2, 'uid'=> 21, 'bet' => '1'),
    array('id' =>1, 'uid'=> 22, 'bet' => 'X'),
    array('id' =>2, 'uid'=> 22, 'bet' => 'X')
);

echo 'HERE IS ORIGINAL ARRAY STRUCTURE<br />';
echo '<pre>';
print_r($results);
echo '</pre>';

//TEMP ARRAY TO HOLD NEW SORTING
$arr = array();

//LOOP THROUGH RESULTS ARRAY GROUP THEM INTO NEW ARRAY BASED ON UID AS ARRAY KEY
foreach($results as $row){
    $arr[$row['uid']][] = $row;
}

//SORT ARR ARRAY BASED ON KEY VALUE ASCENDING... so keys are 20, 21, 22, etc...
ksort($arr);

echo 'HERE IS NEW ARRAY STRUCTURE<br />';
//PRINT OUT THE ARR ARRAY SO YOU CAN SEE NEW STRUCTURE
echo '<pre>';
print_r($arr);
echo '</pre>';


//LOOP THROUGH NEW ARRAY TO CREATE YOUR DISPLAY
foreach ($arr as $uidGroup => $bets){
    echo "HERE IS THE GROUP YOU ARE CURRENTLY DISPLAYING<br />";
    echo 'Group - '.$uidGroup.'<br />';
    foreach($bets as $b){
        echo '<div class="dagens_kampe_kupon">ID: '.$b['id'].' - Bet: '.$b['bet'].'</div>';
    }
}

You will have to change around the loops here so that everything is working using objects returned from your DB query.

Here is a working example: http://phpfiddle.org/main/code/izru-gyzm Press F9 to execute the code on that page.

You should be able to easily adapt this answer to your returned object list.

Answer 2

I have several optimizations/refinements/suggestions to offer. First the code block, then the explanations.

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("A.id, B.uid, C.bet")
                ->from("vm_kampe A")
                ->innerJoin("vm_kuponer B ON A.id = B.id")
                ->innerJoin("vm_tabel C ON B.uid = C.uid")
                ->where("A.dato = DAYOFYEAR(CURDATE()) AND C.liga = 'bs'")
                ->order("B.uid, A.id");
    $db->setQuery($query);

    if (!$results = $db->loadObjectList()) {
        // (No Matches Today)
        JFactory::getApplication()->enqueueMessage('Ingen kampe i dag', 'notice');
    } else {
        $group = null;
        foreach ($results as $row) {
            if ($row->uid !== $group) {
                if ($group !== null) {
                    echo "</div>";
                }
                echo "<div class=\"kupon_gruppe\">Group - $row->uid:";
            }
            echo "<div class=\"dagens_kampe_kupon\">ID: $row->id - Bet: $row->bet</div>";
            $group = $row->uid;
        };
        echo "</div>";
    }
} catch (Exception $e) {
    // (Syntax Failure) use these to debug: $query->dump() && $e->getMessage()
    JFactory::getApplication()->enqueueMessage('Syntaksfejl', 'error');    
}

Now the itemized list of what I've done and why:

• I have removed $date because MySQL already has the ability to generate this value on its own. This reduces the number of variables in the global scope and avoids variable interpolation. Admittedly, MySQL takes two function calls to generate it -- this is a personal preference decision.
• Joomla method chaining can be done from getQuery(). I chain as early as possible to avoid repeating $query (D.R.Y. principle of coding).
• I don't know exactly which table contains bet, but best coding practice demands that you only ask the database to return the values that you intend to use. Only use * when you need all of the columns in a table.
• In the query chain, I am using lowercase method calls because that is what the Joomla documentation uses (when writing raw MySQL, I always UPPERCASE my keywords).
• While making a bunch of qn() and q() calls on tables, aliases, columns, and values is often recommended on JSE to "error on the side of caution/security", the truth is, none of your values need these method calls. To make the code more brief and readable, I've removed all of these calls with no impact on security.
• I use UPPERCASE table aliases because I think they make the query more readable. Often tables and columns are lowercase, so this offers an immediate distinction.
• DAYOFYEAR() puts php's date('j') out of a job and provides a more intuitive syntax for future developers/readers of your code.
• MySQL is VERY good at sorting data, let's perform this process before the resultset is generated so that php can do what it good at -- delivering dynamic content. The ORDER BY clause is sorting by uid ASC first then id ASC when uid is a tie.
• This is the generated query:
• When there are no rows in the resultset (this is done with ! before $results in the conditional), I am issuing a blue "notice" via enqueueMessage(). For anyone unfamiliar with if (!$results = $db->loadObjectList()), this is both declaring $results and checking it for a "false-y" value.
• You do not need to loop the resultset to prepare the data, then loop it again and employ a nested loop to display your desired html. This can all be done with one clever foreach loop. Since the rows are ordered by uid, you can check each iteration of the resultset for a new uid value and conditionally output the appropriate html. Because the html can be structured in a variety of ways, I'll offer two that will serve up two different styles for your consideration. A Single-Stack of Divs or Grouped Divs with Nested Child Divs My code block above is using the nested version.
• A try {} catch {} block offers a very clean way to handle syntax errors across multiple possible breakpoints. Only print $query->dump() and $e->getMessage() to screen when developing/debugging because you don't want these details to be publicly exposed.