Jump to content


Photo

Update field according to last entry


  • Please log in to reply
7 replies to this topic

#1 Savizzaviz

Savizzaviz
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 10 August 2003 - 08:12 AM

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?

#2 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 10 August 2003 - 09:17 AM

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]

#3 Savizzaviz

Savizzaviz
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 10 August 2003 - 07:53 PM

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:

#4 Savizzaviz

Savizzaviz
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 10 August 2003 - 07:57 PM

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.

#5 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 11 August 2003 - 04:49 PM

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]

#6 Savizzaviz

Savizzaviz
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 11 August 2003 - 06:57 PM

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.

#7 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 11 August 2003 - 08:38 PM

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

#8 Savizzaviz

Savizzaviz
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 11 August 2003 - 10:01 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users