Hi,

I'm struggling to figure something out. What I have are thousands of
questions from 5 books of the bible in a database. The questions are
labeled with the book name, chapter, verse and type of question as
follows: X, B, G, A, M, R. I need to be able to select 11 questions of
the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B. I need to select them all
randomly and display them randomly. I want to give user the option to
change the range of books and chapter from which the questions are drawn
from and the number of each type they want.


I have this mysql code:

"SELECT * FROM questions WHERE book IN ('Galatians',
'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1',
'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"

This selects all the general questions (type g) the three books and
their chapters and then randomizes them and selects the 11 I need of
type G.

I can run this query six times, one for each type but I'm still not
displaying them to the user in random order. In other words with a while
loop and mysql_fetch_array() I get 11 general questions followed by 5 of
A type and so on. How can take the six queries, and put them all into
an array that can be randomized and still keep the array key/value pair
as taken from the database.

I have a sample form of what options I want to give the user when
generating a set of questions. The only thing you can't find on the form
is that each set (1 page) can have a maximum of 20 questions. The form
can be found at http://athleo.net/sampleForm.php

I hope I haven't confused anyone, any help would be greatly appreciated.

On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:

> Hi,
>
> I'm struggling to figure something out. What I have are thousands of =
=

> questions from 5 books of the bible in a database. The questions are =

> labeled with the book name, chapter, verse and type of question as =

> follows: X, B, G, A, M, R. I need to be able to select 11 questions of=
=

> the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B. I need to select them all =
=

> randomly and display them randomly. I want to give user the option to =
=

> change the range of books and chapter from which the questions are dra=
wn =

> from and the number of each type they want.
>
>
> I have this mysql code:
>
> "SELECT * FROM questions WHERE book IN ('Galatians', =

> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1', =

> 'E5', 'P1') type=3D'G' ORDER BY RAND() LIMIT 11)"


Are you sure? I miss an ' AND ' before 'type'?
It should work IMHO, if it doesn't, ask in comp.databases.<name of your =
db>
-- =

Rik Wasmus

Rik wrote:
> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:
>
>> Hi,
>>
>> I'm struggling to figure something out. What I have are thousands of
>> questions from 5 books of the bible in a database. The questions are
>> labeled with the book name, chapter, verse and type of question as
>> follows: X, B, G, A, M, R. I need to be able to select 11 questions of
>> the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B. I need to select them all
>> randomly and display them randomly. I want to give user the option to
>> change the range of books and chapter from which the questions are
>> drawn from and the number of each type they want.
>>
>>
>> I have this mysql code:
>>
>> "SELECT * FROM questions WHERE book IN ('Galatians',
>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1',
>> 'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"
>
>
> Are you sure? I miss an ' AND ' before 'type'?
> It should work IMHO, if it doesn't, ask in comp.databases.<name of your db>
> --Rik Wasmus

Rick, your right, I should have copy and pasted but I was didn't feel
like opening the document again.

I added the 'AND' in there but notice how I run the same query but
change the "type='G'" to "type='A'" and I change the number "LIMIT" to
5. I then use a 'while loop' with 'mysql_fetch_array() to print out the
results to the screen. The problem I have is that I use a loop for each
query run and the result is 11 of type='G' printed followed by 5 of
type='A' printed to the screen, all in a row. I want the final result
from all the six queries to be printed to the screen in random order.


$sql = "SELECT * FROM questions WHERE book IN ('Galatians',
'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1','E5',
'P1') AND type='G' ORDER BY RAND() LIMIT 11)"

$sql2 = "SELECT * FROM questions WHERE book IN ('Galatians',
'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1','E5',
'P1') AND type='A' ORDER BY RAND() LIMIT 5)"

zach wrote:
> Rik wrote:
>> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I'm struggling to figure something out. What I have are thousands of
>>> questions from 5 books of the bible in a database. The questions are
>>> labeled with the book name, chapter, verse and type of question as
>>> follows: X, B, G, A, M, R. I need to be able to select 11 questions
>>> of the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B. I need to select them
>>> all randomly and display them randomly. I want to give user the
>>> option to change the range of books and chapter from which the
>>> questions are drawn from and the number of each type they want.
>>>
>>>
>>> I have this mysql code:
>>>
>>> "SELECT * FROM questions WHERE book IN ('Galatians',
>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1',
>>> 'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"
>>
>>
>> Are you sure? I miss an ' AND ' before 'type'?
>> It should work IMHO, if it doesn't, ask in comp.databases.<name of
>> your db>
>> --Rik Wasmus
>
> Rick, your right, I should have copy and pasted but I was didn't feel
> like opening the document again.
>
> I added the 'AND' in there but notice how I run the same query but
> change the "type='G'" to "type='A'" and I change the number "LIMIT" to
> 5. I then use a 'while loop' with 'mysql_fetch_array() to print out the
> results to the screen. The problem I have is that I use a loop for each
> query run and the result is 11 of type='G' printed followed by 5 of
> type='A' printed to the screen, all in a row. I want the final result
> from all the six queries to be printed to the screen in random order.
>
>
> $sql = "SELECT * FROM questions WHERE book IN ('Galatians',
> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1','E5',
> 'P1') AND type='G' ORDER BY RAND() LIMIT 11)"
>
> $sql2 = "SELECT * FROM questions WHERE book IN ('Galatians',
> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1','E5',
> 'P1') AND type='A' ORDER BY RAND() LIMIT 5)"

If you use:

while ($query_1_data[] = mysql_fetch_arrray(...))
{
}
while ($query_2_data[] = mysql_fetch_arrray(...))
{
}
while ($query_3_data[] = mysql_fetch_arrray(...))
{
}

// you now have three arrays like: $query_?_data[0]...[n]
// merge the arrays together to form one big array
// then shuffle (randomize) them

$questions = array_merge($query_1_data, $query_2_data, $query_3_data);
shuffle($questions);

// now you can loop through them to display them

for ($loop = 0; $loop <= count($questions)-1; $loop++)
{
echo $question[$loop][...]; // add your necessary keys here
}

Norm

Norman Peelman wrote:
> zach wrote:
>> Rik wrote:
>>> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm struggling to figure something out. What I have are thousands
>>>> of questions from 5 books of the bible in a database. The questions
>>>> are labeled with the book name, chapter, verse and type of question
>>>> as follows: X, B, G, A, M, R. I need to be able to select 11
>>>> questions of the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B. I need to
>>>> select them all randomly and display them randomly. I want to give
>>>> user the option to change the range of books and chapter from which
>>>> the questions are drawn from and the number of each type they want.
>>>>
>>>>
>>>> I have this mysql code:
>>>>
>>>> "SELECT * FROM questions WHERE book IN ('Galatians',
>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1',
>>>> 'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"
>>>
>>>
>>> Are you sure? I miss an ' AND ' before 'type'?
>>> It should work IMHO, if it doesn't, ask in comp.databases.<name of
>>> your db>
>>> --Rik Wasmus
>>
>> Rick, your right, I should have copy and pasted but I was didn't feel
>> like opening the document again.
>>
>> I added the 'AND' in there but notice how I run the same query but
>> change the "type='G'" to "type='A'" and I change the number "LIMIT" to
>> 5. I then use a 'while loop' with 'mysql_fetch_array() to print out
>> the results to the screen. The problem I have is that I use a loop for
>> each query run and the result is 11 of type='G' printed followed by 5
>> of type='A' printed to the screen, all in a row. I want the final
>> result from all the six queries to be printed to the screen in random
>> order.
>>
>>
>> $sql = "SELECT * FROM questions WHERE book IN ('Galatians',
>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>> 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)"
>>
>> $sql2 = "SELECT * FROM questions WHERE book IN ('Galatians',
>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>> 'E1','E5', 'P1') AND type='A' ORDER BY RAND() LIMIT 5)"
>
> If you use:
>
> while ($query_1_data[] = mysql_fetch_arrray(...))
> {
> }
> while ($query_2_data[] = mysql_fetch_arrray(...))
> {
> }
> while ($query_3_data[] = mysql_fetch_arrray(...))
> {
> }
>
> // you now have three arrays like: $query_?_data[0]...[n]
> // merge the arrays together to form one big array
> // then shuffle (randomize) them
>
> $questions = array_merge($query_1_data, $query_2_data, $query_3_data);
> shuffle($questions);
>
> // now you can loop through them to display them
>
> for ($loop = 0; $loop <= count($questions)-1; $loop++)
> {
> echo $question[$loop][...]; // add your necessary keys here
> }
>
> Norm

Ooops, use mysql_fetch_assoc($result) or
mysql_fetch_array($result,MYSQL_ASSOC)


Norm

Norman Peelman wrote:
> Norman Peelman wrote:
>> zach wrote:
>>> Rik wrote:
>>>> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I'm struggling to figure something out. What I have are thousands
>>>>> of questions from 5 books of the bible in a database. The questions
>>>>> are labeled with the book name, chapter, verse and type of question
>>>>> as follows: X, B, G, A, M, R. I need to be able to select 11
>>>>> questions of the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B. I need to
>>>>> select them all randomly and display them randomly. I want to give
>>>>> user the option to change the range of books and chapter from which
>>>>> the questions are drawn from and the number of each type they want.
>>>>>
>>>>>
>>>>> I have this mysql code:
>>>>>
>>>>> "SELECT * FROM questions WHERE book IN ('Galatians',
>>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3', 'E1',
>>>>> 'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"
>>>>
>>>>
>>>> Are you sure? I miss an ' AND ' before 'type'?
>>>> It should work IMHO, if it doesn't, ask in comp.databases.<name of
>>>> your db>
>>>> --Rik Wasmus
>>>
>>> Rick, your right, I should have copy and pasted but I was didn't feel
>>> like opening the document again.
>>>
>>> I added the 'AND' in there but notice how I run the same query but
>>> change the "type='G'" to "type='A'" and I change the number "LIMIT"
>>> to 5. I then use a 'while loop' with 'mysql_fetch_array() to print
>>> out the results to the screen. The problem I have is that I use a
>>> loop for each query run and the result is 11 of type='G' printed
>>> followed by 5 of type='A' printed to the screen, all in a row. I want
>>> the final result from all the six queries to be printed to the screen
>>> in random order.
>>>
>>>
>>> $sql = "SELECT * FROM questions WHERE book IN ('Galatians',
>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>> 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)"
>>>
>>> $sql2 = "SELECT * FROM questions WHERE book IN ('Galatians',
>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>> 'E1','E5', 'P1') AND type='A' ORDER BY RAND() LIMIT 5)"
>>
>> If you use:
>>
>> while ($query_1_data[] = mysql_fetch_arrray(...))
>> {
>> }
>> while ($query_2_data[] = mysql_fetch_arrray(...))
>> {
>> }
>> while ($query_3_data[] = mysql_fetch_arrray(...))
>> {
>> }
>>
>> // you now have three arrays like: $query_?_data[0]...[n]
>> // merge the arrays together to form one big array
>> // then shuffle (randomize) them
>>
>> $questions = array_merge($query_1_data, $query_2_data, $query_3_data);
>> shuffle($questions);
>>
>> // now you can loop through them to display them
>>
>> for ($loop = 0; $loop <= count($questions)-1; $loop++)
>> {
>> echo $question[$loop][...]; // add your necessary keys here
>> }
>>
>> Norm
>
> Ooops, use mysql_fetch_assoc($result) or
> mysql_fetch_array($result,MYSQL_ASSOC)
>
>
> Norm

Thank you, I'm still learning PHP and you have no idea how much that was
frustrating me. Now I can actually sleep, lol. Thanks again.

zach wrote:
> Norman Peelman wrote:
>> Norman Peelman wrote:
>>> zach wrote:
>>>> Rik wrote:
>>>>> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I'm struggling to figure something out. What I have are thousands
>>>>>> of questions from 5 books of the bible in a database. The
>>>>>> questions are labeled with the book name, chapter, verse and type
>>>>>> of question as follows: X, B, G, A, M, R. I need to be able to
>>>>>> select 11 questions of the G type, 5 of A, 1 M, 1 R, 1 X, AND 1 B.
>>>>>> I need to select them all randomly and display them randomly. I
>>>>>> want to give user the option to change the range of books and
>>>>>> chapter from which the questions are drawn from and the number of
>>>>>> each type they want.
>>>>>>
>>>>>>
>>>>>> I have this mysql code:
>>>>>>
>>>>>> "SELECT * FROM questions WHERE book IN ('Galatians',
>>>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>>>>> 'E1', 'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"
>>>>>
>>>>>
>>>>> Are you sure? I miss an ' AND ' before 'type'?
>>>>> It should work IMHO, if it doesn't, ask in comp.databases.<name of
>>>>> your db>
>>>>> --Rik Wasmus
>>>>
>>>> Rick, your right, I should have copy and pasted but I was didn't
>>>> feel like opening the document again.
>>>>
>>>> I added the 'AND' in there but notice how I run the same query but
>>>> change the "type='G'" to "type='A'" and I change the number "LIMIT"
>>>> to 5. I then use a 'while loop' with 'mysql_fetch_array() to print
>>>> out the results to the screen. The problem I have is that I use a
>>>> loop for each query run and the result is 11 of type='G' printed
>>>> followed by 5 of type='A' printed to the screen, all in a row. I
>>>> want the final result from all the six queries to be printed to the
>>>> screen in random order.
>>>>
>>>>
>>>> $sql = "SELECT * FROM questions WHERE book IN ('Galatians',
>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>>> 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)"
>>>>
>>>> $sql2 = "SELECT * FROM questions WHERE book IN ('Galatians',
>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>>> 'E1','E5', 'P1') AND type='A' ORDER BY RAND() LIMIT 5)"
>>>
>>> If you use:
>>>
>>> while ($query_1_data[] = mysql_fetch_arrray(...))
>>> {
>>> }
>>> while ($query_2_data[] = mysql_fetch_arrray(...))
>>> {
>>> }
>>> while ($query_3_data[] = mysql_fetch_arrray(...))
>>> {
>>> }
>>>
>>> // you now have three arrays like: $query_?_data[0]...[n]
>>> // merge the arrays together to form one big array
>>> // then shuffle (randomize) them
>>>
>>> $questions = array_merge($query_1_data, $query_2_data, $query_3_data);
>>> shuffle($questions);
>>>
>>> // now you can loop through them to display them
>>>
>>> for ($loop = 0; $loop <= count($questions)-1; $loop++)
>>> {
>>> echo $question[$loop][...]; // add your necessary keys here
>>> }
>>>
>>> Norm
>>
>> Ooops, use mysql_fetch_assoc($result) or
>> mysql_fetch_array($result,MYSQL_ASSOC)
>>
>>
>> Norm
>
> Thank you, I'm still learning PHP and you have no idea how much that was
> frustrating me. Now I can actually sleep, lol. Thanks again.

Do you have it working?

Norm

Norman Peelman wrote:
> zach wrote:
>> Norman Peelman wrote:
>>> Norman Peelman wrote:
>>>> zach wrote:
>>>>> Rik wrote:
>>>>>> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo [at] gmail.com> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I'm struggling to figure something out. What I have are
>>>>>>> thousands of questions from 5 books of the bible in a database.
>>>>>>> The questions are labeled with the book name, chapter, verse and
>>>>>>> type of question as follows: X, B, G, A, M, R. I need to be able
>>>>>>> to select 11 questions of the G type, 5 of A, 1 M, 1 R, 1 X, AND
>>>>>>> 1 B. I need to select them all randomly and display them
>>>>>>> randomly. I want to give user the option to change the range of
>>>>>>> books and chapter from which the questions are drawn from and the
>>>>>>> number of each type they want.
>>>>>>>
>>>>>>>
>>>>>>> I have this mysql code:
>>>>>>>
>>>>>>> "SELECT * FROM questions WHERE book IN ('Galatians',
>>>>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>>>>>> 'E1', 'E5', 'P1') type='G' ORDER BY RAND() LIMIT 11)"
>>>>>>
>>>>>>
>>>>>> Are you sure? I miss an ' AND ' before 'type'?
>>>>>> It should work IMHO, if it doesn't, ask in comp.databases.<name of
>>>>>> your db>
>>>>>> --Rik Wasmus
>>>>>
>>>>> Rick, your right, I should have copy and pasted but I was didn't
>>>>> feel like opening the document again.
>>>>>
>>>>> I added the 'AND' in there but notice how I run the same query but
>>>>> change the "type='G'" to "type='A'" and I change the number "LIMIT"
>>>>> to 5. I then use a 'while loop' with 'mysql_fetch_array() to print
>>>>> out the results to the screen. The problem I have is that I use a
>>>>> loop for each query run and the result is 11 of type='G' printed
>>>>> followed by 5 of type='A' printed to the screen, all in a row. I
>>>>> want the final result from all the six queries to be printed to the
>>>>> screen in random order.
>>>>>
>>>>>
>>>>> $sql = "SELECT * FROM questions WHERE book IN ('Galatians',
>>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>>>> 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)"
>>>>>
>>>>> $sql2 = "SELECT * FROM questions WHERE book IN ('Galatians',
>>>>> 'Ephesians','Philippians') AND chapters IN ('G1', 'G2', 'G3',
>>>>> 'E1','E5', 'P1') AND type='A' ORDER BY RAND() LIMIT 5)"
>>>>
>>>> If you use:
>>>>
>>>> while ($query_1_data[] = mysql_fetch_arrray(...))
>>>> {
>>>> }
>>>> while ($query_2_data[] = mysql_fetch_arrray(...))
>>>> {
>>>> }
>>>> while ($query_3_data[] = mysql_fetch_arrray(...))
>>>> {
>>>> }
>>>>
>>>> // you now have three arrays like: $query_?_data[0]...[n]
>>>> // merge the arrays together to form one big array
>>>> // then shuffle (randomize) them
>>>>
>>>> $questions = array_merge($query_1_data, $query_2_data, $query_3_data);
>>>> shuffle($questions);
>>>>
>>>> // now you can loop through them to display them
>>>>
>>>> for ($loop = 0; $loop <= count($questions)-1; $loop++)
>>>> {
>>>> echo $question[$loop][...]; // add your necessary keys here
>>>> }
>>>>
>>>> Norm
>>>
>>> Ooops, use mysql_fetch_assoc($result) or
>>> mysql_fetch_array($result,MYSQL_ASSOC)
>>>
>>>
>>> Norm
>>
>> Thank you, I'm still learning PHP and you have no idea how much that
>> was frustrating me. Now I can actually sleep, lol. Thanks again.
>
> Do you have it working?
>
> Norm

Yes, it worked great, except I had to add array_pop() after the loop
like this:

while ($query_1_data[] = mysql_fetch_arrray(...))
{
}

array_pop($query_1_data);

because it left the last element of the array blank, and with six arrays
I was ending up with 6 blank elements when I merged them, but now it
works great.

zach

..oO(zach)

>Yes, it worked great, except I had to add array_pop() after the loop
>like this:
>
>while ($query_1_data[] = mysql_fetch_arrray(...))
>{
>}
>
>array_pop($query_1_data);
>
>because it left the last element of the array blank, and with six arrays
>I was ending up with 6 blank elements when I merged them, but now it
>works great.

You can drop array_pop() if you do the looping and assignment properly.
mysql_fetch_*() will return FALSE if there are no more values left in
the result set. The code above still appends that FALSE value to the
array, which you have to remove afterwards. Consider that bad style.

Better:

while ($record = mysql_fetch_array(...)) {
$query_1_data[] = $record;
}

With the last return from mysql_fetch_array() the loop will terminate,
before appending the FALSE to the array. So there's no need for an
array_pop() anymore.

Micha

Michael Fesser wrote:
> .oO(zach)
>
>> Yes, it worked great, except I had to add array_pop() after the loop
>> like this:
>>
>> while ($query_1_data[] = mysql_fetch_arrray(...))
>> {
>> }
>>
>> array_pop($query_1_data);
>>
>> because it left the last element of the array blank, and with six arrays
>> I was ending up with 6 blank elements when I merged them, but now it
>> works great.
>
> You can drop array_pop() if you do the looping and assignment properly.
> mysql_fetch_*() will return FALSE if there are no more values left in
> the result set. The code above still appends that FALSE value to the
> array, which you have to remove afterwards. Consider that bad style.
>
> Better:
>
> while ($record = mysql_fetch_array(...)) {
> $query_1_data[] = $record;
> }
>
> With the last return from mysql_fetch_array() the loop will terminate,
> before appending the FALSE to the array. So there's no need for an
> array_pop() anymore.
>
> Micha

If that's the case then cool... i've never run into that problem
before, that I can recall.

Norm