Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1123
  • Last Modified:

complex mysql query

I am trying to come up with a solution for my query below. heres my problem. the query below is on two tables. the second table "orders_products" can have multiple entries tied to the first table "orders". What I want to do is have a result that counts the sum of the entries in "orders_products" based on an extra id and returns those counts per row. For example.

orders contain the order_id and purchase date, and orders_products contain the products that were purchased with columns like order_id and product_id. I am trying to create a query that will report all products purchased within a timeframe and if any of the "product_id" match, count and combine those products into results per row. So my end result should look something like

row1 = sum of product_id1 - product_name1
row2 = sum of product_id2 - product name2.

heres what I have so far which basically returns every order within the date range.


$query = "SELECT o.*, op.*
FROM `orders` o, `orders_products op
WHERE o.orders_id = op.orders_id
AND date_purchased >= '2007-11-06'
AND date_purchased <= '2007-11-07'";
                                                                              
$data = mysql_query($query);
$rows = 0;
 while ($record = mysql_fetch_array($data) ) {
  $rows++;
  $records[] = $record;
}
0
redcable
Asked:
redcable
  • 3
  • 2
1 Solution
 
elfe69Commented:
You should perform a GROUP BY:

SELECT o.orders_id, o.date_purchased, SUM(op.orders_products_id) AS products_count
FROM `orders` o, `orders_products op
WHERE o.orders_id = op.orders_id
AND date_purchased >= '2007-11-06'
AND date_purchased <= '2007-11-07
GROUP BY o.orders_id, o.date_purchased

You can see the GROUP BY reference here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
0
 
imitchieCommented:

$query = "
SELECT op.product_id, count(product_id) as CountSold, p.product_name
FROM `orders` o, `orders_products` op, products p
WHERE o.orders_id = op.orders_id
AND p.product_id = op.product_id
AND date_purchased >= '2007-11-06'
AND date_purchased <= '2007-11-07'
group by op.product_id";

Open in new window

0
 
redcableAuthor Commented:
end up with this, i think the above would work as well so accepting.

SELECT products_id, count(products_id)
FROM orders_products
JOIN orders on (orders_products.orders_id = orders.orders_id)
WHERE orders.date_purchased between '2007-11-01' and '2007-11-07'
GROUP BY orders_products.products_id
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
imitchieCommented:
the above DOESN't work.

SUM(op.orders_products_id) AS products_count

gives you ID+ID+ID+ID instead of counting them.. fffff
0
 
redcableAuthor Commented:
oops imitchie i meant to accept yours, anyway t change or to late?
0
 
imitchieCommented:
gracias
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now