Insert Sort according to a column in 2x2 array

Question

I'm trying to print names of movie and music in DESC order according to the datetime of the rows. So I need some algorithms help in inserting into an array according and have it ordered.

The database:

               Movie                                Music
----------------------------------  ---------------------------------------
|   title  |     row_insert_dt   |  |    title      |     row_insert_dt   |
----------------------------------  ---------------------------------------
| Iron Man | 2019-08-21 08:56:00 |  | Lose Yourself | 2019-08-21 08:56:01 |
|   Thor   | 2019-08-21 08:56:02 |  |   Rap God     | 2019-08-21 08:56:03 |
|   ...    |         ...         |  |     ...       |         ...         |
|   ...    |         ...         |  |     ...       |         ...         |

The code:

<?php
  $output = array();

  $connection = new PDO("mysql:host=localhost;charset=UTF8;dbname=test;", "root", "");

  // Movie
  $statement = $connection->prepare("SELECT title, row_insert_dt FROM movie ORDER BY row_insert_dt DESC;");
  $statement->execute();
  foreach($statement->fetchAll() as $y) {
    array_push($output, array("title" => $y["title"], "date" => $y["row_insert_dt"]));
  }

  // Music
  $statement = $connection->prepare("SELECT title, row_insert_dt FROM music ORDER BY row_insert_dt DESC;");
  $statement->execute();
  foreach($statement->fetchAll() as $y) {
    // How to Insert into '$output' in sorted order.
  }

  echo json_encode($output);
?>

My desirable output:

[
  ["Rap God",       "2019-08-21 08:56:03"],
  ["Thor",          "2019-08-21 08:56:02"],
  ["Lose Yourself", "2019-08-21 08:56:01"],
  ["Iron Man",      "2019-08-21 08:56:00"]
]

Give us some table stucture, valid input sample and expected output, since you told us that this can be done with union — Cid, Sep 02 '19 at 07:50
@AmandeepSingh I still don't see something that can't be done with union — Cid, Sep 02 '19 at 08:28

nacho · Accepted Answer · 2019-09-02T07:58:39.213

Instead of doing it over your PHP code, you can do it with just one SQL query (with UNION). Try using this query:

SELECT *
FROM 
    (SELECT title, row_insert_dt 
     FROM movie 
     UNION 
     SELECT title, row_insert_dt FROM music) a
order by a.row_insert_dt,a.title DESC

EDIT If you need to do it over PHP, you can do it with rsort this way:

<?php
  $output = array();

  $connection = new PDO("mysql:host=localhost;charset=UTF8;dbname=test;", "root", "");

  // Movie
  $statement = $connection->prepare("SELECT title, row_insert_dt FROM movie ORDER BY row_insert_dt DESC;");
  $statement->execute();
  foreach($statement->fetchAll() as $y) {
    array_push($output, "date" => $y["row_insert_dt"]),array("title" => $y["title"]));
  }

  // Music
  $statement = $connection->prepare("SELECT title, row_insert_dt FROM music ORDER BY row_insert_dt DESC;");
  $statement->execute();
  foreach($statement->fetchAll() as $y) {
    array_push($output, "date" => $y["row_insert_dt"]),array("title" => $y["title"]));
  }
  rsort($output);

  echo json_encode($output);
?>

See that i did change the order of your columns, i put first the date (so the rsort uses it to sort) and after that the title.

Sorry I cannot do this in one query. this movie and music database is just for stackoverflow purpose. My original work cannot be done with 1 query — aman, Sep 02 '19 at 07:49
Then try using array_multisort in php https://www.php.net/manual/en/function.array-multisort.php — nacho, Sep 02 '19 at 07:53
@AmandeepSingh then you should probably rephrase the question, to reflect the situation you are actually in - instead of asking about one that is significantly different. _“My original work cannot be done with 1 query”_ - nothing of what you have shown so far gives an indication that would actually be true. Right now we can’t tell if your situation actually makes this impossible, or if you just lack the ability to properly use JOINs or UNION yet. — misorude, Sep 02 '19 at 08:19

score 0 · Answer 2 · answered Sep 02 '19 at 08:34

strong textIt is not the most performing solution, but certainly the simplest:

Merge arrays with array_merge
Sort arrays by column using usort

Snippet:

$column = 1;    // column name or key position for date column
$order = -1;     // 1 => asc, -1 => desc
$film = [
    ["Thor",          "2019-08-21 08:56:02"],
    ["Iron Man",      "2019-08-21 08:56:00"]
];
$music = [
    ["Lose Yourself", "2019-08-21 08:56:01"],
    ["Rap God",       "2019-08-21 08:56:03"]
];
$array = array_merge($film, $music);
usort($array, function($a, $b) use ($column, $order) {
    return (($a[$column] < $b[$column]) ? -1 : 1) * $order;
});
var_dump($array);    // array sorted

Result:

array (size=4)
  0 => 
    array (size=2)
      0 => string 'Rap God' (length=7)
      1 => string '2019-08-21 08:56:03' (length=19)
  1 => 
    array (size=2)
      0 => string 'Thor' (length=4)
      1 => string '2019-08-21 08:56:02' (length=19)
  2 => 
    array (size=2)
      0 => string 'Lose Yourself' (length=13)
      1 => string '2019-08-21 08:56:01' (length=19)
  3 => 
    array (size=2)
      0 => string 'Iron Man' (length=8)
      1 => string '2019-08-21 08:56:00' (length=19)

You can change column ($column) and sorting direction ($order).

Insert Sort according to a column in 2x2 array

2 Answers2