MySQL ORDER BY and while loop

Question

I already asked a question belonging this issue here

The code from the accepted answer by RiggsFolly works really proper but there is a small issue with that. I needed some days to test it out and searched for the reason why this is not the best way to solve the main goal. I was pleased to open a new question.

The code from RiggsFolly is based on $current_provider so the while-loop checks on every round if $current_provider has changed. So far so good. BUT now I needed to add a comprehensive logic. It meens that I added a true/false variable that simply checks if a value from an fetched object is equal to a certain string. This comparison is focused on a specific list item and not to the basic $current_provider.

So the goal is that $current_provider checks each fetched object for true/false and will be independent from $current_provider. At the moment I try to extend with a second loop but just want to give an example in the hope that it will be clear what to achieve:

$service = $db->query("SELECT * FROM `system` ORDER BY provider, artist");
$provider = NULL;
$close = false;

while ($data = $service->fetch_object()) {

    $amount_1 = $data->digit_1; //db-structure: float
    $amount_2 = $data->digit_2; //db-structure: float

    if ($amount_1 == $amount_2) {
        $close = true;
    }
    if ( $current_provider != $data->provider ) {  
        if ( $current_provider !== NULL ) {
            echo '</div>close container in case of current_provider is already set';
        }
        echo '<div class="provider">open a new container in case of current_provider is not like data->provider or empty';
        $current_provider = $data->provider;
    }
    echo 'some styling for each object';   
    if ($close === true ) { 
        echo '<div class="specific">if the amount_1 is same like amount_2 for a single object add only for this object a certain div';  
    } else {
        echo '<div>show standard container even on specific object';
    }   
echo '</div><!--close container provider-->';
}

Kind regards.

Can you expand upon if ($something === $another) { as neither of these variables are used in you sample code — RiggsFolly
– RiggsFolly, Commented Jul 22, 2015 at 13:27
Hello again :D Thanks for answering again. I updated the source code to show what I mean. — John
– John, Commented Jul 22, 2015 at 14:15
Your original code added the specific container on each element inside the provider container. That causes the problem that maybe the first element is digit_1 == digit_2 but the second isn't but it also adds the specific container on all other elements wihtin the provider even the second and third element what is even not true. — John
– John, Commented Jul 22, 2015 at 14:24
Just one small point. My original code as you call it was fit for purpose to answer the question asked. You made no mention of this new requirement. It is true, my original answer does not work with you new requirements. But beating me over the head with my lack of clarevoyance is not going to get your new requirement solved any quicker. Instead try and describe your requirement clearly An example of the inputs and required output would be better than the pseudo code you are offering up in your Specification — RiggsFolly
– RiggsFolly, Commented Jul 22, 2015 at 14:42

RiggsFolly · Accepted Answer · 2015-07-22 14:35:23Z

I am still not sure I understand what you are actually trying to achieve here but maybe if you lay your decision making out like this it will all seem more obvious what you may need to do in addition to what I am suggesting.

The moving of

$amount_1 = $data->digit_1; 
$amount_2 = $data->digit_2;

from a perfectly good object properties to 2 scalar variables is totally unnecessary why store everything twice, you will eventually run out of memory, but more importantly if you leave them in the object and test them using if ($data->digit_1 == $$data->digit_2) { its never confusing where this data came from!

Also testing the digits at the top of the loop only to set ANOTHER scalar variable to use later at the bottom of the loop is a waste of time. Those properties dont change between top and bottom of the loop so test the actual object where you want to make the decision and then put out the required HTML right there and then. Another potential confusion averted and 8 to 16 bytes of memory not wasted!

$service = $db->query("SELECT * FROM `system` ORDER BY provider, artist");
$current_provider = NULL;

while ($data = $service->fetch_object()) {

    if ( $current_provider != $data->provider ) {  

        if ( $current_provider !== NULL ) {
            echo '</div>close container in case of current_provider is already set';
        }

        echo '<div class="provider">open a new container in case of current_provider is not like data->provider or empty';
        $current_provider = $data->provider;
    }

    echo 'some styling for each object';   


    // at this point we test the 2 digits and if equal
    // add an extra bit of HTML to the output
    if ($data->digit_1 == $data->digit_2) {
        echo '<div class="specific">if the amount_1 same like amount_2 for a single object add only for this object an certain div';  
    } else {
        echo '<div>show standard container even on specific object';
    }   
echo '</div>;
}

Thanks again. I already tried to do it exactly like you did but the pseudo code i much much much shorter than the original so that the code still was inside if ( $current_provider != $data->provider ) what I could find that easily. Worked up into the late night so it may was already a little bit late. Thanks for helping me out.

num8er · Accepted Answer · 2015-07-22 12:23:42Z

To avoid playing with opening and closing element better to store them in array and in the end output them.

Look at my example, it's simple:

$service = $db->query("SELECT * FROM `system` ORDER BY provider, artist");
$provider = NULL;
$lines = array();
while ($data = $service->fetch_object()) {
    $close = false;
    if ($something === $another) {
        $close = true;
    }

    if ( $provider != $data->provider ) {  
        $lines[] = '<div class="provider">'.$data->provider.'</div>';
        $provider = $data->provider;
    }

    if ($close === true ) { 
        $lines[] = '<div class="specific">add container for just a specific object when close === true within the while loop</div>';  
    } else {
        $lines[] = '<div>show standard container on specific object</div>';
    }   
}

foreach($lines AS $line) {
    echo $line;
}

Collectives™ on Stack Overflow

MySQL ORDER BY and while loop

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related