Update field according to last entry

[Savizzaviz]

So I\'m running a mySQL database and using PHP to script an add/update form for the rows in my table, which I do like this:

[php:1:2a88340347]if ($submit) {

// If ID is present, update the current row

if ($id) {

$sql = \"UPDATE stories SET story_title=\'$title\',story_desc=\'$desc\',story_body=\'$body\' WHERE story_id=$id\";

// Else create a new one

} else {

$published = date(\"Y-m-d\");

$sql = \"INSERT INTO stories (story_title,story_pub,story_desc,story_body) VALUES (\'$title\',\'$published\',\'$desc\',\'$body\')\";

}

$result = mysql_query($sql);[/php:1:2a88340347]

Now what I want to do is make it so that when you create a new row, a story_numb field is automaticly updated to the next number up from the last one in the story_numb field according to the last story_id (auto_increment) number. So basicly generate the next highest number for the new story.

 

Anyone know how I could even do something similar that?

[DylanBlitz]

Okay, I have a 2am alcohol induced solution, lets see if it holds water hehe. Just do a select of the latest records number and subtract 1 from it. That will give you the last record with a good story number. Then take that story number and add 1 to it. Something like:

[php:1:1ebd38c1c9]
} else {
$published = date(\"Y-m-d\");
$sql = \"INSERT INTO stories (story_title,story_pub,story_desc,story_body) VALUES (\'$title\',\'$published\',\'$desc\',\'$body\')\";
$newstory = mysql_insert_id();
$laststory = $newstory - 1;
$getlast = \"SELECT story_numb FROM stories WHERE story_id = $laststory\";
$getlastnum = mysql_fetch_array($getlast);
$lastnum = $getlastnum[story_numb];
$newestnum = $lastnum + 1;
$updatenew = \"UPDATE stories SET story_numb = $newestnum WHERE story_id = $newstory\";
}
[/php:1:1ebd38c1c9]

On a last note, if you currently have any stories in there, you need to manually set their numbers, but it should work for all new php created stories.
?>[/code]

[Savizzaviz]

Heh... Well I can\'t seem to get it to work properly. I tried it your way and a few other ways, but the only thing that would even slightly work was:

[php:1:8a620cf238] } else {

$published = date(\"Y-m-d\");

$newstory = mysql_insert_id();

$laststory = $newstory - 1;

$getlast = \"SELECT story_numb FROM stories WHERE story_id = $laststory\";

$query = mysql_query($getlast);

$getlastnum = mysql_fetch_array($query);

$lastnum = $getlastnum[story_numb];

$number = $lastnum + 1;

$sql = \"INSERT INTO stories (story_numb,story_title,story_pub,story_desc,story_body) VALUES (\'$number\',\'$title\',\'$published\',\'$desc\',\'$body\')\";

} [/php:1:8a620cf238]

That, though, only made the number 1, which it continued to do forever. I think the problem might be in the mysql_insert_id() but I don\'t know how to fix that... Have you sobered up enough to help? :wink:

[Savizzaviz]

Oh, and I\'m thinking the method for getting the last story_id might not work properly, since my story_id variables aren\'t all in a 1, 2, 3, 4 order seeing as some of the rows get deleted. I was thinking there\'d be a way to do it within the SQL query so as to select the last row somehow.

[DylanBlitz]

well, looked good at 2am lol

 

I was going about it the wrong way, but I\'ll blame it on alcohol :?

 

hehe give this a shot

 

[php:1:944d03e0f0]<?php

 

$published = date(\"Y-m-d\");

$gethigh = mysql_query(\"SELECT story_numb FROM stories ORDER BY story_num DESC LIMIT 1\");

$gethighnum = mysql_fetch_array($gethigh);

$highnum = $gethighnum[story_numb];

$newestnum = $highnum + 1;

$sql = mysql_query(\"INSERT INTO stories (story_numb,story_title,story_pub,story_desc,story_body) VALUES (\'$newestnum\',\'$title\',\'$published\',\'$desc\',\'$body\')\");

 

?>[/php:1:944d03e0f0]

[Savizzaviz]

Hey, thanks, that did it. I had to change \"ORDER BY story_num\" to \"story_numb\" but it worked.

If you want, you can check it out here (You wont be able to add stories, though, obviously ). If you see anything wrong with any of my scripts, I\'d appretiate a message.

 

Thanks for the help.

[DylanBlitz]

one thing i noticed, if you don\'t select a story and you hit the button, it gives an error.

[Savizzaviz]

Hmm, good catch... I hadn\'t noticed that (I\'m using Mozilla Firebird and it seems to only happen in IE). It should work now.