CODEWITHSUNDEEP
phpmysql
MR.Internet
MR.Internet

Reputation: 645

PHP Mysql Increment by 1 with group by some field

Look th etable below

 +----+---------+---------+----+---------+---------+
| id |  name   | order   | one|  two    | three   |
+----+---------+---------+----+---------+---------+
|  1 | stan    |    1    |  5 |  suger  |   true  |
|  2 | kyle    |    1    |  4 |  coffe  |    yes  |
|  3 | stan    |    2    |  3 |  banana |    no   |
|  4 | kyle    |    2    |  4 |  orange |    false|
+----+---------+---------+----+---------+---------+

As you see from above I have two stan and two kyle. Now what happen is if I add a nother stan, I need the Code to see how many is the max number in order and add the row with adding +1 to the order field.

In short, add a row to mysql table increment the order by one +1 group by name. How do I achieve that in PHP and Mysql? or I think can be handled in Mysql Query only?

So, If I need to add the following information,

5 stan (calculate order) 3 banana true.

Upvotes: 0

Views: 198

Answers (1)

Light
Light

Reputation: 375

Even though its a bad idea to group by name since multiple people can have the same name, here's what I think the solution would look like:

select name, max(`order`)+1 
from orders 
group by name;

http://sqlfiddle.com/#!9/b25dbf/3/0

For adding the value to the table:

insert into orders(name,`order`) 
select name, max(`order`)+1 
from orders 
where name='stan'
group by name;

http://sqlfiddle.com/#!9/01db84/1

Adding multiple values:

insert into orders(name,`order`,one,two,three) 
select name, max(`order`)+1 , 3, 'banana', true
from orders 
where name='stan'
group by name;

Upvotes: 2

Related Questions