0

I found on the PHP documentation the function "array_multisort" that is meant to sort an array "of columns". They provide one example where the user has an array of rows and then the user has to transform this array of rows into an array of columns. In my case the array is already set by columns, such as:

tablearray 

{

['Employee_ID'] = {0 => row1, 1 => row2, 2 => row3, 3 => row4}

['First_Name'] = {0 => row1, 1 => row2, 2 => row3, 3 => row4}

['LastName'] = {0 => row1, 1 => row2, 2 => row3, 3 =>row4}

 }

I want to sort by Employee_ID and I need all the other columns to follow the same order. I tried:

   array_multisort($tablearray['Employee_ID'], SORT_ASC);

But it only sorts the first column (which becomes a mess). The array has more than 10 columns and it changes the column names depending on the search (the columns names are its keys). On PHP's documentation for this function, the example provided shows that the after transforming the rows array into a columns array, we should use the original array as a third parameter to match the keys - I don't have the "original" array to do the match since I didn't transform anything.

Thank you.


Desired output, as suggested by one user:

Original:

array
{

['Employee_ID'] = (1002, 4508, 0002, 1112)
['Business_Unit'] = ('UER', 'ABC', 'XYZ', 'EER')
['LastName'] = ('Smith', 'Vicente', 'Simpson', 'Thompson')

}

Sorted by Employee ID:

array
{

['Employee_ID'] = (0002, 1002, 1112, 4508)
['Business_Unit'] = ('XYZ', 'UER', 'EER', 'ABC')
['LastName'] = ('Simpson','Smith', 'Thompson', 'Vicente')

}

-- My original array is a database query output:

Array
 (
[0] => Array
    (
        [Employee_ID] => 0000
        [Supervisor_ID] => 00000
        [Location_Descr] => somewhere
        [Start_Date] => 06/03/2002
        [Service_Date] => 06/03/2002
        [Rehire_Date] => 00/00/00
        [Business_Unit] => YYYY
        [Job_Title] => Manager
        [Email] => [email protected]
        [Dept_Desc] => bla bla bla
        [Employee_Name_LF] => Last, First
        [Supervisor_Name_LF] => Last, First
        [Term_Date] => 00/00/00
        [Preferred_Name] => Someone
        [Source] => Sheet2
    )
)

There a several more rows. The main purpose is to show the results as an HTML table and to generate a CSV file. I already made those functions using the modified structure (the first that I posted). I thought it would be easier to deal with that structure... Indeed it was, but not for sorting unfortunately.

The array_multisort documentation (http://php.net/manual/en/function.array-multisort.php) suggests separating each column as an individual array.. However, as you can see I have several columns (and the user can select more or less to be shown before performing the query.. So I can't just list all of them on the statement).

I a willing to change everything just to make the code better to be worked with.

9
  • I'd start from saying that your table is badly designed. If you can redesign the table, that would make more sense and simplify dealing with in future Commented Jul 16, 2015 at 18:02
  • sort how? You need to show your expected output... Commented Jul 16, 2015 at 18:02
  • There are some user-defined sorting methods (uasort is probably what you want). Where you define the function that sorts. Commented Jul 16, 2015 at 18:08
  • @MarcB I just added some lines showing the desired output. Commented Jul 16, 2015 at 18:13
  • 1
    each of those is its own array, why can't you just foreach($main as $key => $subarray) { sort($main[$key]); }? Commented Jul 16, 2015 at 18:13

3 Answers 3

1

Ugly - would be a lot easier if you formatted the input tables.

$arr = array(
  'Employee_ID' => array('1002', '4508', '0002', '1112'),
  'Business_Unit' => array('UER', 'ABC', 'XYZ', 'EER'),
  'LastName' => array('Smith', 'Vicente', 'Simpson', 'Thompson')
);
$employees = array();
foreach (range(0, sizeof($arr[current(array_keys($arr))]) - 1) as $k) {
  $emp = array();
  foreach ($arr as $col => $vals) {
    $emp[$col] = $arr[$col][$k];
  }
  $employees[] = $emp;
}
$sort = array();
foreach ($employees as $k => $v) {
  $sort[$k] = $v['Employee_ID'];
}
array_multisort($sort, SORT_ASC, $employees);
print_r($employees);

And to put back in the original format:

$arr_sorted = array();
foreach (array_keys($arr) as $col) {
  $arr_sorted[$col] = array();
  foreach ($employees as $emp) {
    $arr_sorted[$col][] = $emp[$col];
  }
}
print_r($arr_sorted);
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you! However, I followed the tips given by the other uses I will format the output to generate a better array structure. I just edited the question actually - I still haven't figured how I am going to do it...
start at $sort = array() ... that should work with your database output
1

Thank you for posting the extra details in your question, as they did help in understanding the intent of your question.
Now, you didn't tell us how that table should look; If you want the employees one per row, or one per column. Which is kind of crucial to know. Normally one would have one employee per line, especially if this is to be exported to CVS. However, I have a suspicion that it's the latter you want. Otherwise you've gone about this in a very overly complicated manner.

Point in case: Normal one-per-row layout:

<?php
$db = new PDO();

// Defining the fields we need here, to avoid having too long a string for the query.
$fields = "e.employee_id, e.first_name, e.lastname, u.business_unit, s.email";

// Do the sorting in the database itself. Not only is this faster, but it
// is also a lot easier to sort it exactly as you'd like.
// Note that I don't use prepared statements here, as there is no user-input.
$query = <<<outSQL
SELECT {$Fields} FROM `unit` AS u
INNER JOIN `employee` AS e ON e.employee_id = u.unit_id
INNER JOIN `employee` AS s ON s.employee_id = u.supervisor_id
ORDER BY e.`employee_id`
outSQL;

$data = $db->query($query);

// Creating a printf() template for the output, to make the code easier to maintain.
$rowTemplate = <<<outHTML
        <tr>
            <td>%1\$d</td>
            <td>%2\$s</td>
            <td>%3\$s</td>
        </tr>

outHTML;

// Generate the table template, using placeholders for where the data will be added..
$tableTemplate = <<<outHTML
<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>First name</th>
            <th>Last name</th>
        </tr>
    </thead>
    <tbody>
%s
    </tbody>
</table>
outHTML;

// Regular table output, one employee per line.
$temp = '';
foreach ($data as $e) {
    // hs() is a shortcut function to htmlspecialchars (), to prevent against XSS.
    $temp .= sprintf($rowTemplate, $e['employee_id'], hs($e['first_name']), hs($e['lastname']));
}

// Add the rows to the table, so that you can echo the completed product wherever you need.
$employeeTable = sprintf($tableTemplate, $temp);

If you want to do it one per column, it becomes a bit more intricate. Though, still a bit easier than your first attempt. :)
Namely, something like this:

<?php
$db = new PDO();

// Defining the fields we need here, to avoid having too long a string for the query.
$fields = "employee_id, first_name, lastname";

// Do the sorting in the database itself. Not only is this faster, but it
// is also a lot easier to sort it exactly as you'd like.
// Note that I don't use prepared statements here, as there is no user-input.
$data = $db->query("SELECT {$Fields} FROM `employees` ORDER BY `employee_id`");

// We need to know how many columns we'll have. One per employee.
$columns = count ($data);

// Rows have a header in front of each line, and one td tag for each employee.
$rowTemplate = "\t\t<th>%s</th>\n".str_repeat("\t\t\t<td>%s</td>\n", $columns);

// Generate the table template, using placeholders for where the data will be added..
$tableTemplate = <<<outHTML
<table>
    <tbody>
%s
    </tbody>
</table>
outHTML;

// Reformat the array to give us the data per-column.
$temp = array ();
foreach ($data as $field => $e) {
    // Since we've already sorted the data in the database we don't need to do any further sorting here.
    // Also note that I'm doing the escaping here, seeing as this array will only be used for output.
    $temp['Employee ID'][] = intval($e['employee_id']);
    $temp['First name'][] = hs($e['first_name']);
    $temp['Last name'][] = hs($e['lastname']);
}

// Now we do the same as in the above example.
$rows = '';
foreach ($temp as $label => $l) {
    // We have the label as the first template variable to be added, so put it as the first element.
    array_unshift($l, $label);

    // Add the current row of items to the output, using the previously established template.
    $rows = vprintf($rowTemplate, $l);
}

// Add the rows to the table, so that you can echo the completed product wherever you need.
$employeeTable = sprintf($tableTemplate, $temp);

PS: Haven't tested the code, but it should work.

4 Comments

Nice! Thanks a lot for the answer! However, it wouldn't be the best solution to me for two reasons: 1- I am querying the data from two separate tables and then I merge the two result arrays.. The tables have columns with different names that I have change in the array as well as creating column with blank rows for the columns that don't match. I am using CI to build the query since there's more than 10 columns in the table... 2- The user selects on the form which columns he wants for output, those values are stored in an array. That's why CI turns to he very helpful but constraints my sql
I found a similar way to make my code work and sorting afterwards the array. I followed the other answer given to the thread. I will post my own answer later with the final code for the code :)... One thing that I forgot to mention, the data come from different tables but some rows may match having the same Employee_ID and I need to show those rows near each other, that's why sorting after is more useful. Actually, by default those tables come sorted by Employee_ID, but when I merge them it puts all values from one and then all values from other.
When querying data from multiple tables, and those data has a relationship, it is recommended to use JOINs. I assume they have an relationship, since they're supposed to be shown in the same table and being selected from the same form. I'm pretty sure that you've painted yourself more and more into a corner on this one, and not quite realized it until you hit the problem in the original question. That's why I recommend refactoring your code and logic, so that you don't have to do all of these complicated workarounds to solve what is actually a rather simple problem.
@Leandro I've now updated my answer above to show how one would query data from multiple tables, and have it sorted as you prefer. If you want to add further fields to sort by, just add them as described in the manual for the database system you're using.
0

I ran into his problem and after much angst found a really nice solution in the notes on the php manual page - I now have the following function which i use whenever I need to solve this type of problem.

function fnArrayOrderby(){
//function to sort a database type array of rows by the values in one or more column
//source http://php.net/manual/en/function.array-multisort.php - user notes
//example of use -> $sorted = fnArrayOrderby($data, 'volume', SORT_DESC, 'edition', SORT_ASC);

$args = func_get_args(); //Gets an array of the function's argument list (which can vary in length)
//echo "sorting  ".$args[0]."<br>";
if (!isset($args[0])) { return;}
$data = array_shift($args); //Shift an element off the beginning of array
foreach ($args as $n => $field) {
if (is_string($field)) {
$tmp = array();
foreach ($data as $key => $row)
$tmp[$key] = $row[$field];
$args[$n] = $tmp;
}
}
$args[] = &$data;
call_user_func_array('array_multisort', $args);
return array_pop($args);
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.