Russia Posted March 17, 2011 Share Posted March 17, 2011 Now im trying to do one more thing, that if I make another row with the value 2 for the type of news it is, I want to automatically turn the oldest news with the value 2 in the type column to '3'. Is there any kind of query to update the row to '3' if a row is found with the same value and update the oldest news that was posted with the value 2. Also, I want it to work that if only there are 3 rows in there with the same value. Id be happy if I could get some help. Is there anything that can do it like that? or can that not be created? Quote Link to comment Share on other sites More sharing options...
phpTrainee Posted March 17, 2011 Share Posted March 17, 2011 I don't get what you're asking. You want to update the oldest 2 to a 3 as soon as you add a new 2 row? // do whatever you need to insert your row if ($value==2) { $row = mysql_fetch_array(mysql_query('SELECT id FROM table WHERE value=2 ORDER BY id LIMIT 1')); $id=$row['id']; mysql_query('UPDATE table SET value=3 WHERE id='.$id.' LIMIT 1'); } Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 Yeah im trying to find the oldest row created that has a value for that row as 2 in that column named 'newstype', it has a timestamp on it in the column named 'dtime' Im trying to get that row, then set that column named 'newstype' value from 2 to 3. Is that possible? Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 17, 2011 Share Posted March 17, 2011 Psuedo query the db id where column x = 2 order by dtime (oldest first) limit 1, then using that id, update x=3 where id = id Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 Basically what i want to update my db table that anything with a type of 2 thats older then x date to be made into type 3. How would I do that? Will your process work? Quote Link to comment Share on other sites More sharing options...
phpTrainee Posted March 17, 2011 Share Posted March 17, 2011 Well then just make an update statement. $time = 'x date cutoff'; mysql_query('UPDATE table SET newstype=3 WHERE newstype=2 AND dtime <= '.$time); Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 Okay, but the thing is, I dont want to set a date, I want it to automatically find the oldest date. Should I just set the date to like the 1900's just as a failsafe? Also, it will edit the OLDEST row only, not all the rows that are before that date correct? Again I dont want to set a date I want it to find the row with the oldest date, and update it to 3. Quote Link to comment Share on other sites More sharing options...
phpTrainee Posted March 17, 2011 Share Posted March 17, 2011 Do you want to update the oldest row or all rows before that date? If you just want the oldest row: $row = mysql_fetch_array(mysql_query('SELECT dtime FROM table WHERE newstype=2 ORDER BY dtime LIMIT 1')); $time=$row['dtime']; mysql_query('UPDATE table SET newstype=3 WHERE newstype=2 AND dtime = '.$time.' LIMIT 1'); Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 17, 2011 Share Posted March 17, 2011 think about it... row 7 is oldest containing a 2 - so change it to a 3 immediately row 6 is oldest with a 2 so change it to 3 etc etc till all the 2's become 3's how often will it check for oldest 2? hourly? daily? weekly? Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 I always want to keep three of the news that are value 2. When I create a new news post with the value 2 I want to update the oldest one to 3. Basically the 2 will mean that the 3 current news that arent important. 1 will stay for a long time until I create a new MAIN news post which will set the old MAIN news post before it to 2. Do you kind of understand it now? Also, how do I do it so it only updates it when there are exactly 3 rows that contain the value 2, so i can keep exactly 3 Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 You are doing a great job helping me out and I appreciate it. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 17, 2011 Share Posted March 17, 2011 I'm betting there's a much less convoluted way of doing whatever it is you're trying to do. Why don't you explain to us what the overall goal you want to achieve here is. I.E. why are you trying to assign these 'flag' numbers to the different records, and what will they be used for? Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 Im trying to keep at any one time i want to keep 3 rows with the value 2 in the news_type column. If a new news post is made with the value 2 in the news_type column I want to make it so the oldest news posted with the value 2 in the column news_type to be set to 3. So that there aren't 4 rows with the value 3 but still 3 just with a new news post. I want it to also only update it to 3 if there are ONLY 3 rows (no less than 3 rows or more than 3 rows) with the value 2 in column news_type. (took me a while to actually try and explain it in a sensible format without to many words). Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 17, 2011 Share Posted March 17, 2011 The question is WHY do you want to do that? What is it being used for? Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 For a news system, there are 3 sections. MAIN NEWS (contains the only row with the value 1 in the column 'news_type'.) RECENT NEWS (contains the 3 rows that contain the value 2 in the column 'news_type'.) when a new row is created with the value 2 the oldest row with the value 2 getting updated with the value 3. turning it into an archives and showing it in the archives section. ARCHIVES (contains the rows that have the row value 3 in the column 'news_type'.) Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted March 17, 2011 Share Posted March 17, 2011 Is main_news always the newest article? Are the recent_news always the 2nd 3rd and 4th newest, and everything else older is considered archive? If that's the case, this can all be done without the added field. Quote Link to comment Share on other sites More sharing options...
Russia Posted March 17, 2011 Author Share Posted March 17, 2011 No not always, because when I post a new main news the old main news article will turn the row value from 1 to 2. There is a reason for it. Quote Link to comment Share on other sites More sharing options...
Russia Posted March 19, 2011 Author Share Posted March 19, 2011 Hey was anyone able to kind of figure out how to do it? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.