I know this is probably due to the fact that I've been looking at this for over 12 hours to resolve this issue. I am using a mysql database and php to generate an extraction/calculation code for keeping my reports stable.

I am trying to create a query/calculation that will result in displaying on seperate invoice teplates, most likely via echo. I haven't added the mysql joins yet for multipe table and/or row extraction yet. I do know how to do that. The problem is in the calculation being correct first.

Here is what I have so far, as I am running the test but haven't broken the product-id down into individual representations yet:

$query = "SELECT * FROM $tablename WHERE name = '$vendor_id'";

$result = mysql_query($query);

$num = mysql_num_rows($result);

$i = 0;

IF ($num == 0) :
PRINT "No data";
ELSEIF ($num > 0) :
PRINT "Data that matched your query: $num<BR>";
WHILE ($i < $num) :
$vendor_id = mysql_result($result,$i,"vendor");
$product_id = mysql_result($result,$i,"product");
PRINT "The products of $vendor_id is: $product_id.<BR>";
$i++;
ENDWHILE;
ENDIF;

Thanks in advance to anyone that can help me out here.

Aftershock2020

    It's not entirely clear to me what you need help with, but here's a slightly simpler and more typically used method to output the query data:

    <?php
$query = "SELECT product FROM $tablename WHERE vendor = '$vendor_id'";
$result = mysql_query($query);
if(mysql_num_rows($result) == 0)
{
   echo "<p>No data found for vendor $vendor_id.</p>";
}
else
{
   echo "<p>The products of $vendor_id:<p>\n<ul>\n";
   while($row = mysql_fetch_assoc($result))
   {
      echo "<li>" . $row['product'] . "</li>\n";
   }
   echo "</ul>\n";
}

      I'll explain a little better. Thanks for the reply.

      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.

      Make more sense?

        It's still difficult for me to suggest anything specific without understanding the database design and relations, as I suspect you can do most of the work in the query itself. For instance:

        $query = <<<EOD
SELECT
  vendor,
  product,
  unit_cost,
  SUM(quantity) AS total_qty,
  SUM(quantity * unit_price) AS total_price
FROM table_name
GROUP BY vendor, product
EOD;
$result = mysql_query($query);
$data = array();
while($row = mysql_fetch_assoc($result))
{
   $data[$row['vendor']][] = array(
      'product' => $row['product'],
      'unit_cost' => $row['unit_cost'],
      'total_qty' => $row['total_qty'],
      'total_price' => $row['total_price']
   );
}
/*
* At this point you now have a query results array that is (relatively) easy to walk 
* through with foreach loops:
*/
foreach($data as $vendor => $arr)
{
   echo "<h3>$vendor</h3>\n<table>\n";
   echo "<tr><th>Product</th><th>Quantity</th><th>Unit Price</th><th>Total Price</th></tr>\n";
   foreach($arr as $prod)
   {
      printf(
         "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
         $prod['product'],
         $prod['total_qty'],
         $prod['unit_cost'],
         $prod['total_price']
      );
   }
   echo "</table>\n";
}

          Thanks, that works beautifully. Now, the next issue is how to make the results disply in a more dynamic way, rather than showing the teletype style text, is there a better way to display the results in a table chart or other visually appealing method?

          I am trying to use the data results for displaying in a report and need it to flow better. Recommendations and if so, how would I go about directing it to display?

            Write a Reply...