Get largest value from mySQL table and increase by one

Question

I am looking to add a function that will get the largest number from a specific column in a table and add to it before doing an INSERT query. (I cant have it be auto increment as several entries need to have the same value this is controlled through an if statement) however it isnt doing this and isn't increasing it by 1 based off the highest value.

$max = "SELECT MAX(LocationID) FROM boss";
$result = mysqli_query($connection, $max);
$locID = $result+1;

$query = "INSERT INTO boss (ID, Name, Type, Location, LocationID, Difficulty) VALUES ('0', '$boss', '$type', '$loc', '$locID', '$diff')";

Possible duplicate of [Insert and set value with max()+1 problems](https://stackoverflow.com/questions/5360117/insert-and-set-value-with-max1-problems) — aynber, May 22 '17 at 21:05
I need to only add 1 to the max if it doesn't comply into one of the other if statements. — Kai Jones, May 22 '17 at 21:08
Why don't you just use `AUTO_INCREMENT` for this? Isn't that what you're already doing with the `ID` field, why do you need another field that increments? — Barmar, May 22 '17 at 21:23

score 1 · Answer 1 · answered May 22 '17 at 21:20

1

You don't need to use two queries for this, you can do it in the INSERT query.

$query = "INSERT INTO boss (ID, Name, Type, Location, LocationID, Difficulty)
    SELECT '0', '$boss', '$type', '$loc', MAX(locationID)+1, '$diff'
    FROM boss";

answered May 22 '17 at 21:20

Barmar

741,623
53
500
612

1

It's still a terrible idea. :-( – Strawberry May 22 '17 at 22:20
@Strawberry Agreed, it seems like he should just be able to use an auto increment column for this. – Barmar May 22 '17 at 23:59

score 0 · Answer 2 · answered May 22 '17 at 21:15

0

You forgot to fetch the result

$max = "SELECT MAX(LocationID) as m FROM boss";
$result = mysqli_query($connection, $max);
$result = mysqli_fetch_array($result);
$locID = $result[0]+1;

answered May 22 '17 at 21:15

Oleg Dubas

2,320
1
10
24

Get largest value from mySQL table and increase by one

2 Answers2