|
|
Posted by zach on 07/23/07 03:29
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@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
Navigation:
[Reply to this message]
|