help with script and mysql optimzation

[ciber]

I have a product database, with 5 different types of products, im wanting to list each of these products under a sub header on a single page. this is how im currently, perhaps someone can give me an idea how I can optimize this.

 

execute mysql query

echo type header
while {
output data for type 1
}

reset mysql position

echo type header
while {
output data for type 2
}

reset mysql position

etc, process is repeated

 

In addition to this, the types are fixed, and wont be changing so variables were not necessary - headers are "hard coded"

any suggestions??

[ChemicalBliss]

You need to look at some "Good Design Practices" for Traditional Databases.

 

On a basic level, you would have a structure similar to this:

 

Database

Table 'products'
----------------------------------------------------------------------
|  id  |  name  |  category_id  |  price  |  description  |  weight  |
----------------------------------------------------------------------
*category_id 	- Reference ID for which category this product belongs to.

Table 'categories'
-------------------------------
|  id  |  name  |  parent_id  |
-------------------------------
*id			- These are the Category IDs (to put into each product)
*parent_id 	- Reference ID for which category this one belongs to. (Sub-Categories etc), No parent ID would mean it is a Top-Level/Main Category.

 

This would allow you easy reference to any range of products from any range of categories in a single query. eg:

 

Categories:

SELECT * FROM categories

 

Products from Category with id "3"

SELECT * FROM products WHERE category_id=3

 

Select only products that have a category, and get that category with it.

SELECT * FROM products RIGHT JOIN categories on products.category_id = categories.id

 

For a really decent run-down of some useful advanced mysql queries try this (just found on google):

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p8.php

 

Hope this Helps,

-cb-

 

PS, the code you have there, to be honest is maybe the most efficient i can think of without re-arranging your database structure as outlined above.

[PFMaBiSmAd]

One of the main points to remember concerning retrieving data from a database is to retrieve it in the order that you want it. Then you simply display it the way you want.

 

For your question, you would use an ORDER BY type in your query so that the data for each type is together in the result set. You then simply detect when the type changes in your code and output a new heading when the type changes value (any typo's in the code are there as a test) -

$result = mysql_query("your query here");
$last_type = NULL; // set to a value that will never appear in the data
while($row = mysql_fetch_assoc($result)){
    // test if the 'type' changed and do any special processing
    if($last_type != $row['type']){
        // output a new heading -
        echo "{$row['type']}<br />";
        $last_type = $row['type']; // remember the current value
    }
    echo "{$row['data']}<br />"; // output the data value
}

[ciber]

@ChemicalBliss I kinda want to keep the mysql queries to a minimal since it slows the execution time down. So executing multiple queries is kinda out, however that link you provided was quite helpful.

 

@PFMaBiSmAd thats actually perfect idea, thanks

 

Thanks to both!!