Andrew 38 - 1 year ago 74
PHP Question

# auto increment every 10 rows

I'm using PHP. I will create a a column in my database that will have a value, such as

1
. I want to set this value to auto-increment each time it passes 10 rows:

In other words:

1st row will have the value = 1
2nd row will have the value = 1
........
........

10th row will have the value = 1

11th row the value will auto increment and change to be = to 2


Here is the solution for you

Assume you have a table named table1 and that table have following structure

CREATE TABLE table1 (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)


Note that id column is auto increment

Following query will give you the result you want

select a.id,FLOOR((a.id-1)/10)+1 as calculated_value from table1 a


In the result calculated_value will give you the result.

If you are interested, some additional information

Assume you fear that taking auto increment column for calculation is risky then you can use following

    SELECT t.id,t.name ,(@num:=@num+1) AS i,FLOOR((@num-1)/10)+1
as calculated_value  FROM table1 t CROSS JOIN (SELECT @num:=0) AS
dummy ORDER BY id;