PHP Mysql Increment by 1 with group by some field

176
July 06, 2018, at 10:50 AM

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.

Answer 1

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;
READ ALSO
PHP SQLSRV returning blank data when some rows contain null

PHP SQLSRV returning blank data when some rows contain null

When the below is run from PHP the results are incorrect:

195
SQL query doesnt find exact name from database

SQL query doesnt find exact name from database

When I try the exact name from the database doesnt show up any resultsIf i search "Larissa" or "LARISSA", I get no results even in my database having "LARISSA CAMPOS"

189
cannot figure out how to chnage the location of PHP.exe in netbeans

cannot figure out how to chnage the location of PHP.exe in netbeans

I think I might be using a wrong instantiation of php on my computer (MySql metheds not working)I cannot figure out how to set the location of php

193