CODEWITHSUNDEEP
phpmysqlarrays
Leon van der Veen
Leon van der Veen

Reputation: 1672

Sort on array inside mysql_fetch_array

I've a little PHP script that get results from my database. In the mysql_fetch_array() I count files on the server in a folder. I get the folder name of the MySQL results.

What I want is to build a list with all companynames and a total of all the files from the associated folder.

Well, so far so good. But the problem is I want to sort the list on the total of files.

My script look like this:

<? $query = "SELECT * FROM users WHERE id = '".$_COOKIE['users_id']."'"; $sql = mysql_query("$query");
while ($fill = mysql_fetch_array($sql)) { 
   $multi_user = $fill['multi_user'];
   $folder = $fill['folder'];
   $company_name = $fill['company_name'];

   $directory_purchase = '/var/www/vhosts/domain.nl/private/'.$folder.'/purchase/';
   $directory_sales = '/var/www/vhosts/domain.nl/private/'.$folder.'/sales/';
   $email_folder = '/var/www/vhosts/domain.nl/private/'.$folder.'/email/';
   $kas_folder = '/var/www/vhosts/domain.nl/private/'.$folder.'/bank/';
   $multi_folder = '/var/www/vhosts/domain.nl/private/'.$folder.'/multi/';

   $total_purchase = count(glob($directory_purchase."*.*"));
   $total_sales = count(glob($directory_sales."*.*"));
   $total_email = count(glob($email_folder."*.*"));
   $total_kas = count(glob($kas_folder."*.*"));
   $total_multi = count(glob($multi_folder."*.*"));

   $total = $total_purchase + $total_sales + $total_email + $total_kas + $total_multi;

   echo '<table>
     <tr>
         <td>'. $company_name. '</td>
         <td>'. $total .'</td>
     </tr>
   </table>';
}?>

Somebody know how to do this?

Upvotes: 0

Views: 4015

Answers (2)

Jovan Perovic
Jovan Perovic

Reputation: 20201

You should consider sorting data within MySql. If you really need to do it in PHP use usort function (user-sort):

http://www.php.net/manual/en/function.usort.php

usort functon must take two parameters (compared items) and returns -1, 0 or 1 if item1 is "less", "equal" or "greater" than item2 respectively.

Example:

$mydata = ... // some data

usort( $mydata, function($item1,$item2){
    // your sort logic
    return $item1['column1'] > $item2['columns1']; //simple numeric sort
});

Taking into account your example, you probably want to sort item by string values. It that case for sort logic use str_cmp to see which item is greater from another withing data array:

http://php.net/manual/en/function.strcmp.php

Example:

usort( $mydata, function($item1,$item2){
    // your sort logic
    return str_cmp($item1['folder'], $item2['folder']); //string sort
});

Hope this helps...

Upvotes: 0

Till Helge
Till Helge

Reputation: 9331

Well...just create an array and fill it during the loop. Then sort the array and loop again. Additionally you need to cache the company names:

<?
$totals = array();
$companies = array();
$query = "SELECT * FROM users WHERE id = '".$_COOKIE['users_id']."'";
$sql = mysql_query("$query");
while ($fill = mysql_fetch_array($sql)) { 
   // all the other stuff

   $total = $total_purchase + $total_sales + $total_email + $total_kas + $total_multi;
   $totals[$fill['id']] = $total;
   $companies[$fill['id']] = $fill['company_name']; 
}
asort($totals);
foreach ($totals as $company => $total) {
    echo $companies[$company] . ': ' . $total . '<br />';
}
?>

Obviously you also could put all information into one single array and use usort(), but in my opionion that's a little overkill.

Upvotes: 1

Related Questions