How to create an insert function based on conditions

Question

I am having a little trouble with my function. I am getting a syntax error and I do not know how to insert multiple things in one go.

Essentially I want the safe_insert() function to take in:

• _museum_id the id from the museums table
• _curr_date a string of a date, eg. 2021-01-23 09:23:48
• _customers a string separated by commas, eg. bob, billy,zoey,sarah,heather

However, when it inserts the group members, it should insert 5 rows into group_members, as there are 5 names in this case (bob,billy,zoey,sarah,heather).

Errors/questions:

• a syntax error which I don't know how to fix #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 9
• I also don't know how I would separate the string by comma to insert 5 rows.

Tables:

DROP TABLE IF EXISTS group_members;
DROP TABLE IF EXISTS group_visit;
DROP TABLE IF EXISTS museums;
CREATE TABLE museums (

    id                          int
                                NOT NULL
                                AUTO_INCREMENT
                                PRIMARY KEY,
    
    name                        varchar(64)
                                not null
);

INSERT INTO museums (id, name) VALUES (1, 'dino-museums');

CREATE TABLE group_visit (

    id                          int
                                NOT NULL
                                AUTO_INCREMENT
                                PRIMARY KEY,

    museum_id                   int
                                NOT NULL,
                                FOREIGN KEY (museum_id) REFERENCES museums (id),

    date_added                  datetime
                                NOT NULL
);

CREATE TABLE group_members (

    group_visit_id              int
                                NOT NULL,
                                FOREIGN KEY (group_visit_id) REFERENCES group_visit (id),

    customer                    varchar(32)
                                NOT NULL,

    PRIMARY KEY (group_visit_id, customer)
);

My function:

DROP FUNCTION IF EXISTS safe_insert;
    
DELIMITER //
CREATE FUNCTION safe_insert(
    _museum_id INT,
    _curr_date VARCHAR(32),
    _customers varchar(1024)
    ) RETURNS int
   
BEGIN
  DECLARE
    should_insert_museum int DEFAULT 1;
    group_visit_id int;
    
    
    /* Check if a group visit with the same museum_id has been inserted in the last 15 minutes */
    SELECT 0 INTO should_insert_museum
    FROM group_visit
    WHERE museum_id = _museum_id
    AND date_added >= DATE_SUB(_curr_date INTERVAL 15 MINUTE);
    
    /* If we found a result, do not insert anything and return 0 */
    IF should_insert_museum = 0 THEN
        RETURN should_insert_museum;
    
    /* Insert a group visit and store the id in group_Visit_id */
    INSERT INTO group_visit (museum_id, date_added) VALUES (_museum_id, _curr_date);
    SELECT LAST_INSERT_ID() INTO group_visit_id;
    
    /* How do I insert the list of customers into group_members */
    
        
  
  RETURN group_visit_id;
  
  
END //
DELIMITER ;

Answer 1

I'm not seriously advovating this as a solution because I think things like this should be handled in application code, but just for fun...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,name VARCHAR(12) UNIQUE
);


DELIMITER $$

CREATE OR REPLACE PROCEDURE array_insert(my_array VARCHAR(50))
BEGIN
    
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= CHAR_LENGTH(my_array)-CHAR_LENGTH(REPLACE(my_array,',',''))+1 DO
        INSERT INTO my_table (name) VALUES (SUBSTRING_INDEX(SUBSTRING_INDEX(my_array,',',counter),',',-1));
        SET counter = counter + 1;
    END WHILE;

END$$

DELIMITER ;

CALL array_insert('billy,bob,jane');

SELECT * FROM my_table;
+----+-------+
| id | name  |
+----+-------+
|  1 | billy |
|  2 | bob   |
|  3 | jane  |