...

View Full Version : Efficiency question



Gremlyn1
09-08-2009, 06:46 PM
I am working on an FAQ section for my work's customer service portal and am wondering what method would be more efficiency. In my database I have a table of products, and each product has a field with a string of numbers in it that corresponds to the id number of the FAQ. These id's are arranged in the order I want them displayed. For those visual people:

product | faq_order
Product 1 | 5,6,4,3
Product 2 | 2,9,6
Product 3 | 1,8,6,7

Also, in the FAQ table, each currently has a field that relates it to the product(s) it applies to (using a product id).

I need to display the FAQ's in that order on the admin side and then all un-ordered FAQs (not all are necessarily shown to the user) so that they can add them to the shown FAQs if needed. I can conceive of two ways to do that...

#1 is to explode 'faq_order' into an array, and use a foreach loop to go through and make a mysql call for the approrpiate FAQ id number, then output the appropriate info until there are no more. Then tack on the rest of the unordered FAQs with a call that pulls the unordered FAQs that have the product id and display those.

#2 is to pull all FAQs related to the selected product into a multidimensional array (using the yet-to-be-needed field in the FAQ table I mentioned above), and then pick out the component arrays from inside the master array as needed in order, again using an explode of the 'faq_order'.

I'm sure there are other ways, if you have a better one, please let me know, otherwise what would be more efficient of my two options? There will be maybe 10-20 FAQ's pulled at a time most likely.

mr e
09-08-2009, 08:17 PM
It sounds like #1 you'll be making a query for every faq you want, whereas in #2 you'll make one query for all the faqs then pick them out of an array. If there won't be many faqs then I would go with #2, sounds like the simpler option to code for too.

On another note, if you're going to want to be able to manage these faq options from a "config" page or some sort on your website you might want to look into normalizing that table so you won't be pulling comma delimited information out of one field.

Though if you don't have many faqs then even that probably won't make too much of a difference.

All in all, unless your faq table is going to be huge, I would pick the one that keeps the code cleanest and most readable as I personally wouldn't worry about the minor efficiency differences between your options (again as long as the faqs aren't huge in number).

Gremlyn1
09-08-2009, 10:17 PM
It sounds like #1 you'll be making a query for every faq you want, whereas in #2 you'll make one query for all the faqs then pick them out of an array. If there won't be many faqs then I would go with #2, sounds like the simpler option to code for too.

On another note, if you're going to want to be able to manage these faq options from a "config" page or some sort on your website you might want to look into normalizing that table so you won't be pulling comma delimited information out of one field.

Though if you don't have many faqs then even that probably won't make too much of a difference.

All in all, unless your faq table is going to be huge, I would pick the one that keeps the code cleanest and most readable as I personally wouldn't worry about the minor efficiency differences between your options (again as long as the faqs aren't huge in number).

The table is going to be relatively small. Should be 10-15 products with 10-20 FAQ's per product (some over lapping). I am going with something a little different than my two options above, i think. I was talking with a friend and fellow coder and we came up with make two database queries when the product is loaded.

The first would be to pull all the data for the FAQs that are going to be listed in a specified order, then the second will pull the remaining FAQs that apply to the product but are not in use. With the small-ish amount of data I shouldn't notice to much difference in query times and i think this will be the cleanest to code in.

Gremlyn1
09-08-2009, 10:50 PM
OK, my code mockup is looking something like below. I'm not really that savvy with MYSQL, and a little better with PHP, so I figured I'd post this to see if anyone can pick out any glaring issues.


$faq_sql1 = mysql_query("SELECT * FROM techinfo WHERE id IN " .
"(SELECT faq_order FROM products AS faqord WHERE id ='$mdl' LIMIT 1) " .
"ORDER BY find_in_set(id, 'faqord')") or die(mysql_error());
// Will output FAQs in set order
while ($row = mysql_fetch_array($faq_sql1)){
echo "Q: {$row['issue']} <br />";
echo "A: {$row['answer']} <br />";
}
$faq_sql2 = mysql_query("SELECT * FROM techinfo WHERE id IN " .
"(SELECT products FROM techinfo) AND id NOT IN" .
"(SELECT faq_order FROM products AS faqord WHERE id ='$mdl')") or die(mysql_error());
// Will output FAQs applicable to product not output above
while ($row = mysql_fetch_array($faq_sql2)){
echo "Q: {$row['issue']} <br />";
echo "A: {$row['answer']} <br />";
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum