SQL Hacks: Sequentially Numbering Records

Mon, Dec 11, 2006

Tech Tips

It is a simple concept, I want each record of a SQL result set to be numbered 1 through X. Well SQL wasn't really designed to do this so here come some the ugliest hacks you'll every see to "just get it done".

Here is some more background. I got contracted to write some fixes for an existing ecommerce web site written in PHP/MySQL. One of those fixes was a bug that occurred when trying to sort the store's products in the admin interface. Each product had a sort order that could be manually adjusted using these up and down arrows. Common approach, but it wasn't implemented well. I'm not going to go into any more details, but the simple fix was to create a function that could reset the sort when needed.

I wanted a quick way to regenerate the sort order numbers based on the SQL result set. I did some googling and found this link pertaining to Microsoft SQL Server.:
Database Journal Article

These methods could be modified to work with MySQL but I needed something more elegant. I found my solution in the least likely of places, the manual.

Displaying Sequential Numbers in MySQL:

SQL:
  1. SET @rnk=0;
  2. SELECT id, @rnk:=@rnk+1 AS product_order
  3. FROM products ORDER BY product_name;

Now for the let down. It won't work in PHP 4/MySQL. This is typical when working in LAMP. You develop code locally, go to move it to the client's machine and they have minor versions differences and your code doesn't work. The solution above requires two SQL statements and the PHP MySQL library does not allow the running of multiple queries at the same time (to prevent SQL injection attacks). Supposedly it will work in PHP 5/MySQL with the mysqli_multi_query but I didn't have time to test.

Since excuses don't pay the bills, I had to resort to an ugly hack (as opposed to elegant hack). I didn't really like the methods in the Database Journal link above so I made my own:

Displaying Sequential numbers in MySQL/PHP4:
The basic idea behind this one is to use a seperate table to update the sort order.
1. Create a Sort Table

SQL:
  1. CREATE TABLE `sort_order` (
  2.   `id` int(11) NOT NULL DEFAULT '0',
  3.   `order` int(11) NOT NULL AUTO_INCREMENT,
  4.   PRIMARY KEY  (`order`)
  5. )

2. Run the SQL that will update the sort

SQL:
  1. #first clear the table
  2. TRUNCATE TABLE sort_order;
  3.  
  4. #reset the auto increment
  5. ALTER TABLE sort_order AUTO_INCREMENT=1;
  6.  
  7. #insert the id's of the records sorted the way you want
  8. INSERT INTO sort_order (id)
  9. SELECT id FROM products ORDER BY product_order;
  10.  
  11. #update the sort order field
  12. UPDATE products p
  13. INNER JOIN sort_order so ON p.id = so.id
  14. SET p.product_order = so.ORDER;

This was a quick and dirty hack but it worked for the situation. For multi-user use, I would put the statements in a transaction.

Let me know if you have any questions or come up with anything else.

Bookmark and Share
,

2 Responses to “SQL Hacks: Sequentially Numbering Records”

  1. artebranoni Says:

    [url=http://vzqajvl.my3gb.com/3/08-201060.html]?????????? ??? ????? ? ???????? [/url][url=http://ublaxji.my3gb.com/3/879.html]????? ????????? ? ????? [/url][url=http://kdkdzkz.my3gb.com/znakomstva-s-russkimi-za-granitsey/znakomstva-v-achinske.html]?????????? ? ??????? [/url]
    [IMG]http://farm1.static.flickr.com/77/162366982_c1c326a2a4.jpg[/IMG][IMG]http://farm1.static.flickr.com/52/127372236_8d06715d39.jpg[/IMG][IMG]http://farm1.static.flickr.com/58/195969299_f289ea553d.jpg[/IMG][IMG]http://farm1.static.flickr.com/64/152954687_0e408077f0.jpg[/IMG][IMG]http://farm1.static.flickr.com/53/115178600_9df6b6a286.jpg[/IMG]
    ??? ???????, ??? ? ????? ??? ??????.????? ????? ???-??? ???????.????, ? ?? ??????? ??????????? ?? ????????.
    ?????! ?????? ????? ???????.??????? ?? ?? ??????????.?? ????? ??? ??? ???? ???????!????? ?????????? ?????, ?????.??, ? ????? ??? ???? ?? ???????????????, ??? ????? ????????? ??? ?????.??? ???????, ??? ??????? ??? ?? ??, ??? ?? ???????, ????
    ??????, ? ?????. ? ?????. [url=http://byebqbb.my3gb.com/96/znakomstva-g-klin.html]?????????? ? ???? [/url][url=http://pajelgq.my3gb.com/514/9316.html]?????????? ? ??????? [/url][url=http://fpigeav.my3gb.com/05-20108/onlayn-devushki-znakomstva.html]?????? ??????? ?????????? [/url][url=http://qcupntm.my3gb.com/5/77.html]Www o love [/url][url=http://rhgynkn.my3gb.com/08-06-20102/01-20102.html]????? ?????? [/url]
    [url=http://wjzbuvy.my3gb.com/462/8783.html]??????? ????? ????????? [/url][url=http://lyzopms.my3gb.com/znakomstva-g-kamishina/poznakomitsya-s-evreem.html]????????????? ? ?????? [/url]
    [url=http://gjkndym.my3gb.com/339/]????? ?? ?????? ?????????[/url][url=http://uwkgbfl.my3gb.com/27-12-20109/3649.html]?????????? ? ????????? [/url]
    [url=http://rnjjjoj.my3gb.com/31-05-201017/2470.html]?????? ??????? ?????????? [/url][url=http://oxjzyqx.my3gb.com/03-201012/7660.html]?????????? ? ???????? [/url]
    [url=http://kabfxpu.my3gb.com/06-201012/sayt-znakomstv-dlya-vich-infitsirovannih.html]???? ????????? ??? ??? ?????????????? [/url][url=http://kwkrwgj.my3gb.com/10/26-11-201083.html]?????????? ???????????? ??????? [/url]

    [url=http://kjxzxdh.my3gb.com/13/sayt-znakomstv-meno-mamba.html]???? ????????? meno ????? [/url]
    [url=http://gqyuivl.my3gb.com/8/sluzhba-znakomstv-v-krasnodare.html]?????? ????????? ? ?????????? [/url]

    [url=http://owezdiu.my3gb.com/622/sayt-znakomstv-v-v-novgorode.html]???? ????????? ? ? ????????? [/url][url=http://vzqajvl.my3gb.com/7/seks-za-dengi-volgograd.html]???? ?? ?????? ????????? [/url][url=http://idnlxad.my3gb.com/5914/19-04-201025.html]???? ????????? ? ???????? [/url]
    [url=http://oxjzyqx.my3gb.com/11/09-12-201083.html]???? ?? ?????? online [/url]
    ???? ??? ? ???? ??????? ??????, ??? ??????? ????????? ? ????.?? ??????? ????? ???? ?????, ?? ? ???? ?? ??????????.? ???? ??? ?????????? ?????? ?? ??????, ????? ???????..??? ??? ????????, ????? ????? ?????? ?????? ??? ?? ???? ??? ??? ??????????.??? ?? ?? ?????? ?????? ?????? ??? ???? ??, ???? ???????, ? ????????! [url=http://rouacez.my3gb.com/21-12-201011/23-02-201011.html]?????????? ? ????? ?????? [/url][url=http://ilvcddn.my3gb.com/09-201013/12-03-201019.html]????? ?????????? ? ???????????? [/url][url=http://lmiulez.my3gb.com/11-20107/67.html]Www love lg ua [/url][url=http://dsxmdrc.my3gb.com/03-07-20103/]?????????? ? ??????[/url][url=http://ilvcddn.my3gb.com/16/10-201078.html]?????????? ? ????????? ????????? [/url]
    ?? ???????? ??? ??? ????????? ? ???, ??? ? ?? ???? ?????? ??? ????.?? ?? ???? ??????? ????????????-????????” 70-?? ????, ??????????? ???????? ?? ?????.? ???? ?????????? ????????, ???? ???? ??? ???????? ?? ???????. [url=http://fyoeali.my3gb.com/422/intim-znakomstva-v-ulyanovske.html]????? ?????????? ? ?????????? [/url][url=http://ttsbjmm.my3gb.com/frazi-na-saytah-znakomstv/29-11-201080.html]Wap ???? ????????? [/url][url=http://cwtdawt.my3gb.com/9/08-201065.html]???? ????????? ???? [/url][url=http://fyoeali.my3gb.com/06-201016/sayt-znakomstv-meyl.html]???? ????????? ???? [/url][url=http://mdzzmka.my3gb.com/15/]? ?????????? ??????????[/url][url=http://amggyic.my3gb.com/znakomstva-s-russkimi-za-granitsey/04-11-201078.html]???? ????????????? ? ???? [/url]

  2. JerDyecyeluch Says:

    Who and where to order this summer on festival, portion your information.