I have to populate a presently empty table having a hundred approximately fake records to simulate logins in the last 2 yrs to check my code with.
The login table schema appears like:
CREATE TABLE `Logins` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `User_ID` int(11) NOT NULL, `Date_Login` datetime NOT NULL, `Location` enum('site','admin') NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I truly am new at SQL generally, so, I not have the smallest idea exactly what the query need to look like past
INSERT INTO `Logins` (`User_ID`,`Date_Login`,`Location`) VALUES ...
Things I require is place N records (allows say 100) into
Logins to ensure that
User_IDdraws its values in the
Date_Loginought to be between 24 months ago and today
Locationshould alternate between
'admin', however with
'site'more heavily weighted (say 80% of times).
Hopefully, I'm able to glean some SQL-fu to assist concentrating on the same problems later on :D
(I am using MySQL 5.1)
Here's an SQL statement to place just one row in to the Logins table. You are able to run this frequently (e.g. inside a saved procedure) to obtain multiple records. You need to run it multiple occasions to obtain multiple records if you increase
LIMIT 1 to
LIMIT 10 you're going to get 10 records however the
User_ID values would be the same for every record.
INSERT INTO `Logins` (`User_ID`, `Date_Login`, `Location`) SELECT users.ID AS `User_ID`, DATE_SUB(NOW(), INTERVAL FLOOR(1 + (RAND() * (365 * 2))) DAY) AS `Date_Login`, IF(RAND() > 0.8, 'admin', 'site') AS `Location` FROM users ORDER BY RAND() LIMIT 1;
ORDER BY RAND() isn't good style since it is inefficient, but this is not a performance-sensitive task.
If you wish to make use of a full SQL solution, this is how you may create a loop: http://dev.mysql.com/doc/refman/5.1/en/iterate-statement.html To obtain a random date, you should use RAND() in conjunction with +, * plus some timestamps.