php/mysql- extraction/calculation and display

I'm stuck and need some serious help here. New eyes welcome!

[aftershock2020]

I'm trying to create code to extract mysql data, calculate and display the results in a template, showing the vendor_id and then listing all of the related products, ( product_id ), to form an automated ordering process for my cart.

The output would be something like:

Vendor_ID

Product_ID quantity_total cost_per_unit pricing_total shipping_total

I am trying to get help on the actual calculations to make sure that the code associates the vendor_id to the corresponding product_ids to be listed as well as to make sure that is lists the other features properly.

Example:

Client A orders

product_id_102 12 units cost per unit cost total shipping cost
product_id_03 48 units cost per unit cost total shipping cost
product_id_24 01 units cost per unit cost total shipping cost

Client B orders

product_id_102 12 units cost per unit cost total shipping cost
product_id_102 12 units cost per unit cost total shipping cost
product_id_102 12 units cost per unit cost total shipping cost
product_id _03 12 units cost per unit cost total shipping cost
product_id _09 12 units cost per unit cost total shipping cost

I am trying to extract this information from the database, total it and display a master report as well as an indisvidual report that shows all or individually like this:

Purchase order A-

Vendor_ID_A

product_id_102 48 units cost per unit cost total shipping cost
product_id_03 60 units cost per unit cost total shipping cost

Purchase order B-

Vendor_ID_B

product_id_09 12 units cost per unit cost total shipping cost
product_id_24 01 units cost per unit cost total shipping cost

That should be as simple as to echo the results into the display templates from the primary report results. I've just been looking at this so long that I'm needing some new eyes.

Can anyone help?

[jatar_k]

Welcome to WebmasterWorld aftershock2020,

I see what you are trying to do though I am not sure how you are storing them

do you have an sql query that you are trying to use already? or can you show us the structure of the tables you are trying to get this data from?

[aftershock2020]

Actually, I've figured it out at about 3 am this morning and am posting some exteneded table to the database at the moment.

[jatar_k]

glad you got it sorted :)

[aftershock2020]

Yeah, it's working alright. I'll be sure and post a copy of the code that resolved the issue...paying it forward for the next crunch I'm in.

Thanks for the help.

[aftershock2020]

Okay, bit of a problem still.

How would I code a query for a total value of a column?

Say there are 3 columns in the database, how would I go about totaling the values of each row being added together to display the proper result?

Example:

Client_ID . Product_quantity Date_pruchased

Mary 28 today
Bill 35 last week
Dave 9 last month

I want to know how to code a mysql query to total the 'product_quantity' values for a grant total on the display results.

I can configure the table values. I simply need to know how to make the math work in the code. This is something simple and is being a major pain in the hide.

[aftershock2020]

Got the right command figured out. Not sure how to build this query in php though. Would be to sum up the column but my question is how?

[aftershock2020]

Here are the results for anyone else that needs to know this one. Take notice that this example works by calculating the entire 'price' and 'groups' the items apart. This will total up anything you specifically target the value for within the sum() command.

The issue that I was getting returned errors on where due to the "$" within the price column reading as variables. It's always something like a missing quote or something with this language.

Here is the datqabase example:
id name type price
123451 Park's Great Hits Music 19.99
123452 Silly Puddy Toy 3.99
123453 Playstation Toy 89.95
123454 Men's T-Shirt Clothing 32.50
123455 Blouse Clothing 34.97
123456 Electronica 2002 Music 3.99
123457 Country Tunes Music 21.55
123458 Watermelon Food 8.73

Here is the code:

<?php
// Make a MySQL Connection

$query = "SELECT type, SUM(price) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['type']. " = $". $row['SUM(price)'];
echo "<br />";
}
?>

Here's the displaying results:

Total Clothing = $67.47
Total Food = $8.73
Total Music = $45.53
Total Toy = $93.94