homepage Welcome to WebmasterWorld Guest from 54.198.42.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
joining and grouping by
generic




msg:4534415
 7:30 pm on Jan 8, 2013 (gmt 0)

I have to tables, products and manufacturers, that looks basically like this:

PRODUCTS
|- prod_id
|- prod_name
|- prod_qty

MANUFACTURERS
|- man_id
|- man_name

What I'm trying to do is output a list of available stock, by manufacturer, so something like:

"Manufacturer: $man_name | SUM($prod_qty) in stock"

So I'm trying to get the total sum of prod_qty (in stock product) for each associated prod_man (manufacturer id).

I'd like to show you what I have currently, but it's a gnarley nest of loops and queries that really does nothing but spout errors. Can anyone show me how to write a query like this?

Thanks in advance!

 

coopster




msg:4534670
 3:02 pm on Jan 9, 2013 (gmt 0)

You'll need to join the two tables. But I do not see any common keys shared between the two tables to join them?

generic




msg:4534686
 3:24 pm on Jan 9, 2013 (gmt 0)

The prod_man field holds the id of the manufacturer (man_id) but that's as common as it gets. I just can't wrap my head around joins at all, no matter how many books or tutorials I look at.

coopster




msg:4534689
 3:31 pm on Jan 9, 2013 (gmt 0)

So if a manufacturer has 10 different products, you only want one line to show with the total quantity of all 10 different products summed up?

generic




msg:4534692
 3:42 pm on Jan 9, 2013 (gmt 0)

Yea, it's basically just so I can tally total amount of stock by manufacturer. So if there are two manufacturers, Acme and WidgetCo, I'd like it loop through and return the total sum of prod_qty where prod_man = man_id

Acme: 49 unique products (246 items in stock)
WidgetCo: 26 unique products (421 items in stock)

Or something like that. We're tracking t-shirt transfers so we don't want to enter each individual transfer (and amount of each transfer in stock) as inventory, we'd rather just keep track of how many transfers we have, in total, by manufacturer.

coopster




msg:4534693
 3:45 pm on Jan 9, 2013 (gmt 0)

Understood. It would looks something like this ...

SELECT 
m.man_name AS Manufacturer,
SUM(prod_qty) AS Qty
FROM manufacturers AS m
INNER JOIN products AS p ON (m.man_id = p.prod_man)
GROUP BY m.man_name
ORDER BY m.man_name
;

You select from your primary table which is the manufacturers and join the products table on the primary key, manufacturer identifier. In order to SUM you need to use a GROUP BY.

generic




msg:4534695
 4:00 pm on Jan 9, 2013 (gmt 0)

That's amazing. I just can't seem to wrap my head around joins and my code is about four times longer and uglier than it needs to be because of that. That is exactly what I needed. Thank you so much coopster, I really appreciate your help!

skoff




msg:4534768
 9:08 pm on Jan 9, 2013 (gmt 0)

you should read about inner join and all that stuff it would really help your query to run faster and try to avoid using select inside a select method this is hard on the server! Coopster got it right

generic




msg:4534769
 9:12 pm on Jan 9, 2013 (gmt 0)

I completely agree. I know I needed to use joins but I just can't wrap my head around them no matter how many times I try. I'm tenacious though, one of these days the idea will stick. Cheers.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved