ridho pratama ridho pratama - 6 months ago 11
SQL Question

sort calculation data to rank my data in php

How can I sort my data to give rate like 5 stars, 4 stars, until 1 stars.

This is my query

function top5cust($tahun, $bulan)
{
if ($tahun == 'semua' && $bulan == 'semua'){
$data = $this->db->query("SELECT nama_jalan, no_rumah, jarak, kelurahan, kecamatan, kota, COUNT(*) as freq
from t_cust GROUP by nama_jalan, no_rumah, jarak,kelurahan, kecamatan, kota
ORDER by freq DESC LIMIT 5");

}
else
{

$data = $this->db->query("SELECT nama_jalan, no_rumah, jarak, kelurahan, kecamatan, kota, COUNT(*) as freq
from t_cust
WHERE YEAR(tgl_req) = $tahun AND MONTH(tgl_req) = $bulan
group by nama_jalan, no_rumah, jarak, jarak,kelurahan, kecamatan, kota
ORDER by freq DESC LIMIT 5");
}
return $data->result();
}


and this is my php code

<table>
<thead>
<tr>
<th><center>Alamat</center></th>
<th><center>Jarak</center></th>
<th><center>Frekuensi</center></th>

<th><center>Rate</center></th>
</tr>
</thead>
<tbody>
<?php

$cost=2000;
$totalbeli=50000;

foreach($top5cust as $data){
$frekuensi=$data->freq;
$jaraks=$data->jarak;
$perhitunganrank=($frekuensi*$totalbeli)-($jaraks*$cost*$frekuensi);


echo "<tr>";
echo "<td>".$data->nama_jalan." no ".$data->no_rumah.", ".$data->kelurahan.", ".$data->kecamatan.", ".$data->kota." </td>";
echo "<td>".$data->jarak."</td>";
echo "<td>".$data->freq."</td>";
echo '<td>'.$perhitunganrank.'</td>';
echo"</tr>";
}?>
</tbody>
</table>


How can I sort data and give rate by
$perhitunganrank
but without echo it.
Sorry if my question not clear my english is not good.

regards,
ridho

Answer

You can use usort, you can see the documentation here: http://php.net/manual/en/function.usort.php an example in your case would be:

$cost=2000;
$totalbeli=50000;

//We create an empty array. We need this array because PDO resultsets are iterable objects which means you can't iterate twice

$my_array = array();

//Now we put objects into this empty array. Each row from the resultset will be a new object.

foreach($top5cust as $data){
// The following is a shallow copy (using the keyword 'clone'). It is OK as long as the fields are primitive variables.
    $my_object = clone $data;
    $my_object->perhitunganrank=($my_object->freq*$my_object->totalbeli)-($my_object->jarak*$cost*$my_object->freq);
    array_push($my_array, $my_object);
}

// Now we need to create a function that compares two objects and tells us which one comes first between the two. 
// This function will then be used by usort.
// The function compares using the property perhitunganrank. 

function cmp($a, $b){
    return strcmp($a->perhitunganrank, $b->perhitunganrank);
}

// now we sort based on the function above
usort($my_array, "cmp");

//Finally we print the array of objects which is now sorted.
foreach($my_array as $row_to_print){
    $counter =1;
    echo "<tr>";
    echo "<td>".$row_to_print->nama_jalan." no ".$row_to_print->no_rumah.", ".$row_to_print->kelurahan.", ".$row_to_print->kecamatan.", ".$row_to_print->kota."   </td>";
    echo "<td>".$row_to_print->jarak."</td>";
    echo "<td>".$row_to_print->frekuensi."</td>";
    echo '<td>'.$row_to_print->perhitunganrank.'</td>';
    echo '<td>'.$counter.'</td>';
    echo"</tr>";
    $counter++;
}