Forum Moderators: phranque
Each time I load the data I'm performing a while loop with a specific query to the database. This looks and feels like a lot of processing overhead.
I'm considering an alternative method, loading the info from the database one time, into an array, then populating each form from the array. (I've toyed with this idea a little, and have more to learn about php array processing yet.)
A few statistics. The database contains over 12K records. Each form contains appx 300+ items. Each query is specific to the form. Here's a sample query:
$sql = "SELECT * FROM table WHERE status = 'A' AND prodgrp = '11' AND prodtyp = ' ' AND prodqty = '5' ORDER BY desclong";
I would change prodgrp, prodtyp or prodqty, depending on the form.
Is there a better way to handle this amount of data and not have to run a query for each form?
I'm afraid I don't quite understand why you need to run so many queries to fill in the form info, but if you can avoid it do so!
If you give me a more in depth example I may be able to give you a better strategy.
I'm afraid I don't quite understand why you need to run so many queries to fill in the form info, but if you can avoid it do so!
It's so easy to want to say, look at the page...
...instead, let me try to explain.
I sell a widget that comes pre-packaged in 3 quantities.
You can get a pack of 5, 20 or 100. Each of those packs comes in 330 different varities. The first form will list all the varities for the 5 pack, the next form handles all the varities for the 20 pack, and so on thru the page.
If you look at my query select, the status check limits the results to only 'Active' items. Right now that's over 300 and is expected to double in the not too distant future.
The prodgrp part of the query can be one of 7 different groups. The prodtyp part of the query will be one of 3 product types. Finally, the prodqty item will be one of three available quantites for that widget.
So, each form on the page is dedicated to the specific parameters that make up the particular widget in that form.
Now for an example:
Lets say the first form is used for my 'Brown Widgets' which are sold in 5 packs. I populate a drop down box in that form that lists every variety of Brown Widget, ie., fuzzy, hairy, bald, round, flat... up to 330 varieties. That drop down box is populated from the database.
The next form then handles the same Brown Widget, except this is the 20 pack. The next form handles the same Brown Widget in 100 packs.
The next 3 forms handle Blue Widgets is the manner described for Brown Widgets. Then on to Red Widgets, Purple Widgets, etc.
On another site I've done all this differently, and it works pretty good. But on this site I have a little more latitude to experiment, so I'm trying to overcome some of the difficulties I've experienced on the first site. But the cost of overhead seems to be too high. I'm fishing for ideas about how to trim that overhead.
prodgrp prodqty prodtyp
Blue....5.......bald
Blue....5.......fuzzy
Blue....5.......hairy
Blue....20......bald
Blue....20......fuzzy
Blue....20......hairy
Blue....100.....bald
Blue....100.....fuzzy
Blue....100.....hairy
Red.....5.......bald
Red.....5.......fuzzy
Red.....5.......hairy
Red.....20......bald
Red.....20......fuzzy
Red.....20......hairy
Red.....100.....bald
Red.....100.....fuzzy
Red.....100.....hairy
// Initialize some variables:
$selections = ''; // our entire group of select lists
$prodgrp = '';
$prodqty = '';
$sql = "SELECT prodgrp, prodqty, prodtyp from products";
$rows = mysql_query($sql);
while ($row = mysql_fetch_assoc($rows)) {
if ($prodgrp <> $row['prodgrp'] OR $prodqty <> $row['prodqty']) {
// close the previous group, if any:
if ($selections) $selections .= '</select>';
// establish our new control variables:
$prodgrp = $row['prodgrp'];
$prodqty = $row['prodqty'];
// start a new select list:
$selections .= '<select name="' . $prodgrp . $prodqty . '">';
}
$selections .= '<option value="' . $prodgrp . $prodqty . '">';
$selections .= $prodgrp . $prodqty;
$selections .= '</option>';
}
// close on the last row read in the while loop, if any existed:
if ($selections) $selections .= '</select>';
I just threw this code down here, meaning it hasn't been tested, but hopefully you understand what I am trying to show here. Is this a possiblity for you grandpa?
1) your sql selects on multiple fields, make sure you have indexes on all of them.
2) other than adding new products and fixing errors, it doesn't sound like this data changes much. Should it really be processed dynamically? For the way this site sounds, I would most likely store the data in a database and generate static pages from it when the underlying data changes.
figment, I assume by static you mean all the options in a select group on the form are hard coded on the page. Then just create a new page when the data changes?
The static page takes about 45 seconds to load over a DSL connection. (OK, so right now the php version isn't much better...) Also, this is just one page that uses this db information. What I've managed to do so far by using MySql is eliminate the need to edit several pages every time we add a new widget, or even temporarily de-activate a widget if it becomes unavailable. I make copies of my loaded page in the event the db becomes unusable for any reason; I can pop in the static copy. For a comparison, the php version of this page is 45Kb, the static version of the same page is 896Kb. Seems like a comparison of apples to oranges, since when the page loads it will load all 896K no matter what source it comes from...
I know all the fields I use for my selects are not indexed. So maybe before I get too much farther along I'll add some indexes and see how much of a performance boost I can get.
I appreciate the feedback. Now to get to work.
At 896kb, I think you want to save wherever you can.
For example, I'd things like
1) drop closing option tags </option>
2) drop single and double quotes
3) drop value paramaters on the options and read the text label or index
4) get rid of most formatting character (newlines, tabs, spaces)
These tactics make for crappy HTML but you can get pages to load faster.
coopster, I started from the base you provided, and several hours later I have something that looks completely different. Go figure...
It seems the most critical part to this page is the query itself. I ordered my data by the qty, group, and type , then in my while loop I used if, elseif statements to build arrays. Each array contains the option groups for every widget, based on a unique combination of qty, group & type.
Here's what it looks like:
$sql = "SELECT * from products WHERE status = 'A' ORDER BY prodqty, prodgrp, prodtyp ";
$rows = mysql_query($sql);
while ($row = mysql_fetch_assoc($rows)) {
$wkqty = $row['prodqty'];
if ($wkqty == '1') {
$arrA[$wkc] = "<option stuff here>and here</option>";
$wkc ++;
}
else if ($wkqty == '5') {
$arrB[$wkc] = "<option stuff here>and here</option>";
$wkc ++;
}
//more else if here, for each qty, group, type combination
} //end while
Then, in each form, I've placed this line of code:
foreach ($arrA as $val) echo " $val ";
It's just a matter of matching up the arrays to the forms.
I don't think this is quite as elegant as it was supposed to turn out? Since I'm building 24 arrays now, I assume all that data is being stored in memory on my server? Is there a parameter for MySQL that can bite me if say, 10 people are surfing the page at the same time? 20 people?
While I almost completely understand coopsters solution, one thing I haven't figured out yet is how to present the right $selections to a specific form. I'm going to try to tackle it again. I was beginning to see it work when I got blindsided by those arrays :-)
Oh, the database is indexed in the same way I use ORDER BY in the select statement. First index is qty, then group, then type.
