Buy Now
Sign in

Get random ids from the database

rafaelscouto , November 20 2020, 19:14
R
rafaelscouto 212
November 20 2020, 19:14 #394961
Good afternoon

I'm trying to create a quiz (questions and answers).

But I'm not sure how to do the front end.

I would like to take 5 questions from the database at random. How can I do this with the CC6?

Each question has 4 choices, one correct and the rest wrong. I would also like these choices to be random.
R
rafaelscouto 212
November 20 2020, 20:58 #394963
It seems to me that the read_data function has no way of using ORDER BY RAND ()

In this case I had to read the database using a custom code, the one below:

<? php
$ db = JFactory :: getDBO ();
$ query = $ db-> getQuery (true);
$ query-> select ('*')
-> from ('test_quiz_questao ORDER BY RAND () LIMIT 5');
$ db-> setQuery ($ query);
$ data = $ db-> loadObjectList ();

foreach ($ data as $ detail):
?>
<table>
<tr>
<th> question_id </th>
</tr>
<tr>
<td> <? php echo $ detail-> question_id; ?> </td>
<? php endforeach; ?>
</tr>
</table>

But now I need to do this here below in my code above, and I didn't quite understand how to do it:

[0] => SELECT `Quiz`.`id_questao` AS` Quiz.id_questao`, `Quiz`.`created` AS` Quiz.created`, `Quiz`.`modified` AS` Quiz.modified`, `Quiz `.`nunciado` AS` Quiz.enunciado`, `Quiz`.`justificativa` AS` Quiz.justificativa`, `Quiz`.`url_image` AS` Quiz.url_image` FROM `teste_quiz_questao` AS` Quiz`;
[1] => SELECT `Choi`.`id_escolha` AS` Choi.id_escolha`, `Choi`.`id_questao` AS` Choi.id_questao`, `Choi`.`created` AS` Choi.created`, `Choi `.`modified` AS` Choi.modified`, `Choi`.` and correct` AS` Choi.e_correto`, `Choi`.`text` AS` Choi.texto` FROM `teste_quiz_escolha` AS` Choi` WHERE `Choi `.`id_questao` IN ('12', '33', '43', '44', '45', '46', '47', '48', '49', '50', '51' , '52', '53', '54', '55', '56');
R
rafaelscouto 212
November 20 2020, 21:32 #394964
How do I do this within chronoconnectivity

This looks like it can help solve my problem
R
rafaelscouto 212
November 22 2020, 18:52 #394988
Good afternoon guys, I'm working on a Quiz for a joomla site and I'm trying to get some data from the DB.
I have a table "teste_quiz_questao" with the columns: id_questao, statement and justification
In another "test_quiz_escolha" table, I store 4 options of choice for the same question_id. In it I have the
columns: choice_id, question_id, text, correct.
I need to load the 4 options of choice within their respective question. I believe that the best way to do this
it will be using a WHERE IN SELECT, but I don't know how to do it, can someone guide me where I'm going wrong?
In this case, var_dump only returns the quiz_quiz
I'm using the following code:
<?php
$db = JFactory::getDbo()
$query = $db->getQuery(true);
$query->select('Quiz.*');
$query->from($db->quoteName('teste_quiz_questao', 'Quiz'));
$query->where($db->quoteName('Quiz.id_questao') . ' IN (SELECT' . $db->quoteName('Choi.id_questao') . ' FROM ' . $db->quoteName('teste_quiz_escolha', 'Choi') . ')');
$query->order('rand() limit 1');
$db->setQuery($query);
$results = $db->loadAssocList();
var_dump($results);
R
rafaelscouto 212
November 24 2020, 23:25 #395027
If I use the code below, how do I display the data from the database within chronoconnectivity. For example inside an input.
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);


$query
->select(array('questao.id_questao'))
->from($db->qn('teste_quiz_questao', 'questao'));
$db->setQuery($query);


$idList = $db->loadAssocList('id_questao', 'id_questao');

$query = $db->getQuery(true);

$query
->select(array('questao.id_questao', 'questao.enunciado', 'escolha.texto', 'escolha.e_correto'))
->from($db->qn('teste_quiz_questao', 'questao'))
->join('LEFT', $db->qn('teste_quiz_escolha', 'escolha') . ' ON ' . $db->qn('questao.id_questao') . ' = ' . $db->qn('escolha.id_questao'))
->where($db->qn('questao.id_questao') . ' IN (' . implode(',', array_rand($idList,5)) . ')' );

$db->setQuery($query);

$results = $db->loadAssocList();


$listaQuestao = array();

foreach($results as $row) {

if(!array_key_exists($row['id_questao'], $listaQuestao)){

$escolha = array(
"correto" => $row['e_correto'],
"texto" => $row['texto']
);

$questao = array(
"id" => $row['id_questao'],
"enunciado" => $row['enunciado'],
"escolhas" => array($escolha)
);

$listaQuestao[$row['id_questao']] = $questao;
}else {

$escolha = array(
"correto" => $row['e_correto'],
"texto" => $row['texto']
);
array_push($listaQuestao[$row['id_questao']]['escolhas'], $escolha);
shuffle($listaQuestao[$row['id_questao']]['escolhas']);
}
}

echo '<pre>';
print_r($listaQuestao);
echo '</pre>';
admin 30
November 27 2020, 14:58 #395064
 Answer
Remove the first <?php tag then use that code in a PHP action then return the value you want to use at the end of the code:
return $listaQuestao;
Then use {var:php_fn_name} anywhere to call that variable, if it's an array then you can get sub values:
{var:php_name.sub.value}