Hi everyone :)
I've got a small issue with my code...
Say you want to limit the amount of messages a user can send on your website... I'd typically do this by using PHP, in pseudo-code:
SELECT messages FROM table WHERE user_id = 123
if messages > 10, return 'message limit reached'
else, INSERT message INTO table
But I'm wondering if this can be done purely with SQL in MySQL, something like this, in pseudo-code:
INSERT message INTO table if messages < 10 where user_id = 123
This is my best try, but I keep receiving a syntax error...
INSERT INTO message_user_map
(user,
(select count(user)
FROM product_image_map
WHERE user='123') < 10)
VALUES ('123', 'hey! this is a new message!')
Can anyone give me a tip on how to correct this statement?
This looks more like a stored procedure than a conditional insert. did you try aggregations?
something like this?
INSERT INTO message_user_map SELECT 1, 'test123' FROM product_image_map WHERE user IN (SELECT user FROM product_image_map GROUP BY user HAVING COUNT(user) > 1) GROUP BY user;although most likely a stored procedure would be more comfortable (although hidden)
MariaDB [test]> create table product_message_map (user_id int(11)); MariaDB [test]> create table message_user_map (user_id int(11), message VARCHAR(1000)); delimiter $$ create procedure insert_if_limit_not_reached(IN local_user_id INT(11),IN message VARCHAR(1000),IN max_amount INT(11)) begin DECLARE amount_messages INT DEFAULT 0; SELECT COUNT(*) INTO amount_messages FROM product_message_map WHERE user_id = local_user_id; IF (amount_messages < max_amount) THEN INSERT INTO message_user_map VALUES (local_user_id, message); END IF; end $$ DELIMITER ;and you would call it via
call insert_if_limit_not_reached(1, 'test', 10);