CODEWITHSUNDEEP
phpmysql
Piers Blinco
Piers Blinco

Reputation: 11

sorting price without doing it in sql

I am doing a ticket pricing table. It works perfectly apart from one niggle, the prices are coming out in the wrong order, here is what i have so far:

$totalresult = mysql_query("select * from ticket_pricing  WHERE (service_id='".mysql_real_escape_string($_POST['service_id'])."') and (boarding_point='".mysql_real_escape_string($_POST['boarding_point'])."') order by ticket_type DESC") or die(mysql_error());

while($row = mysql_fetch_array($totalresult)){ 
    if(strtolower($row['ticket_type']) === "single"){
        if (!$a++){  
                    echo($row['ticket_type']);
            $a++;
        }
    ?>
    <span>
        <?php echo($row['price']);?>
    </span>                 
    <?php
    }
    if(strtolower($row['ticket_type']) === "return"){
        if (!$b++){  
                    echo("<br />" . $row['ticket_type']);
            $b++;
        }
    ?>
    <span>
        <?php echo($row['price']);?>
    </span>                 
    <?php
    }   
    if(strtolower($row['ticket_type']) === "period"){
        if (!$c++){  
                    echo("<br />" . $row['ticket_type']);
            $c++;
        }
    ?>
    <span>
        <?php echo($row['price']);?>
    </span>                 
    <?php
    }           
    if(strtolower($row['ticket_type']) === "group"){
        if (!$d++){  
                    echo("<br />" . $row['ticket_type']);
            $d++;
        }
    ?>
    <span>
        <?php echo($row['price']);?>
    </span>                 
    <?php
    }                                   
}

Upvotes: 0

Views: 109

Answers (3)

xavier Z
xavier Z

Reputation: 144

I'm with sammaye for the suggestion but I will answer the question because it can be usefull with some custom D-base Or C-base system wich doesnt support multiple order fileds.

so you can do it with array sorting :

$totalresult = mysql_query("select * from ticket_pricing  WHERE (service_id='".mysql_real_escape_string($_POST['service_id'])."') and (boarding_point='".mysql_real_escape_string($_POST['boarding_point'])."') order by ticket_type DESC") or die(mysql_error());

   $results = array();//instanciate array
    while($row = mysql_fetch_array($totalresult)){ 
        $type = $row['ticket_type'];//get current type to preserve type order
        $price = $row['ticket_price'];//get the current price to sort by price
        if(!is_array($results[$type])){
            $results[$type]=array();//current type is new
        }
        $results[$type][$price] = $row;//store entire row datas     
    }

    $types = array_keys($results);
    $nb = count($types);
    for($i=0;$i<$nb;$i++){//get the current type, preserving group
        $type = $types[$i];
        $prices = array_keys($results[$types]);//extract prices of the group
        asort($prices);//sort prices ASC
        //arsort($prices);//sort price DESC
        foreach($prices As $price){
            $row = $results[$types][$price];//now you can get you'r datas and start you'r process
        }
    }

Upvotes: 0

Matt Williams
Matt Williams

Reputation: 53

I would have to say sort the prices in mysql, it would be much easier, faster and more efficient. Is there a specific reason that you don't want to do the ordering in sql?

Surely you can just do order by ticket_type DESC, price ASC/DESC.

Upvotes: 0

Sammaye
Sammaye

Reputation: 43884

Why not just sort twice over using the price as the second method of sort?

 $totalresult = mysql_query("select * from ticket_pricing  
     WHERE (service_id='".mysql_real_escape_string($_POST['service_id'])."') 
     and (boarding_point='".mysql_real_escape_string($_POST['boarding_point'])."') 
     order by ticket_type DESC, price ASC") or die(mysql_error())

Upvotes: 1

Related Questions