CODEWITHSUNDEEP
phpmysql
Ed Ball
Ed Ball

Reputation: 1

Unable to select highest value from a column

I have suffered with this for to long. It is turning me nuts...

I just want to take the max value of a column, add 1 to id and then insert a new entry.

The table has a column 'id' and lets say 'name' and 'age'

The problem is that the $new_id variable is not selecting the highest value from the id column. I know I am doing something wrong... but what??
Please help, I know this must be a pretty basic issue, but I have been looking at this for hours...

$relations_table="xxx_sobipro_relations";

$sql="SELECT * FROM '$relations_table' ORDER BY id DESC LIMIT 1";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);

$new_id = $row[id] + 1;

//more code

$query="INSERT INTO $relations_table (id, pid, oType, position, validSince)
VALUES ($new_id, 2, 'testtest' , $position , NOW() )";

Upvotes: 0

Views: 287

Answers (6)

d'alar'cop
d'alar'cop

Reputation: 2365

Use this instead:

SELECT COALESCE(MAX(id)+1,0) FROM `$object_table`

Upvotes: 4

Honk der Hase
Honk der Hase

Reputation: 2488

SELECT * FROM `$object_table`

table names and field names not in '' but `` (backticks)

And you should ask the database for errors...

and you should NOT use the mysql extension anymore, but switch to mysqli or PDO...

Upvotes: 2

jedwards
jedwards

Reputation: 30200

So, the query you're looking for is:

SELECT MAX(id) FROM table

But many databases provide an "auto-increment" functionality where you can specify a column (in this case id) to be the primary key and have its value auto-increment, so you don't have to do any of this.

Upvotes: 0

aynber
aynber

Reputation: 23001

You can do this on your insert:

$query="INSERT INTO $relations_table (id, pid, oType, position, validSince) 
    VALUES ((SELECT MAX(id)+1 FROM $object_table), 2, 'testtest' , $position , NOW() )";

Auto_increment is a better option, but this will work when you can't auto_increment.

Upvotes: 1

Elon Than
Elon Than

Reputation: 9765

First, it's $row['id'] not $row[id].

Second, mysql_* is deprecated, do not use it. Instead use PDO or mysqli.

Third, you can use MAX to get maximum value from column. SELECT MAX(id) FROM '$object_table' LIMIT 1.

And most important, set id field to autoincrement and don't set it (MySQL will do it).

Upvotes: 0

Joel Hinz
Joel Hinz

Reputation: 25374

You should look into AUTO_INCREMENT - turning that on for your id column will cause it to increase by one automatically every time you insert a row. That way, you won't have to specify id when you do the insertion, because MySQL keeps track of it for you.

Upvotes: 1

Related Questions