Array out DB content + Some product ratings. - Notice: Undefined index:

[RK4002]

Hello,

 

Im learning PHP and i have startet af little product, hvor im rating some products an so on. 

 

But im getting stuck trying to echo out some Variables. Hope somebody can help, it will be greatly apriciated! :-)

 

 

 

My function:

 function getAllProductInfo() {

    // DB Connection
        $all_products_query = mysql_query("SELECT * FROM products");
        while ($row = mysql_fetch_assoc($all_products_query)) {          

        // Get ratings


        // Get effect data

            $count_effect_rating = 0;
            $effect_result=mysql_query("SELECT effect_rating FROM rating WHERE product_id = $product_id");
            
            if(!empty($effect_result)){
                while($row=mysql_fetch_assoc($effect_result)){
                    $count_effect_rating += $row['effect_rating'];
                } 
            }
            
        // Get taste data

            $count_taste_rating = 0;
            $taste_result=mysql_query("SELECT taste_rating FROM rating WHERE product_id = $product_id");
          
            if(!empty($taste_result)){
                while($row=mysql_fetch_assoc($taste_result)){
                    $count_taste_rating += $row['taste_rating'];
                } 
            }

        // Get price data

            $count_price_rating = 0;
            $price_result=mysql_query("SELECT price_rating FROM rating WHERE product_id = $product_id");
          
            if(!empty($price_result)){
                while($row=mysql_fetch_assoc($price_result)){
                    $count_price_rating += $row['price_rating'];
                } 
            }

        // Get number of votes

            if(!empty($effect_result)){
                $count_votes = mysql_num_rows($taste_result);
            }

        // Calculate and result variables

            if($count_effect_rating > 0){
                $effect_rating = ($count_effect_rating / $count_votes);
                $taste_rating = ($count_taste_rating / $count_votes);
                $price_rating = ($count_price_rating / $count_votes);
                $parameters = "3";
                $overall_rating = round(($effect_rating + $taste_rating + $price_rating) / ($parameters), 1);
            } else {
                $effect_rating = "0";
                $taste_rating = "0";
                $price_rating = "0";
                $overall_rating = "0";
            }

            $all_products_array[$row['product_id']] = $row;
            $all_products_array[$row['product_id']]['overall_rating'] = $overall_rating;
            $all_products_array[$row['product_id']]['effect_rating'] = $effect_rating;
            $all_products_array[$row['product_id']]['taste_rating'] = $taste_rating;
            $all_products_array[$row['product_id']]['price_rating'] = $price_rating;
        
        } // WHILE END
   
        return $all_products_array;
        
    } // FUNCTION END

Display content:

 

// Instantiate the function
$all_products_array = getAllProductInfo();

// Loop through each row and display the data

foreach ($all_products_array as $product) {
    echo '<p>' . $product['product_name'] . '</p>';
}

Thanks! :-)

[RK4002]

I have tried setting this in the top:

$product_id = $row['product_id'];     

But then then the row im trying to access is undefined :-)

[mac_gyver]

it would probably help if you posted the undefined error you got. what are the column names in your products table?

 

btw - you should never run queries inside of loops. it's likely that everything you are trying to get your code to do can be done in one single query or at most one query and a small amount of php code (for the combined average.) you can calculate the average of data using the mysql AVG() function in the query.

[mac_gyver]

the following single query will produce all the data you have shown in the above code -

$query = "SELECT p.product_name, AVG(r.effect_rating) as effect_rating, AVG(r.taste_rating) as taste_rating,
    AVG(r.price_rating) as price_rating,
    ROUND(AVG(r.effect_rating + r.taste_rating + r.price_rating)/3,1) as overall_rating
    FROM products p LEFT JOIN rating r ON p.product_id = r.product_id GROUP BY p.product_id";

if you are using more columns from your products table than you have shown in the code, you will need to add them to the select list in the query.

 

products that don't have any ratings yet will produce a row with null values for the four ratings, which you can address in the query (using COALESCE()) if you want and produce zero values for them.

Edited May 4, 2014 by mac_gyver

[RK4002]

Thank you so much! I will begin with SQL i didn't even know the power of it. :-)