Mysql with function-CodePudding

CREATE TABLE `emp_info` (
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
first_name VARCHAR(70),
last_name VARCHAR(70),
PRIMARY KEY (id),
KEY (user_id)
);

CREATE TABLE `message_info` (
  id INT NOT  NULL AUTO_INCREMENT,
  user_id INT,
  message MEDIUMTEXT,
  PRIMARY KEY (id),
  CONSTRAINT fk_message FOREIGN KEY(user_id) REFERENCES emp_info(user_id)
);

INSERT INTO emp_info(user_id,first_name,last_name) VALUES
(001,'Ashley','Smith'),
(002,'Valen','Hue'),
(003,'Mia','Cmd'),
(004,'Alex','Smith'),
(005,'Dy','Thomson'),
(006,'Ashley','Smith'),
(007,'Alex','Smith'),
(008,'Dy','Thomson');

INSERT INTO message_info(message) VALUES
('This is  a  message1 '),
('This is  a  message2'),
('This is  not   messge3'),
('This is  a  messagee4'),
('This is  a  messge5');

Hi, all I need mini help with the function. Create a function with parameters of id user to count and return the total number of messages he/she has sent.

CodePudding user response：

if we are talking about a specific id, the query is like:

SELECT count(*) FROM message_info where message_info.user_id = ´your_id´

To create a function is like:

DELIMETER $$
CREATE FUNCTION count_message(id INT) 
RETURNS INT 
DETERMINISTIC 
BEGIN 
DECLARE count_message INT; 
select count(*) INTO count_message FROM message_info where message_info.user_id = ´your_id´; 
return count_message; 
END$$ 
DELIMITER ;

in you query insert you are missing the id user, so you need add it.