Updating a column with Numbers

Question

Please help me, I am new in this field trying to understand how to achieve updating a column from table with Numbering of each row from that table. (from 1 to ~).
Table name PRODUCTS.
Column name NUMBER.
I want to mention from that the start that Field Number is totally different from the ID. Products are inserting and deleting and ID is autoincrementing. But Field Number is UPDATING with the number of rows. So I want to achieve to have a button while submiting to update the field Number with corespondent number of rows.

Please suggest some PHP/MySql script which could help me to update the Field NUMBER with numbers. I would much appreciate any of your suggestion. Thank you in advance!

My script is wrong:

$rank="0"; $q = mysql_query("SELECT NUMBER FROM PRODUCTS"); $productCount = mysql_num_rows($q); if ($productCount > 0) { while($row = @mysql_fetch_row($q)){ mysql_query("update PRODUCTS set NUMBER=$rank++ where ID IS NOT NULL"); }

edited: Database now:

ID NAME NUMBER ________________ 201 Apple 3 203 Orange 5 204 Grape 17

should be after updating:

ID NAME NUMBER ________________ 201 Apple 1 203 Orange 2 204 Grape 3

can you give some sample data here to make it more clear like how is ur table and how you want it? — AnandPhadke
– AnandPhadke, Commented Nov 26, 2012 at 10:43
in the first place I would like to UPDATE the entire Column NUMBER starting with number 1 and next numbers 2,3,4... and so on — Sergiu Costas
– Sergiu Costas, Commented Nov 26, 2012 at 10:49

AnandPhadke · Accepted Answer · 2012-11-26 10:54:35Z

2

SET @rank:=0; update tablename set NUMBER=@rank:=@rank+1

answered Nov 26, 2012 at 10:54

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sergiu Costas Over a year ago

Nice I thing this should be the way, but How to use it in PHP?

AnandPhadke Over a year ago

use the whole query and execute in php

cichy Over a year ago

add at the end ORDER BY ID ASC so it update values by correct order

Sergiu Costas Over a year ago

I do something wrong, please write me an example how to apply this in PHP

AnandPhadke Over a year ago

may be you can try this link for more details on php - pear.php.net/manual/en/package.database.db.intro-execute.php

|

Collectives™ on Stack Overflow

Updating a column with Numbers

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related