Posted by zach on 07/22/07 14:49

Norman Peelman wrote:
> Norman Peelman wrote:
>> zach wrote:
>>> Rik wrote:
>>>> On Sun, 22 Jul 2007 07:46:10 +0200, zach <wackzingo@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.

[Back to original message]