mysql not sorting properly, need help! Time sensitive!

[HaLo2FrEeEk]

I'm going to start by saying that I'm on a tight time schedule, so if anyone could please PLEASE help quickly, that would be awesome.

 

Ok, I've got a load balancer script that uses my database and php to let me enter different sources to a file (located on different servers) and choose whichever one has used the least bandwidth, to even out usage a little.  The balancer uses a link_id that is used to call up the file.  the link_id is a group that contains url_id's.  For one link_id, I can have as many url_id's as I need to split the download between.  Everytime the individual url is called up, the total filesize of that file is added to a total bandwidth usage, the url with the lowest bandwidth usage is used.  The thing is, when I insert like this:

 

INSERT INTO `forum`.`distrib` (
`link_id` ,
`url_id` ,
`url` ,
`filesize` ,
`bandwidth` 
)
VALUES (
'40', '1', 'url1', '122.10', '0.00'
), (
'40', '2', 'url2', '122.10', '0.00'
), (
'40', '3', 'url3', '122.10', '0.00'
);

 

Or even this:

 

INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '1', 'url1', '122.10', '0.00')
INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '2', 'url2', '122.10', '0.00')
INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '3', 'url3', '122.10', '0.00')

 

instead of showing as 1 2 3, it shows reversed in the database, 3 2 1, meaning that it shows url3 first.  now this shouldn't matter, but I want my file to be the one served up first, and when url_id 3 is first then it's served up first, even though it's #3.  I have no idea what's going on here.  I've used my database's insert function to put these in and I clearly wrote them inorder from 1 to 3, yet they show up from 3 to 1, reversed.  Is there anything I can do about this.  It's not a huge deal, but this needs to be perfect.  This has worked before today, yesterday I used a mass insertion script to insert 105 seperate links into the database, in groups of 3, 3 different sources for each file I'm serving up so I used a foreach loop to iterate through arrays containing the sources and the filenames.  It inserted them just fine, in order, and the url_id's show up in order from 1 to 3.  I didn't change anything with the insertion when I did different versions of the files (wmv and mov's), except changing the extension to mov.

 

PLEASE HELP!!!

[HaLo2FrEeEk]

Sorry for the double post, but I need to clarify a bit.

 

Here's what I did:

 

Used this query:

 

INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '', 'url1', '100', '0');

INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '', 'url2', '100', '0');

 

When I do this, it sorts right, url1 comes first becuase I ran it first, then url2.  I can manually edit the url_id's for each to 1 and 2 respectively, and they stay in order.

 

If I do this:

 

INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '1', 'url1', '100', '0');

INSERT INTO `distrib` (`link_id`, `url_id`, `url`, `filesize`, `bandwidth`) VALUES ('40', '2', 'url2', '100', '0');

 

It shows url2 first in the list, then url1.  I clearly inserted url1 first, so it should show up first.  I'm getting to the point where I'm pulling out my hair becuase I can't explain why it's doing this, it's driving me crazy.  I've even gone into phpMyAdmin's interface and used the insert function to manually enter everything, it still won't show up in order.  It's sorting by link_id and it goes:

 

link_id: 40, url_id: 2

link_id: 40, url_id: 1

 

Why, why would it do that?  It pulls url 2 first and I need it to pull url1 first, since url2 is the first in the list, it uses that.  It isn't supposed to.  Please help someone, I like my hair and I don't want to rip it all out.

[aschk]

You haven't posted your SELECT statement so I couldn't even possibly comment on the resultset that is being returned.... 

[luca200]

One basic concept of RDBMS is that the order in which a table shows its rows is not relevant, unless you use ORDER BY in your select statement.

 

Rows inside a table are NOT sorted. They are put on the physical support (e.g. hard disk) based on available space.

Making mass operations on a table, and updating its data instead of just inserting rows, can "shuffle" the table contents, but this is not a matter because, as it is stated, the order of rows in a table is not relevant. Use ORDER BY if you need some precise order.

[HaLo2FrEeEk]

I used this SELECT query:

 

SELECT * FROM `distrib` WHERE `link_id` > 4 GROUP BY `link_id` ORDER BY `link_id`

 

But when I'm using phpmyadmin I just use the default

 

SELECT * FROM `distrib`

 

And with all the wmv file url's, it inserted and sorts just fine.

 

The rows inside the table are sorted, becuase I inserted them like this:

 

link_id: 1 url_id: 1 url: url1

link_id: 1 url_id: 2 url: url2

...

 

In that order, so I put in url_id 1 first.  I'm ordering by link_id, and it goes, I have 3 url_id's per link_id, this is what it shows:

 

link_id: 1 url_id: 3 url: url3

link_id: 1 url_id: 2 url: url2

link_id: 1 url_id: 1 url: url1

 

And becuase I don't use the url_id for any reason other than sorting and keeping them in order, it pulls url3 first, even though it needs to pull url1 first.

 

I suppose in my distrib.php file where it does the math, when I pull each url in a link_id group, I could do this:

 

SELECT * FROM `distrib` WHERE `link_id` = $id ORDER BY `url_id`

 

And that would sort them from 1 to 3 for that individual link_id.  I'll see if it works.

 

EDIT: I should add that if I don't specify a url_id when inserting, they are shown in order of insertion in phpmyadmin.