title: User defined ordering using MySQL and PHP
body: <p>This is more of a methodological question. Say I have a table</p>
  
  <pre><code>id int auto_increment primary key,
  name text not null
  </code></pre>
  
  <p>I have a number of entries which I want to order in some arbitrary way. I could create an interface that allows me to change the order of the names as they would appear on some page. When I read out the entries of the table, they would be ordered according to how I chose. I see three possible approaches. First approach is to add a field</p>
  
  <pre><code>order int not null
  </code></pre>
  
  <p>and when I changed the the order, I would have to update every row, or at least every row with ordering higher than the the lowest order I am changing. This seems like the wrong approach, as it would require doing SQL statements in a for loop. The second approach would be to create another table, linked by id</p>
  
  <pre><code>id int not null
  order int not null
  </code></pre>
  
  <p>but I would run into the same problem here. If I added a name and wanted to put them first, I would have to change the order entry in every row. I could see a possible third approach, which is to store some associations between id and order in a single column, or even in a flat file. I could see using JSON formatting to do this. </p>
  
  <p>My question is this. What is the best way to do this using MySQL and PHP.</p>