在每个用户会话上显示不同的数据[关闭]

Question

Let's say I have 1000 rows in my table. And I have users that can login to my app. I want to display this 1000 rows to the 4 user (But only display 50 rows to each of them) without having them seeing the same rows. I'm using PHP and postgresql.

I'm thinking of using temp table to display the data and assign the data on each login, but is there another way to do this without database? Is it possible to use php session? What do you think is the best approach to do this?

Thanks for your advice

Answer 1

I would suggest creating three tables.

CREATE TABLE IF NOT EXISTS `data` (
`id` int(11) NOT NULL,
`value` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `name`) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Bill'),
(4, 'Ben');

CREATE TABLE IF NOT EXISTS `users_data` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`data_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `data`
ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
ADD PRIMARY KEY (`id`);

ALTER TABLE `users_data`
ADD PRIMARY KEY (`id`);

ALTER TABLE `data`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

ALTER TABLE `users_data`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Now, you can insert your 1000 records into the data table. Each time you fetch 50 random results from the data table, insert these values into the users_data table.

To select values from data, do the following query:

SELECT *
FROM data
WHERE data.id NOT iN (
    SELECT data_id
    FROM users_data
    WHERE user_id != '...current user id...'
)
ORDER BY RAND()
LIMIT 50

Insert the retrieved values with the current user id into users_data. This way, users can never get the values that other users got.