JOIN? UNION? Help!

[dbutler]

My experience with PHP and MySQL is limited to what I would call “simple” uses of the software. I have an issue I can’t seem to overcome and I am probably over complicating this and just need some experienced help. So here goes:I have a web application I built where employees enter projects into a database. These projects have certain identifiers that pertain to the whole project (general ledger ID, Account #, etc). Then, each project could have materials associated with it, or each project could have labor associated with it, or each project could have both materials and labor associated with it.My database is designed with a PROJECTS table, MATERIALS table, and a LABOR table. There are other supporting tables in the database, but these are the primary ones.In order to create a report that shows the total cost of certain projects, I want to build a SINGLE query that I can build an HTML table around. My issue is that I just don’t understand how to build a join (or maybe union) statement that gives me an array that contains what I need it to contain and how I need it contain the information. Below is an abbreviated look at the 3 tables’ structures:PROJECTSid (auto increment column key)account (the accounting code that this project pertains to)glid (the general ledger ID where this project should be accounted for)tax (number I use as the % of tax that pertains to any materials in this project)freight (number I use as the % of freight that pertains to any materials in this project)MATERIALSid (the key that ties back to the project table)mat_qty (the qty of materials)mat_unit (the unit price of the material)- note that the qty*unit is what the above tax and freight percentages are calculated onLABORid (the key that ties back to the project table)lab_qty (the qty of labor)lab_unit (the unit price of the labor)- note that there is no tax or freight associated with laborI would like to build a query that would have a single result that looks like the below:id glid account tax freight mat_qty mat_unit lab_qty lab_unit100 42402 RA400 6 1 5 5000 null null100 42402 RA400 6 1 null null 200 75101 39106 RA301 5 2 3 25000 null null102 22508 RA402 6 1 null null 150 50Project id 100 has both materials and labor, id 101 has only materials, and id 102 has only labor. How do I write a query that does this?I want to be able to have a mysql_fetch_array of a query result that I can do a foreach loop thru the glid and accounts and add up the total materials and labor and show it nicely in an html table. I could do two separate queries but then I would have to show two separate tables with a total for labor and a total for material and I couldn’t add the two together. Perhaps there is a much easier way to do this, but my thoughts are that a single query with a result like the above would make it easy to do. Any thoughts or help would be much appreciated! drb

[dbutler]

Ok, I haven't seen any comments but I have thought of another way I could build this table, however, my code is not working right (this is a common theme for me now.....). Instead of ONE query to satisfy, I will just invoke a function that queries the database from within a loop for materials and labor. I have validated that the queries below get the right data from the database, but the foreach only ends up showing me one account and the materials and labor is empty. Thoughts on what might be wrong?

<?php/*******************************************************************************	Test of function for table build********************************************************************************/include ("php_header.inc");function get_materials($account,$glid)	{	$query1 = "select tax, freight, mat_qty, mat_unit from PROJECTS, MATERIALS where PROJECTS.id=MATERIALS.id and account='$account' and glid='$glid'";	$result1 = mysql_query($query1) or die ("Couldn't select materials for glid and account");	$row1 = mysql_fetch_array ($result1,MYSQL_ASSOC);	while ($row1 = mysql_fetch_array($result1))		{		extract($row1);		$mat_extended = ($mat_qty * $mat_unit * (1 + (($tax + $freight) / 100)));		$mat_subtotal = $mat_extended + $mat_subtotal;		}	echo "Total Materials $mat_subtotal<br>";	}function get_labor($account,$glid)	{	$query2 = "select lab_qty, lab_unit from PROJECTS, LABOR where PROJECTS.id=LABOR.id and account='$account' and glid='$glid'";	$result2 = mysql_query($query2) or die ("Couldn't select labor for glid and account");	$row2 = mysql_fetch_array ($result2,MYSQL_ASSOC);	while ($row2 = mysql_fetch_array($result2))		{		extract($row2);		$lab_extended = $mat_qty * $mat_unit;		$lab_subtotal = $lab_extended + $lab_subtotal;		}	echo "Total Labor $lab_subtotal<br>";	}$glid = 39102;$query = "select distinct account from PROJECTS where glid='$glid' order by account";$result = mysql_query($query) or die ("Couldn't select distinct accounts");$row = mysql_fetch_array ($result,MYSQL_ASSOC);foreach ($row as $account)	{	echo "$account<br>\n";	get_materials($account,$glid);	get_labor($account,$glid);		}?>

[jlhaslip]

what happens when you take the "distinct" out?

[dbutler]

I just tried it and nothing changed...I have the distinct in there so that the array does not generate duplicates; basically I just need to know which accounts are actually entered, and then I need to query the details of each of those accounts in MATERIALS and LABOR.Thanks for the thought! Any others? In breaking this down, I don't understand why the "foreach" is not working correctly; it only gives me the first item instead of all of them. Any chance that $row is not being considered an array???

[jlhaslip]

set error-reporting "on" to find out.

[dbutler]

ok, I'll try and figure out how to do that. thx

[dbutler]

well, apparently I cannot turn on error reporting...I am convinced that using a function is the right way to do this, and not thru a single query. I'm going to start a new thread that helps me figure out why my query result is not showing up as an array. Thanks! drb

[alexnofue]

Hi, you seem to have leave the topic but anyway, the first thing you should check out is the design of your db, you are missing some foreign keys in the materials and labor, create them, then you could run a query like:$query1 = "SELECT p.id, p.glid, p.account, p.tax, p.freight, m.mat_qty, m.mat_unit, l.lab_qty, l.lab_unit FROM PROJECTS p LEFT JOIN MATERIALS m ON p.id = m.pid LEFT JOIN labor l ON p.id = l.pid WHERE p.account='$account' AND p.glid='$glid'";this could give you a similar result to you example with the keys added first. you could also try a query in a db administrator, could be the phpmyadmin for mysql for example and check the results there.you could do more than one query but that means checking the array and doing the other queries inside a for or something similar and add the results to the initial array, it's a lot more work.