For one of my inserts I have the following: (PDO / MySQL)
$this->sql = "INSERT INTO tblfixedfare SELECT NULL, MAX(FixedFareID)+1, '1', '$fieldDay' FROM tblfixedfare UNION SELECT NULL, MAX(FixedFareID)+1, '2', '$fieldNight' FROM tblfixedfare"
Which works fine if the database is not empty. How would I go about setting MAX(FixedFareID) == 1
if the table is empty?
SELECT NULL, IFNULL(MAX(FixedFareID), 0) + 1
[edit]
As @AndreKR mentioned, it's arguably better to use COALESCE
. It is better compatible accross databases (in Oracle IFNULL is called NVL instead). Also, COALESCE allows more than 2 parameters, in which case it returns the first one that is not null. I think COALESCE is less readable, probably because the term is less known to non-native english speakers (like me), where IFNULL is quite self-explanatory. But that's a small trade-off for better compatibility and a more flexible use.
$this->sql = "INSERT INTO tblfixedfare SELECT NULL, IFNULL(MAX(FixedFareID),1)+1, '1', '$fieldDay' FROM tblfixedfare UNION SELECT NULL, IFNULL(MAX(FixedFareID),1)+1, '2', '$fieldNight' FROM tblfixedfare"