0

i want to create a table that have 3 column :

  1. visit_times : have a unique id for every specific visitor
  2. visitor_ID
  3. Date

i tryed to create the table as showing this code

CREATE TABLE test.table ( visit_times INT NOT NULL AUTO_INCREMENT , visitor_ID INT NOT NULL , Date VARCHAR NOT NULL , UNIQUE Unique column (visit_times, visitor_ID)) ENGINE = MyISAM;

but when i store this data into the table

INSERT INTO `tab`(`visitor_ID`, `date`) VALUES ("1", "15/05/2021");
INSERT INTO `tab`(`visitor_ID`, `date`) VALUES ("1", "20/07/2021");
INSERT INTO `tab`(`visitor_ID`, `date`) VALUES ("2", "02/09/2021");
INSERT INTO `tab`(`visitor_ID`, `date`) VALUES ("3", "24/08/2021");

the result it will be like this

visit_times visitor_ID date
1 1 15/05/2021
2 1 20/07/2021
3 2 02/09/2021
4 3 24/08/2021

and what i actually want as result

visit_times visitor_ID date
1 1 15/05/2021
2 1 20/07/2021
1 2 02/09/2021
1 3 24/08/2021

Server version: 8.0.21 - MySQL Community Server - GPL

I use Wampserver version 3.2.3 - 64bit

Apache Version : 2.4.46

1
  • 2
    Get your visit_times using a window function. Don't store dates in a varchar column. Use a date column. Welcome to SO.
    – danblack
    Sep 2 at 12:23
1

I am assuming visit_times means the nth time a user has visited.

You can't do it with AUTO_INCREMENT. You have 2 options:

1- Inserting visit_times value while inserting rows:

SET @visitTimes = IFNULL((SELECT visit_times FROM tab WHERE visitor_ID = 3 
ORDER BY visit_times DESC LIMIT 1), 0)+1;
INSERT INTO tab(visit_times, visitor_ID, date) 
VALUES (@visitTimes , "3", "24/08/2021");

2- Using a Trigger:

DELIMITER $$

DROP TRIGGER IF EXISTS setVisitTimesOnTab$$

CREATE TRIGGER setVisitTimesOnTab
    BEFORE INSERT
    ON tab
    FOR EACH ROW
BEGIN
    SET NEW.visit_times = IFNULL((SELECT visit_times FROM tab
                                  WHERE visitor_ID = NEW.visitor_ID
                                  ORDER BY visit_times DESC
                                  LIMIT 1), 0) + 1;
END $$

DELIMITER ;

Using a trigger is a simpler approach.

1
  • thank you so much :) <33
    – OMAR TOUIL
    Sep 2 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.