CODEWITHSUNDEEP
phpmysqlreturn
user1879415
user1879415

Reputation: 21

Returning a set value where there is no data

I have report which shows the the products on sale, and the total weight of each product ordered. Here is a markup of the code to show the total weight:

$weightsql = 'select op.products_name, sum(op.products_quantity * p.products_weight) as weightsum from ' . TABLE_ORDERS_PRODUCTS . ' op left join ' . TABLE_PRODUCTS . ' p on op.products_id = p.products_id where op.products_id =  '.$pro['products_id'];
$weightq = tep_db_query( $weightsql );
while ($weight = tep_db_fetch_array( $weightq )){
if($category_parent_id != 0)$list_items[] = $weight['weightsum'];
}

It returns values for the products ordered, but for the unordered products, it stays blank, which is very confusing. How would I return 0.00 for the unordered products (the products not pulled from the query).

Example:

 Product Name         |             Total Weight
                      |
 Jelly Beans          |             25.00
 Soft Candy           |                              This product has not been ordered, needs to show 0.00
 Bubblegum            |             10.00

Upvotes: 0

Views: 69

Answers (2)

spencer7593
spencer7593

Reputation: 108480

From what I understand about your problem: if you want all rows from TABLE_PRODUCTS, with matching rows (if any exist) from the TABLE_ORDERS_PRODUCTS, you would need RIGHT JOIN rather than a LEFT JOIN in your query.

However, I prefer to use a LEFT JOIN wherever possible, so I would re-order the tables in the query

<snip>

... from ' . TABLE_PRODUCTS . ' p left join ' . TABLE_ORDERS_PRODUCTS . ' op ...

</snip>

That will fix the problem, if your problem is that you aren't getting the rows you expect.

The other issue is dealing with the NULL values returned from the rows returned by the OUTER JOIN. The quick answer there is to wrap those expressions in an IFNULL function to return a 0 (or 0.00, if you need decimal type)

<snip>

... IFNULL( sum(op.products_quantity * p.products_weight) ,0.0) as ...

or

... sum( IFNULL(op.products_quantity,0.0) * IFNULL(p.products_weight, 0.0) ) as

</snip>

Q: If I wanted to make the true value bold, and the null value as normal, to recognise the sold products, how would that be done?

A:

I expect you would need to include appropriate <b> and </b> tags in the generated HTML. (We don't normally return those types of markup tags from a database query, since a lot of the markup is usually handled by CSS.)

From the database, you could include an expression in your SELECT list, to let you know if a matching row was found in the TABLE_ORDERS_PRODUCT table or not, something like this:

  IF(op.products_id IS NULL,0,1) AS is_ordered

That would return to you a 0 if no a matching products_id was found in the TABLE_ORDERS_PRODUCT table, otherwise it would return a 1.

You could then could use this value to make the determination of what style or markup should be applied to whichever element you needed to make bold.

Upvotes: 0

dani herrera
dani herrera

Reputation: 51715

You are looking for coalesce function.

coalesce( sum(op.products_quantity * p.products_weight), 0 )

Also, change left join to right join because in your query product is on right of order.

This will replace Null value to 0.

Edited Due OP Comment. You can add new column with desired style:

select 
   op.products_name, 
   coalesce( sum(op.products_quantity * p.products_weight), 0 ) as weightsum,
   case when sum(op.products_quantity * p.products_weight) is NULL then 'Normal' 
        else 'Bold' end
   as stype
...

Upvotes: 1

Related Questions