I have a input field in a basic html form which the user can either add their own reference number, or they can leave this input blank. However I want it so that if the user leaves the field blank that the sql will generate a reference number based on a count of a foreign key in my mysql table.

INSERT INTO my_table (`reference`,)
VALUES (CASE {$inputValue}
            WHEN '' THEN sum(case when `fk` CONTAINS {$inputFK} then 1 else 0 end)
            ELSE {$inputValue}
        END)

When I've run the above code I get '#1111 - Invalid use of group function'

Is there a better way of doing this, or is there a way of fixing the above code?

CodePudding user response：

Like the commants already explained , you need some selects to run this query

CREATE TABLE my_table (reference int, colum1 varchar(50) , fk varchar(50))

INSERT INTO my_table (reference) 
SELECT CASE `colum1`
            WHEN '' THEN (SELECT  sum( `fk` = 'test' ) FROM my_table)
            ELSE 1
        END
        FROM my_table 

SELECT * FROM  my_table

reference | colum1 | fk
--------: | :----- | :-

db<>fiddle here