MySQL or PHP - Managing an Inventory

[fedoracore]

What is the "standard practice" for grouping multiple returns of the "same item" in a simple join query?Please observe this lines from a working MySQL query Method:

$AGquery = "SELECT cat_id_ag, ag_id, ag_identity, ag_nickname, ag_details				FROM aguitar_inventory, aguitar_models				WHERE aguitar_inventory.ag_id = aguitar_models.ag_identity";	$AGresult = mysql_query($AGquery,$dblink) or die("I could not connect to the database because ".mysql_error());	$this->many=mysql_num_rows($AGresult);	 	for($z=0;$z<$this->many;$z++) {	$counter[$z]=  mysql_fetch_array($AGresult, MYSQL_ASSOC);	}

It's technically giving me what i'm asking for, but i don't want it to show all that it does in the ultimate "end-user view". Allow me to elaborate just a bit as it will probably cut-to-the-chase a bit faster: it's an on-line inventory for my friend's guitar store. I want the results to show one-of-each itemcurrently, any duplicate inventory item is printed as part of the $counter array......resulting in an output like:$counter[0] = guitarA, $counter[1] = guitarA, $counter[3] = guitarA, $counter[4] = guitarBhowever, i want it to print to screen asguitarA = qty 3guitarB = qty 1etc.my question is:do i want to handle the "sum" at MySQL (i.e. is there a MySQL built-in-syntax which will do such a thing? [MySQL v.5.0 - PHP 5.current]. i'm far from being well versed in the art of SQL, so please don't take for granted that i might know even the simplest of functions [i can do date INTERVALS and simple joins like above, and that's about it!]), or would i be best to try to create the "sum" on the PHP side? seems like a MySQL thing to me but it's just a hunch.thanks!!!PS. then of course i'll need to play around w/ the SQL to report an "out-of-stock" item. hmm... i think i can tackle that one, but any SQL insight offered for that one will be appreciated as well!thank you!!

[Nakor]

This will depend on the structure of your SQL database but you may be able to use the GROUP BY function. This function returns all variables that have the same variable declared directly after GROUP BYex.Say you have a table that looks like this

select * from test;+----+------------+| id | day		|+----+------------+|  1 | 2006-10-08 ||  2 | 2006-10-08 ||  3 | 2006-10-09 |+----+------------+

If you ran the query:

select day, count(*) from test GROUP BY day;+------------+----------+| day		| count(*) |+------------+----------+| 2006-10-08 |		2 || 2006-10-09 |		1 |+------------+----------+

As you can see it grouped the two entries that shared the same date. This could be useful for inventorying guitars. I believe this would also handle your problem of out of stock items. If the count returns as 0 then you know there are none and you can show them as out of stock.

[justsomeguy]

I'm not that great at using GROUP BY, so if anyone sees any problems speak up, but I think this is probably close to the query you need:

SELECT cat_id_ag, ag_id, ag_identity, ag_nickname, ag_details, COUNT(ag_id) AS ag_qtyFROM aguitar_inventory, aguitar_modelsWHERE aguitar_inventory.ag_id = aguitar_models.ag_identityGROUP BY ag_identity, cat_id_ag, ag_nickname, ag_details

[fedoracore]

hey there! thanks mucho for your input on this issue. i regret to report that it "didn't work"... at least not as nice and clean as shown in Nakor's demo...couple things, please, before we get into specific analysis 1.) Do we all agree that the best approach to solving this problem is through a MySQL function, and NOT something i might want to do to the return data on the PHP side?2.) Something tells me my "failed SQL" isn't really failing as much as it is my chosen use of the query result-- have another look (just in case...) at that next-to-the-last line of my original posted code. the output comes from this multi-dimensional array created by the FOR() loop there at the end. i'm not married to this "M.D. Array" method. it's worked for me elsewhere (for dropdowns n stuff), and it seemed the right idea as a starting point here as well, but perhaps i want to take it from another angle, considering the issue at hand?okay. on to the results: you'll see the SQL, and the results of it's output. I chopped it down to spare you my made-up filler-data, but the results were the same, essentially, no matter if i used justsomeguy's SQL, or this which i consider more a variation on Nakor's proposal:

"SELECT ag_id, COUNT(*)				FROM aguitar_inventory, aguitar_models				WHERE aguitar_inventory.ag_id = aguitar_models.ag_identity				GROUP BY ag_identity, cat_id_ag, ag_nickname, ag_details";// ### END //// ### print_r($mulitiDimArray) ###Array(	[0] => Array		(			[ag_id] => J045			[COUNT(*)] => 1		)	[1] => Array		(			[ag_id] => J045			[COUNT(*)] => 1		)	[2] => Array		(			[ag_id] => J045			[COUNT(*)] => 1		)	[3] => Array		(			[ag_id] => J045			[COUNT(*)] => 1		)	[4] => Array		(			[ag_id] => L03			[COUNT(*)] => 1		)	[5] => Array		(			[ag_id] => L03			[COUNT(*)] => 1		)	[6] => Array		(			[ag_id] => MHD28			[COUNT(*)] => 1		)	[7] => Array		(			[ag_id] => MHD28			[COUNT(*)] => 1		))

also tried count(ag_id); COUNT(aguitar_identity), etc., etc... everything came out as value (1)..thanks again!

[Nakor]

You may want to try changing the SQL if that is the result. It looks as if it is trying to group them but since the group query is so specific it may not be grouping them because of a subtle difference in one of the fields. Try

"SELECT ag_id, COUNT(*)				FROM aguitar_inventory, aguitar_models				WHERE aguitar_inventory.ag_id = aguitar_models.ag_identity				GROUP BY ag_id";

If that does not work you could definitely do some server side PHP to group them together after a simple SELECT * query. Let me know if that doesn't work and I'll see what I can do about some PHP grouping.

[fedoracore]

hey Nakor, that did it man! thanks so much.now, why exactly is it that we had to go that route in particular? you mentioned that

since the group query is so specific it may not be grouping them because of a subtle difference in one of the fields

-- how might i avoid / plan for this in future designs? i'm all about trying to get the db just right, so i like to take advantage of any opportunity-- especially while i'm "down in it" so to speak. no biggie if ya don't have time, but if you do, i'm curious... or maybe you have a URL to an article on the subject?thanks again!!! if y'r ever trav'lin through Appalachia, and ya need some guitar strings, or a Kazoo perhaps... definitely look me up! hehe :)EDIT:Sorry-- but for some reason i'm feeling rather thick-headed about this thing today. I made an image for you to view the structure a bit better. My question: using the return value from the Query developed above (the one which Nakor provided), will i be able to manipulate that particular query, or should i "simultaneously" execute another query for the rest of the data, and then somehow use the results from the first (the COUNT(*) / GROUP query) for formatting a more detailed query? for example, i'd like to see:###########Manufacturer NameCatalog No. | Product (aka 'nickname') | Description | List | Sale | In Stock###########Manufacturer NameCatalog No. | Product (aka 'nickname') | Description | List | Sale | In Stock###########Manufacturer NameCatalog No. | Product (aka 'nickname') | Description | List | Sale | In Stockhence my reasoning for the Query we developed here.EDIT2:hmph. i don't quite get what's different here, but i mod'd the query, like so, and im getting what i want / need now:

$AGquery = "SELECT ag_id, COUNT(*), ag_identity, ag_nickname, ag_details				FROM aguitar_inventory, aguitar_models				WHERE aguitar_inventory.ag_id = aguitar_models.ag_identity				GROUP BY ag_id";

[Nakor]

Ah, that is because I forgot about those other variables that you had to retrieve from the database. Is it working correctly then?