Quick MySQL question

[snareklutz]

How will I go about minus 1 to an entire column in a table?

-S.

 

[Palyriot]

the Update function. find the old value through a loop for each file, and in the loop, update it +1. I have to go for now, if no one has helped by the time I get back, I will write it for you.

 

[moondog]

UPDATE <table> set <column> = <column> - 1

 

[Peter]

yes as moondog sats you can just do it via 1 query. or if you want to only update 1 entry then use a where clause as normal.

There is no need at all to go through a loop for such a simple task.

 

[Palyriot]

he wants to update every column in the database, requiring a loop statement.

 

[Peter]

no, every column can be updated by not adding a where clause

 

[mch]

"to an entire column in a table"
This clearly means one column in a table, correct me if I'm wrong.

 

[Peter]

yes that is correct, of course you can have many rows in that column.

 

[Palyriot]

ok, this is taking too long to answer. I'll write the script for you.

I realized while writing this, that if their is more than one row with the same value, it could mess things up. If there is an easier way, please tell. I'd like to learn from this too.

<?php
$db = mysql_connect("localhost", "hostname_username", "password");
mysql_select_db(hostname_table", $db);
$query = mysql_query("SELECT * FROM table", $db);
while ($col = mysql_fetch_array($query)) {
	mysql_query("UPDATE table SET column = '$col[num] - 1' WHERE column = '$col[num]'", $db);
}
?>

 

[snareklutz]

UPDATE <table> set <column> = <column> - 1
will work...thanks alot...

 

[Palyriot]

If every row is the same in that column, then that will work, but if they are different at all, you need a loop. You didn't really clarify the contents of it.

 

[MikeBigg]

If every row is the same in that column, then that will work, but if they are different at all, you need a loop. You didn't really clarify the contents of it.

Palyriot,

with respect, this can be done in SQL ... no php and no loop required ... the database engine will take care of all the looping for you.

Look at it a different way. If you wanted to decrement a single value in a column you would use something like:

UPDATE <table> set <column> = <column> - 1 where id = 77

dropping off the where clause causes the UPDATE statement to act on all rows in the table one by one.

Mike

 

[Peter]

Palyriot you seem very insistant that a loop is required, seriously go and try it, no loop is required at all!!!.

There is no need to select the value of the field as that is what is happening in "<column> - 1" by putting the column name there it will first retrieve the value of that first then take the 1 away and will input that into the table. It will do this for every row if no where clause is specified.

If you do not beleive us why not go and try it!

 

[Palyriot]

Doesn't he want to do it to ever row in his table? That's what he makes it sound like when he says "to an entire column". If you want to do it to every row in one column, then you need a loop. If there is only one row needed to be changed, then it is just one mysql query and he is done.

 

[MikeBigg]

Doesn't he want to do it to ever row in his table? That's what he makes it sound like when he says "to an entire column". If you want to do it to every row in one column, then you need a loop. If there is only one row needed to be changed, then it is just one mysql query and he is done.

Palyriot,

I can't work out whether you are trolling now or that you really don't understand.

Forgetting the incrementing part for a moment. Do you accept that the following SQL will set every row of a column to 123 ?

update <table> set <acolumn> = 123

Mike

 

[Peter]

Palyriot an update query will update ALL row's in a table normally in fact you have to go 1 more to stop it doing this behaviour by adding a where clause.

By doing it the way you suggest you are adding alot more work to php/mysql as well as coding for the user.

If the user had 1000 rows in his table doing it your way 1001 queries would have to be carried out, doing it our way only requires 1 query, mysql does all the work!.

As I have already suggested create a table on a mysql table yourself, add multiple rows to it and try it yourself.

 

[Palyriot]

I just realized this: UPDATE <table> set <column> = <column> - 1, but the entire time, you guys were using the where statement in there, which would only change rows that are alike. Sorry for the confusion.

If every row is the same in that column, then that will work, but if they are different at all, you need a loop. You didn't really clarify the contents of it.

Palyriot,

with respect, this can be done in SQL ... no php and no loop required ... the database engine will take care of all the looping for you.

Look at it a different way. If you wanted to decrement a single value in a column you would use something like:

UPDATE <table> set <column> = <column> - 1 where id = 77

dropping off the where clause causes the UPDATE statement to act on all rows in the table one by one.

Mike

Wow, I'm an idiot. I'm sorry. I just read what he wrote in what I quoted more clearly. I'm really sorry I didn't see that the entire time. It was so simple. That's really embarresing.

 

[MikeBigg]

LOL ... funny how we can miss the obvious sometimes.

Mike

 

[Palyriot]

Yep, I couldn't lose the fact of the where clause and the loop.

 

[moondog]

Wow, I'm an idiot. I'm sorry.
. . .
I'm really sorry I didn't see that the entire time. It was so simple. That's really embarresing.

Been there, done that - MORE THAN ONCE.

:hehe:

-Bob