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:
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
-
span style="color: #ff0000;">`sort_order` (
-
`id`'0',
-
`order``order`)
-
)
2. Run the SQL that will update the sort
-
#first clear the table
-
#reset the auto increment
-
#insert the id's of the records sorted the way you want
-
#update the sort order field
-
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.
October 25th, 2011 at 10:08 pm
[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]
May 29th, 2012 at 8:06 am
Who and where to order this summer on festival, portion your information.