Posted by Norman Peelman on 07/22/07 21:00

zach wrote:
> 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.

Do you have it working?

Norm

[Back to original message]