Jump to content

Update field according to last entry


Savizzaviz

Recommended Posts

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?

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.