MAR
MAR

Reputation: 623

How can MySQL records be reordered by a certain column permanently?

I'll use an example for this.

Say I'm taking RSS feeds, going through their items, and storing some information about each item in a DB. I'm using SimplePie for processing the feeds, and Josh Campbell's MySQLi wrapper for the DB actions.

For each entry, I will save the item title, the time the item was published (retrieved from the feed), the time the item was added to my DB (defined at the time of inserting to DB), and an auto-incremented ID assigned to each.

function process_rss($rss_url) {
      require_once './feed-parser/vendor/autoload.php';
      $feed = new SimplePie();
      // some feed processing logic here
      $items = $feed->get_items();
      
      $data = array();
      foreach ($items as $item) {
            $data[] = array(
                  'name'      => $item->get_title(),
                  'published' => $item->get_date(),
                  'created'   => time()
            );
      }
      $insert = ... // db insert query with the $data array  
      if ($insert) {
            return 'Imported';
      } else {
            return 'Try again...';
      }
}

Now assume I ran this function 3 times with 3 different feeds, and a total of 7 items were processed and inserted to the DB. They are inserted at the order in which they were processed, so my table would appear as follows:

ID  | Name    |  Published    |  Created
----------------------------------------
1   | Track 1 |  1595147815   |  1594986945
2   | Track 2 |  1594996785   |  1594986945
3   | Track 3 |  1595146567   |  1594986945
4   | Track 4 |  1594996598   |  1594986945
5   | Track 5 |  1594996096   |  1594986945
6   | Track 6 |  1594986545   |  1594986945
7   | Track 7 |  1594986609   |  1594986945

What I want to do now, is to sort that table by the published column permanently (rather than sorting while fetching the entries from the DB).

Taking the above example, the desired output would look like this:

ID  | Name    |  Published    |  Created
----------------------------------------
6   | Track 6 |  1594986545   |  1594986945
7   | Track 7 |  1594986609   |  1594986945
5   | Track 5 |  1594996096   |  1594986945
4   | Track 4 |  1594996598   |  1594986945
2   | Track 2 |  1594996785   |  1594986945
3   | Track 3 |  1595146567   |  1594986945
1   | Track 1 |  1595147815   |  1594986945

Looking into the answers provided here, it is indicated that using ALTER and ORDER BY will not persist the changes upon new inserts. How would I go about achieving this?

Upvotes: 1

Views: 138

Answers (1)

O. Jones
O. Jones

Reputation: 108706

How can MySQL records be reordered by a certain column permanently?

You Can't Do That™. Seriously. And you will be sorry if you try.

Seriously, rows in SQL databases have no inherent order. If you do not specify ORDER BY on your query the server is free to return the set of rows you requested in any order it wants. If you get them in a particular order that somehow matches your expectation, it is a lucky coincidence.

As tables grow, SQL databases sometimes start using different ways of accessing them. And, without any ORDER BY specification, those different ways of accessing them may generate different orderings. So, if you need a particular order but you don't specify it, sometime in the life of your application the order will change. When you don't expect it to. And your users will suddenly be baffled by strange results.

Use ORDER BY.

Upvotes: 3

Related Questions