Sort on array inside mysql_fetch_array

Question

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?

**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Oct 24 '19 at 19:03
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 24 '19 at 19:04

score 1 · Accepted Answer · answered Feb 27 '12 at 16:30

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.

Jovan Perovic · Answer 2 · 2012-02-27T16:32:21.043

0

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...

edited Feb 27 '12 at 16:32

answered Feb 27 '12 at 16:25

Jovan Perovic

19,846
5
44
85

The totals wich I want to sort on are results that don't get from the database... – Leon van der Veen Feb 27 '12 at 16:27
Well in that case this should do it :) I have edited my answer to include another example... – Jovan Perovic Feb 27 '12 at 16:30
I can't see how this answer solves the problem? The value to sort by is the number of files on the system, that belong to a certain company. Only the company information is retrieved from the database. The file count is retrieved afterwards. I can't see any strings being relevant for sorting here. – Till Helge Feb 27 '12 at 16:34
@TillHelgeHelwig My assumption was wrong, but I didn't want to edit answer as it might be useful anyways... The point is explanation of `usort` functioning... – Jovan Perovic Feb 27 '12 at 16:37
I can see how in general this might be helpful, but in this case the obvious problem is that the OP did not yet come to the conclusion that using an array to cache all values migth be the solution. So while in general helpful, he basically could have got the same answer by reading the PHP documentation. – Till Helge Feb 27 '12 at 16:40

Sort on array inside mysql_fetch_array

2 Answers2