Re: PHP MYSQL QUESTION

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@xxxxxxxxx> 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
.

Relevant Pages

  • Re: PHP MYSQL QUESTION
    ... 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 then use a 'while loop' with 'mysql_fetch_arrayto print out the results to the screen. ... 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)" ... // merge the arrays together to form one big array ...
    (alt.php)
  • Re: PHP MYSQL QUESTION
    ... 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. ... 'P1') type='G' ORDER BY RAND() LIMIT 11)" ... I then use a 'while loop' with 'mysql_fetch_arrayto print out the results to the screen. ... // merge the arrays together to form one big array ...
    (alt.php)
  • Re: PHP MYSQL QUESTION
    ... 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 then use a 'while loop' with 'mysql_fetch_arrayto print out the results to the screen. ... 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)" ... // merge the arrays together to form one big array ...
    (alt.php)
  • Re: Structured Coding
    ... First, thank you for reading my books, and please do not read what follows ... like the book less than I like the language. ... concepts (such as arrays and strings). ... Programming C# assumes some experience with a related language ...
    (comp.lang.cobol)
  • Re: PHP MYSQL QUESTION
    ... 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 then use a 'while loop' with 'mysql_fetch_arrayto print out the results to the screen. ... 'E1','E5', 'P1') AND type='G' ORDER BY RAND() LIMIT 11)" ... // merge the arrays together to form one big array ...
    (alt.php)