Php or mysql select data from each combination at random
I have a table named it_vit_qs from which I have to ask a question. Each set of questions should receive qs from each category (eg cat_id) and each level (eg lavel_id) in random order.
Please note that each category (cat_id) has 3 levels. There are 7 categories. This means that there will be a combination of 3 * 7 = 21. This means that cat_id 1 have level_id 1,2 and 3, cat_id 2 have level_id 1,2 and 3, etc.
Now, how can I randomly select 5 questions from each combination of cats and levels ??? I tried the following query, but it seems like slow and bad practice. I have included my sql and its result. But I want to do it in an efficient and easy way.
SELECT
a.*
FROM
(SELECT
*
FROM
it_vit_qs
WHERE cat_id = 1 AND level_id=1
ORDER BY RAND()
LIMIT 3) a
UNION
SELECT
b.*
FROM
(SELECT
*
FROM
it_vit_qs
WHERE cat_id = 1 AND level_id=2
ORDER BY RAND()
LIMIT 3) b
UNION
SELECT
b.*
FROM
(SELECT
*
FROM
it_vit_qs
WHERE cat_id = 1 AND level_id=3
ORDER BY RAND()
LIMIT 3) b
UNION
SELECT
b.*
FROM
(SELECT
*
FROM
it_vit_qs
WHERE cat_id = 2 AND level_id=1
ORDER BY RAND()
LIMIT 3) b
UNION
SELECT
b.*
FROM
(SELECT
*
FROM
it_vit_qs
WHERE cat_id = 2 AND level_id=2
ORDER BY RAND()
LIMIT 3) b
Updated:
I think it can also be done in php after running the request.
SELECT * FROM it_vit_qs ORDER BY RAND ();
Could you please help me to do this in php from mysql query result.
source to share
You can consider populating the hashmap with the strings obtained from this query:
SELECT * FROM it_vit_qs ORDER BY RAND()
When you retrieve a string, you add a tuple as a hashmap value, whose key is a combination of cat_id and level_id (for example, (cat_id * 10) + level_id). When the number of tuples for a combination reaches 5, you mark that key as terminating by adding the key to another hashmap called C.
When the number of records in C reaches 7, you stop fetching rows.
The reason your code is taking a long time is because your query makes 21 full passes on it_vit_qs.
Worst case for my approach, you make one complete pass on it_vit_qs. And this remains true if you add more categories or levels.
Appendix 1 php example
<?php
$db = new mysqli(localhost, DB_USER, DB_PASSWORD, DB_NAME);
$stmt = $db->prepare("SELECT cat_id, level_id, other_id FROM it_vit_qs ORDER BY RAND()");
$stmt->execute();
$stmt->bind_result($cat_id, $level_id, $other_id);
$found = Array();
$complete = Array();
while ($stmt->fetch()) {
$key = "$cat_id:$level_id";
#if (count($found[$key]) < 5) { # this might suffice
if (!array_key_exists($key, $found) || count($found[$key]) < 5) {
# if it_vit_qs has more than 3 columns, then push a list instead of a scalar
if (count($found[$key]) == 0) {
$found[$key] = Array();
}
array_push($found[$key], $other_id);
} else {
$complete[$key] = 1;
if (count($complete) == 7) {
break;
}
}
}
print_r($found);
?>
Appendix 2
<?php
$db = new mysqli(localhost, DB_USER, DB_PASSWORD, DB_NAME);
$stmt = $db->prepare("SELECT cat_id, level_id, other_id FROM it_vit_qs ORDER BY RAND()");
$stmt->execute();
$stmt->bind_result($cat_id, $level_id, $other_id);
$found = Array();
$complete = Array();
while ($stmt->fetch()) {
$key = "$cat_id:$level_id";
if (!array_key_exists($key, $found)) {
$found[$key] = Array();
}
if (count($found[$key]) < 5) {
array_push($found[$key], $other_id);
} else {
$complete[$key] = 1;
if (count($complete) == 7) {
break;
}
}
}
print_r($found);
?>
source to share