[SOLVED] Incremental problems.

[WorldDrknss]

I am having some incremental problems.

Example script:

$query = $db->sql_query("SELECT order FROM main_links ORDER BY order DESC");
		$row = $db->sql_fetchrow($query);
		$MainOrder = $row['order'];
		$MainOrder++;
		$query = $db->sql_query("INSERT INTO main_links (title, link, order, sid, active) VALUES('$title', '$link', '$MainOrder', '$sid', '2')");

 

But the problem is that when the order reaches 10 every new insert will have the order of 10 instead of going 11, 12, 13 and so on.

 

It looks like this: 1,2,3,4,5,6,7,8,9,10,10,10,10,10

 

I have also tried:

if($order >= 10){
             $order = $order+1;
         }

But that was a no go.

 

I am also have problems with ordering by the order.

When order I get this: 1,10,10,10,10,10,2,3,4,5,6,7,8,9 instead if 1,2,3,4,5,6,7,8,9,10 and so on.

Thanks for any help.

[simcoweb]

Just a question, why wouldn't you just use an 'id' field in there that is auto-incremented?

[WorldDrknss]

Because an auto incremental field does not use the same number once it has been used. This is used for ordering navigational links and lets the administrators choice which link should be displayed where they choice so the order must always be 1-*.

 

Here is what I am trying to acheive.

But as you can see it repeats 10. Also when you delete a link, the ordering automatically gets adjusted to compensate for the deletion. 

[WorldDrknss]

*bump*

Please I really need to get this solved.

Thanks

[Jessica]

What is the datatype of the field?

[WorldDrknss]

It's a text field.

CREATE TABLE main_links (
`id` INT unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`link` text NOT NULL,
`order` text NOT NULL,
`sid` varchar(255) NOT NULL,
`active` TINYINT(1) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;

[Jessica]

did you try printing the query before running it to see if the problem is PHP or sql?

[WorldDrknss]

I echoed the three queries, weight and did not receive an error.

This is what I got:

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('Generators', 'Generators', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('Generators', 'modules.php?name=Generators', '1', '172', '2')
Weight = 1

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('affiliates', 'affiliates', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('affiliates', 'modules.php?name=affiliates', '2', '173', '2')
Weight = 2

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('content', 'content', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('content', 'modules.php?name=content', '3', '174', '2')
Weight = 3

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('downloads', 'downloads', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('downloads', 'modules.php?name=downloads', '4', '175', '2')
Weight = 4

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('guides', 'guides', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('guides', 'modules.php?name=guides', '5', '176', '2')
Weight = 5

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('links', 'links', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('links', 'modules.php?name=links', '6', '177', '2')
Weight = 6

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('privacy', 'privacy', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('privacy', 'modules.php?name=privacy', '7', '178', '2')
Weight = 7

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('radio', 'radio', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('radio', 'modules.php?name=radio', '8', '179', '2')
Weight = 8

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('search', 'search', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('search', 'modules.php?name=search', '9', '180', '2')
Weight = 9

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('staff', 'staff', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('staff', 'modules.php?name=staff', '10', '181', '2')
Weight = 10

Query2 = INSERT INTO modules (title, custom_title, active) VALUES('tou', 'tou', '0')
Query3 = SELECT weight FROM main_links ORDER BY weight DESC
Query4 = INSERT INTO main_links (title, link, weight, sid, active) VALUES('tou', 'modules.php?name=tou', '10', '182', '2')
Weight = 10

Once it reaches 10 it keeps repeating after that.

[hitman6003]

`order` text NOT NULL,

 

Why is order a text field?  that is why you are having a problem with your ORDER BY statement.

 

Change it to INT.

[WorldDrknss]

Thank you very much, that solved my issue.