CODEWITHSUNDEEP
mysql
mdnba50
mdnba50

Reputation: 379

MAX(count) + 1 when inserting, MySQL

I currently use the below query to increment the third column count on every insert.

$DB2->query("INSERT INTO relations (item_ID,tag_ID,count)
                    SELECT '$ID', '$tag_id', MAX(count) + 1
                    FROM relations
                    WHERE tag_ID = '$tag_id';");

The problem is when there is no rows in the table and i try to insert, the Max(count) + 1 is just null. I've tried defining the default value as zero but still null. The column should be 1 on first insert.

How do i change the query, so if first insert then count is 1. I don't want to do a select query before because this code is in a loop.

Upvotes: 0

Views: 899

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

add an ifnull(...,1)

"INSERT INTO relations (item_ID,tag_ID,count)
                SELECT '$ID', '$tag_id', ifnull(MAX(count) + 1,1)
                FROM relations
                WHERE tag_ID = ''$tag_id';");

Upvotes: 3

Related Questions