mdnba50 mdnba50 - 1 year ago 101
MySQL Question

MAX(count) + 1 when inserting, MySQL

I currently use the below query to increment the third column

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
on first insert.

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

Answer Source

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';");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download