mysql lookup multiple times - or use array?

mfacer

Hi guys...

I've got a form on my page which has around 50 drop down (select) boxes. It's a page of answers - as I output each question I use a mysql to see if the user has answers that particular question. If so, show their answer in the drop down.

It takes a long time to load when there are a lot of questions.... my question is should I do ONE sql at the beginning and store the result in an array, then loop through the array each time I load the question to see if there's an answer?

eg:

[sql]
#output the question
while ($row = mysql_fetch_array($question_res)) {
echo $row['question'] . " ";
$answer_res = mysql_query("SELECT * from answers WHERE question_id=".$row['question_id']."");
if (mysql_num_rows($answer_res) > 0) {
$answer = $row['answer'];
}
}
[/sql]

the above example is just something ive written now to show kinda how I get the answers. Would it be faster if I replaced the sql lookup for EACH question with an array loop??

So at the start, I'd get ALL the answers for this page then compare?

Any help is much appreciated! 🙂

Piranha

The universal answer is: Try it and find out.

To be a little more helpful: Normally it is faster to do one query than to do queries in a loop. I can't say for sure in your case, but I would go for one query. Another thing that will speed it up is to index the columns you use in the where-statement and use SELECT COUNT(*) when you only want the number of rows returned. Notice that you need to get the value and not mysql_num_rows when you use COUNT.

MarkR

Personally I'd say:

Do whatever is more convenient to write / lets you reuse more code etc
Measure the performance quantitatively
If it's found to be insufficient, refactor your implementation as necessary.

Mark

mfacer

thanks guys - I just wondered if there was a set way to do it - seems there is no right or wrong way... I'll set up some timers and see what's most effective. I'm guessing if I could do it all with one SQL statement it'd be waaay more spedly! (is that a word?!)

Would a giant join be any good?

eg:

GET QUESTION (question, question_id)
JOIN answers on question_id
GROUP BY question_id

OUTPUT question, if answer, select that.

mfacer

ok I've actually got the answers.... trouble is I cannot group otherwise it misses the answers.... is it bad? eg: the results are looking like

question_id question answer
1 invoices processed future|100
1 invoices processed current|43
2 staff in sales future|2342
2 staff in sales current|3421

so basically, the way it's set up is that there are two options for each "question". The user gives an answer as to the future and current answer. If I group by question_id it just returns on answer row... any ideas how i could display this data?

looping through checking if I've already got the question... no idea!

Piranha

Insert another column, with one value for future and one for current.

Piranha

I came to think of another way. You can use WHERE column LIKE '%future'. It will be much slower than to use a column to mark, but it is a possibility.