views:

328

answers:

3

Hey, I'm making an e-shop and to display the tree of categories and all the products with their multiple variations of prices I made like more than 150 mysql_query("SELECT ..."); queries on one page. (If I count the "while" loops).

Is it too many, and if yes, can it have any negative effect? (ofc. it takes longer to load the data ..)

Also can I anyhow achieve the effect of this code without doing it that way?

$result2 = mysql_query("SELECT * FROM ceny WHERE produkt_id='$id' ORDER BY gramaz");
$result3 = mysql_query("SELECT * FROM ceny WHERE produkt_id='$id' ORDER BY gramaz");
$result4 = mysql_query("SELECT * FROM ceny WHERE produkt_id='$id' ORDER BY gramaz");
$result5 = mysql_query("SELECT * FROM ceny WHERE produkt_id='$id' ORDER BY gramaz");

while( $row2 = mysql_fetch_array( $result2 )) { }
while( $row3 = mysql_fetch_array( $result2 )) { }
while( $row4 = mysql_fetch_array( $result2 )) { }
while( $row5 = mysql_fetch_array( $result2 )) { }

Thanks, Mike.

+12  A: 

It's usually a good idea to reduce the number of queries you run. In this case you can select rows for all products in a single query, for example:

SELECT * FROM ceny WHERE produkt_id IN (?, ?, ?, ...) ORDER BY gramaz

Then you will have a single loop that iterates over the results and populates the appropriate variable(s):

$ceny = array()
while ($row = mysql_fetch_array($result)) {
    $id = $row['produkt_id'];
    $ceny[$id][] = $row;
}

Now $ceny[$produkt_id] contains a list of rows from ceny for that product, sorted by gramaz.

Lukáš Lalinský
+1 Also you could order by `produkt_id` and then by `gramaz` to get the results in the same order as you would be multiple queries.
Bill Karwin
A: 

Are you really querying each product separately? If so, that would be bad. You should query all products at once or at least in big batches and then loop through them and split them into different arrays.

What exactly is your 'while' loop doing, if every resultset contains only one row???

You can also use the WHERE IN clause but only if you can make sure the list of ids is not hard coded. You don't want to hard code any selectors.

SELECT *

It is not generally a good idea to use the * selector. You should define exactly which fields you need. See these posts for more exmplanations why * is bad

tharkun
Ofc I do not use the multiple $result2,3,4,5 to view the products, but the products have multiple prices for different sizes and I need to select the prices when I'm displaing the product. Now when I use mysql_fetch_array on $result that was already fetched, it doesn't work, that's why I use it and don't know way around.
Mike
You need to select all your products once! And then deal with them in your code!
tharkun
before selecting all the product think about the table size of your table "ceny". if it is not that big then you can select all the data of that table to an array and operate on that array . if the size of the table is bigger then use "produkt_id IN".
Imrul
@Imrul: good point!
tharkun
A: 

Hello,

It seems your situation is in desperate need of a join, and possibly some AJAX if you really want to cut down on the load for that page.

If I'm understanding you correctly, you want to display a tree menu of categories, and presumably when you click to open a particular branch, you see the items which fall under that category, correct?

So, your current approach is to loop through the categories, then do a select * from the items table to show them, right?

If this is the case, and I'm understanding it correctly, this could be solved using a simple join. You would want to do a single select statement to the items table, join to the categories table by the foreign key you have in the items table, then sort by the category.

Now, you'd be able to loop through the single result set to show both the categories and the associated items.

Jason Palmer