[SOLVED] Auto Increment updating...

[BlackKite]

Hi,

 

I have a database with a table 'pages' in it. Inside pages is the row 'ID_MAIN'. It's got 5 entries in it with values of 1 to 5. I want to create a new entry in between entry 2 and 3 with a value of 3, there by updating 3 to 4 and so on.

 

This is the script I came up with, however I'm completely new with this and... well it didn't work. And info as to how to correct this would be greatly welcomed.

 

Script:

$name = $_GET['name'];
$ID_CAT = $_GET['ID_CAT'];
$navi = $_GET['navi'];
$body = $_GET['body'];

$ID_MAIN = $navi + 1;
$Update = ">=$ID_MAIN";

mysql_query("UPDATE 'pages' SET 'ID_MAIN'='+1' WHERE 'ID_MAIN'='$Update'") 
or die(mysql_error());  

mysql_query("INSERT INTO pages 
(name, ID_CAT, ID_MAIN, body) VALUES('$name', '$ID_CAT', '$ID_MAIN', '$body' ) ") 
or die(mysql_error());

 

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''pages' SET 'ID_MAIN'='+1' WHERE 'ID_MAIN'='>=2'' at line 1

[teng84]

try

"UPDATE pages SET ID_MAIN='1' WHERE ID_MAIN='".$Update."'"

and what is +1

[BlackKite]

Thanks but it didn't work, I got a duplicate entry error.

 

The +1 is "add one" because I'm trying to add 1 to every number after the number I'm updating so I don't get that duplicate entry error.

[teng84]

try the +1 to be assigned in a variable i dont know if it will work on the select "+1"

[BlackKite]

:-\ Still received the same duplicate entry error.

[BlackKite]

bump

[pro_se]

Try: UPDATE 'pages' SET 'ID_MAIN'='ID_MAIN++' WHERE 'ID_MAIN'='$Update'

[BlackKite]

Still no luck. =[

[per1os]

Ok let's hold the horses here.

 

Why not use an auto_increment field for the table via MySQL? You do know that is an option right?

 

<?php
$name = $_GET['name'];
$ID_CAT = $_GET['ID_CAT'];
$navi = $_GET['navi'];
$body = $_GET['body'];

$ID_MAIN = $navi + 1;
$Update = ">=$ID_MAIN";

// Note that COLUMNS CANNOT HAVE single quotes around them, they need backticks or NOTHING (`)
// Also note that '+1' is taken literally. (ID_MAIN+1) would be the correct syntax.
// Below is proper syntax. 
mysql_query("UPDATE `pages` SET `ID_MAIN`= (ID_MAIN+1) WHERE `ID_MAIN` $Update") 
or die(mysql_error());  

mysql_query("INSERT INTO pages 
(name, ID_CAT, ID_MAIN, body) VALUES('$name', '$ID_CAT', '$ID_MAIN', '$body' ) ") 
or die(mysql_error());
?>

 

Now it would probably be better to use the auto_increment field on your main ID field instead of the above, but to each his own. Give your logic is correct/what you need the above should produce the desired results.

[BlackKite]

Well, that worked, but only to a point. It was ok with the fact I was adding a new number 2, but threw a fit when trying to make 2 into 3, because there was also already a number 3. If that makes sense?

 

Also, the field is set to auto_increment. I had that from the start, but it was throwing a duplicate entry error, so thats why I came up with all this crap.

[per1os]

Well, that worked, but only to a point. It was ok with the fact I was adding a new number 2, but threw a fit when trying to make 2 into 3, because there was also already a number 3. If that makes sense?

 

Also, the field is set to auto_increment. I had that from the start, but it was throwing a duplicate entry error, so thats why I came up with all this crap.

 

Auto_increment fields should be left null and not assigned a value.

<?php
$name = $_GET['name'];
$ID_CAT = $_GET['ID_CAT'];
$navi = $_GET['navi'];
$body = $_GET['body'];

mysql_query("INSERT INTO pages 
(name, ID_CAT, body) VALUES('$name', '$ID_CAT', '$body' ) ") 
or die(mysql_error());

$new_id = mysql_insert_id(); // grabs the new id assigned to what was just entered in
?>

 

That will take care of your duplication problem. As the code you have is prone to fail as it is not being looped to modify every id in the table (which you do not want it to do)

 

[BlackKite]

Yeah but I do want it to loop then. That code wouldn't work for what I'm doing because I want the new entry to be inserted in a specific spot, IE between 2 and 3. That would just add it to the end and make an entry of ID 6. Correct me if I'm misunderstanding something. ???

[per1os]

Why do you want it at a specific spot between 2 and 3. You do know that with the primary key you can only have one, 2 or one three. Your logic does not make any sense at all bud. You basically just want to overwrite the data, for that just use UPDATE keyword, no need to insert. Unless I am not understanding correctly.

[trq]

I want to create a new entry in between entry 2 and 3 with a value of 3, there by updating 3 to 4 and so on.

 

If you want to do that you can't use an auto incrementing field. You'll need to create another field to order however you please, but leave the primary key as auto incrementing. I think you missunderstand how databases store data. They are unordered. Meening, you cannot specify where records will be inserted. Using the method Ive suggested though you can easily work around this.

[BlackKite]

Hm, your right, I probably don't have as much of a grasp on this as I should.

 

Let me just clarify what exactly I'm doing though, and why I am thinking the way I am. I'm making a content management system. This part here is whats creating the new entries in the database to be called as pages on the site. The part I'm having trouble with is the part that is used in creating the navigation list. I'm using ID_MAIN to call upon and also order the list. Being that it has to be in a stated order and it is a list where the user defines the placement of the new link, that is why I need to be able to insert a number into a specific place.

 

Does that make it more clear as to what I'm doing now? Hopefully.

[per1os]

I wouldn't use the id_main for ordering, create a new column called order or rank to order the data by (As Thorpe somewhat suggested)

 

From there you can easily change that field with a looped update statement to do what you want. But the update statement has to start with the last rank and go to the inserted rank to work.

[BlackKite]

Ok, i'll give it a try.

[per1os]

Ok, i'll give it a try.

 

Another way of doing it, depending on how many pages you plan to have is by making the rank/order column go by 10's or 100's for each entry.

 

So say you insert a page with a rank of 10. If you want to insert a page before it you can make that page 5 which in return leaves it open to have another page inserted before that or after that etc.

 

10 for a smal page system or 100 for a larger scale one. Just another option/idea.

[BlackKite]

Hm, I ran into another problem. It's now throwing a syntax error at the INSERT INTO area. Not sure what is wrong with my syntax, looks fine to me, but you all know better than I do.

 

Current Code:

<?php
include("../include/session.php");
if($session->logged_in){

$name = $_GET['name'];
$ID_CAT = $_GET['ID_CAT'];
$navi = $_GET['navi'];
$body = $_GET['body'];

$order = $navi + 1;
$Update = ">=$ID_MAIN";

$query = "SELECT * FROM `pages`"; 
mysql_query($query) or die(mysql_error());

while ( `order` >=$order ) {
mysql_query("UPDATE `pages` SET `order`= (order+1) WHERE `order` $Update") 
or die(mysql_error());  
}

mysql_query("INSERT INTO `pages` 
(name, ID_CAT, order, body) VALUES(`$name`, `$ID_CAT`, `$order`, `$body`) ") 
or die(mysql_error());

echo "Your page has been successfully uploaded into the database and will now appear on the site."; 

}
?>

 

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order, body) VALUES(`New Page One`, `1`, `3`, `This is the first page created wi' at line 2

 

Please and thanks. :-\

[BlackKite]

bump

[per1os]

You don't use back ticks for values you use single quotes, backticks only go around the column and table name:

 

(`name`, `ID_CAT`, `order`, `body`) VALUES('$name', '$ID_CAT', '$order', '$body') ") 

[BlackKite]

Woo! It's working, oddly, but its working. I had a few more syntax errors and my loop was a little off but I managed to sort it out. I have one more question though. I'm inserting in a new `order` with a value of 3. Therefore it should be updating the old 3 to 4, 4 to 5, and 5 to 6. However, its decided now that it's going to update the old 3 to 8, and therefore 4 to 9 and 5 to 10. Any ideas?

 

<?php
include("../include/session.php");
if($session->logged_in){

$name = $_GET['name'];
$ID_CAT = $_GET['ID_CAT'];
$navi = $_GET['navi'];
$body = $_GET['body'];

$order = $navi + 1;
$Update = ">=$order";

$query = "SELECT `order` FROM `pages`"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
mysql_query("UPDATE `pages` SET `order`= (`order`+ 1) WHERE `order` $Update") 
or die(mysql_error());  
}

mysql_query("INSERT INTO `pages` 
(`name`, `ID_CAT`, `order`, `body`) VALUES('$name', '$ID_CAT', '$order', '$body')")  
or die(mysql_error());

echo "Your page has been successfully uploaded into the database and will now appear on the site."; 

}
?>

[sasa]

try

<?php
include("../include/session.php");
if($session->logged_in){

$name = $_GET['name'];
$ID_CAT = $_GET['ID_CAT'];
$navi = $_GET['navi'];
$body = $_GET['body'];

$order = $navi + 1;
$Update = ">=$order";

//$query = "SELECT `order` FROM `pages`"; 
//$result = mysql_query($query) or die(mysql_error());

//while($row = mysql_fetch_array($result)){
mysql_query("UPDATE `pages` SET `order`= (`order`+ 1) WHERE `order` $Update") 
or die(mysql_error());  
//}

mysql_query("INSERT INTO `pages` 
(`name`, `ID_CAT`, `order`, `body`) VALUES('$name', '$ID_CAT', '$order', '$body')")  
or die(mysql_error());

echo "Your page has been successfully uploaded into the database and will now appear on the site."; 

}
?>

[BlackKite]

Ah hah, it works! Thank you all for the help. Ended up learning a lot from this thing.