Please support our PHP advertiser: Lunarpages PHP Web Hosting
 |
•
•
Join Date: May 2005
Posts: 230
Reputation: 
Rep Power: 4
Solved Threads: 0
Posting Whiz in Training
Hey, I've got a simple little table that has only 3 fields. ID (primary key, auto-increment), cat_name, and cat_num.
I need to know how to get my php script to go into that table, and increase the value in cat_num by one on every row.
So for instance, here is my table:
ID cat_name cat_num
3 Music 1
5 Movies 2
6 Food 3
10 Other 4
I need a script that will change the cat_num for all those rows to be one higher than they already are. I tried using a for loop, and a while loop, but can't seem to get it to work. Anyone know an easy way to do this?
Edit, sorry, just realized the table doesn't look nice and neat like I typed it. But hopefully you get the drift.
I need to know how to get my php script to go into that table, and increase the value in cat_num by one on every row.
So for instance, here is my table:
ID cat_name cat_num
3 Music 1
5 Movies 2
6 Food 3
10 Other 4
I need a script that will change the cat_num for all those rows to be one higher than they already are. I tried using a for loop, and a while loop, but can't seem to get it to work. Anyone know an easy way to do this?
Edit, sorry, just realized the table doesn't look nice and neat like I typed it. But hopefully you get the drift.
Last edited by nathanpacker : Oct 29th, 2007 at 2:10 pm.
•
•
Join Date: Aug 2007
Location: Cavite,Philippines
Posts: 508
Reputation:
Rep Power: 3
Solved Threads: 68
Posting Pro
try this one:
I think that's it,let me know if it solves your problem thanks...
php Syntax (Toggle Plain Text)
I think that's it,let me know if it solves your problem thanks...
Last edited by ryan_vietnow : Oct 29th, 2007 at 8:18 pm.
"death is the cure of all diseases..."
http://ryantetek.wordpress.com
http://ryantetek.wordpress.com
•
•
Join Date: May 2005
Posts: 230
Reputation:
Rep Power: 4
Solved Threads: 0
Posting Whiz in Training
Thanks. Sorry it's taken me a while to reply. I haven't had time to get back to this part until now. Your code make sense, and it looks to me like it should work, but it's not. What it ends up doing is setting cat_num on EVERY row to the same number, in fact, one number higher than the highest number that was in there before.
To make it clear. I had seven entries in the table. The cat_num's were 1,2,3,4,5,6,7. After running the script, it changed all of them to 8. Here is the exact code I used. Anything I've done wrong?
And I tried using $cat_new=$cat_num++ as well, and it made them all 7 instead of 8.
To make it clear. I had seven entries in the table. The cat_num's were 1,2,3,4,5,6,7. After running the script, it changed all of them to 8. Here is the exact code I used. Anything I've done wrong?
<?php
include 'config.php';
$query="SELECT cat_num FROM categories";
$result=mysql_query($query);
$num=mysql_num_rows($result);
$i=0;
while($i<$num)
{
$cat_num=mysql_result($result,$i,"cat_num");
$cat_new=$cat_num+1;
$query="UPDATE categories SET cat_num='$cat_new'";
mysql_query($query);
$i++;
}
echo "Done!";
?>And I tried using $cat_new=$cat_num++ as well, and it made them all 7 instead of 8.
•
•
Join Date: Aug 2005
Posts: 4,844
Reputation:
Rep Power: 17
Solved Threads: 325
This is really simple, just fire up mysql from the command line and play with the commands. You don't need php.
*Voted best profile in the world*
•
•
Join Date: May 2005
Posts: 230
Reputation:
Rep Power: 4
Solved Threads: 0
Posting Whiz in Training
•
•
•
•
Originally Posted by iamthwee 
This is really simple, just fire up mysql from the command line and play with the commands. You don't need php.
Well I've been trying that, and playing around with the commands, but can't seem to get it to work quite right. Funny thing is, in my php code, I echoed out the variables immediately before the UPDATE command runs, and it echoes out all the variables correctly. However, what I think is happening is that every time the while loop runs, the UPDATE command is updating ALL of the rows, even though I've told to only UPDATE on a specific row. Can't figure out why. But thanks for your help.
•
•
Join Date: Feb 2002
Location: Lawn Guylen, NY
Posts: 11,115
Reputation:
Rep Power: 33
Solved Threads: 117
Can't you do
UPDATE table SET col_value = col_value + 1? Dani the Computer Science Gal
•
•
Join Date: Sep 2007
Location: Budapest
Posts: 252
Reputation: 
Rep Power: 0
Solved Threads: 14
Posting Whiz in Training
•
•
•
•
Originally Posted by cscgal
Can't you do UPDATE table SET col_value = col_value + 1? this will work if the column type is int.
Do a favour, leave me alone
•
•
Join Date: May 2005
Posts: 230
Reputation:
Rep Power: 4
Solved Threads: 0
Posting Whiz in Training
•
•
•
•
Originally Posted by cscgal
Can't you do UPDATE table SET col_value = col_value + 1? You'd think, but I tried that and it didn't work either. It seems that any UPDATE I do in a loop, just makes the changes to every row, even if I specify only one row. There's got to be a simple solution to this problem. And to be honest cscgal, I thought yours was it! Anyway, here's a little script I wrote just to troubleshoot this issue. It prints out all the table values before it runs the UPDATE command, then echoes out the UPDATE commands with their values, and then echoes out the values after the operation is complete.
It all looks like it should go fine, but when you see the final values, it's just screwy. I also included a link at the bottom to a script to set all the values back to normal, so anyone looking at the thread can see it as well.
To run the script, just go to www.packerworld.com/budget/upcat.php
Here is the script:
<?php
include 'config.php';
//Printing out values before the change
$query="SELECT * FROM categories";
$result=mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result))
{
echo"ID " . $row['id'] . ": " . $row['cat_num'] . "<br>";
}
//Getting the highest cat_num there is
$query = "SELECT MAX(cat_num) FROM categories";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$max=$row['MAX(cat_num)'];
}
//Initialize the counter
$i=1;
//Loop through all the cat_num's...
while($i<=$max)
{
$new_cat=$i+1;
echo "UPDATE categories SET cat_num=$new_cat WHERE cat_num=$i...<br>";
mysql_query("UPDATE categories SET cat_num=$new_cat WHERE cat_num=$i") or die(mysql_error());
$i++;
}
//Print out the values after the process is complete...
$query="SELECT * FROM categories";
$result=mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result))
{
echo"ID " . $row['id'] . ": " . $row['cat_num'] . "<br>";
}
echo "<a href='normal.php'>Click here to reset cat_num's back to their original incremental orders.</a>";
?> •
•
Join Date: Sep 2007
Location: Budapest
Posts: 252
Reputation:
Rep Power: 0
Solved Threads: 14
Posting Whiz in Training
why dont you try stored procedures??
sp can do it better and faster
sp can do it better and faster
Do a favour, leave me alone
•
•
Join Date: May 2005
Posts: 230
Reputation:
Rep Power: 4
Solved Threads: 0
Posting Whiz in Training
Well, if I knew how to create and use them, and if it were possible through phpMyAdmin, seeing as that is the only access I have to my databases, I might try that. However, I'm not too worried about speed and whatnot, as this procedure will never be updating more than 20, 30 rows max. And even then, to do that, wouldn't I have to have the SQL commands figured out. Which so far, I can't even figure out what SQL commans would do it. If we could find a sequence of SQL commands to do it, my problem would be solved.
Oh, and another point, I will not simply always be updating every row in the database. That's just where I'm starting to figure out the problem. In reality, I will be updating some or most of the rows. For example, say I have 20 rows, with cat_num's 1-20. I will be inserting a row, making it's cat_num say 13, then incrementing all rows with cat_num's 13 and up one integer.
Oh, and another point, I will not simply always be updating every row in the database. That's just where I'm starting to figure out the problem. In reality, I will be updating some or most of the rows. For example, say I have 20 rows, with cat_num's 1-20. I will be inserting a row, making it's cat_num say 13, then incrementing all rows with cat_num's 13 and up one integer.
Last edited by nathanpacker : Nov 1st, 2007 at 12:57 pm.
|
Similar Threads
Other Threads in the PHP Forum
- error on MySQL UPDATE on a text field (PHP)
- insert csv file into mysql through php (PHP)
- Selecting MYSQL into fields (HTML and CSS)
- retreive and update from check boxes (ColdFusion)
- mysql update help??? (PHP)
- MySql multiple table query problem.... (MySQL)
- serch mysql for many fields (PHP)
- How can i deledet and update from table ??????? (PHP)
Other Threads in the PHP Forum
- Previous Thread: results to form field
- Next Thread: MySQL require_once stopping script workin?
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
