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_ID draws its values in the Users table's ID area
  • Date_Login ought to be between 24 months ago and today
  • Location should alternate between 'site' and '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

Thanks!

(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;

Normally 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.