[SOLVED] Multiple queries into one....

[bubbasheeko]

I have two tables but would prefer only one query to do the job.

 

I am trying to pull yearly data (from `ticketevents` and `ticketevent_items`).  The only common field between the two is `item_number`.

 

The fields I want are:

 

sum(ticketevents.gross), sum(ticketevents.tax), sum(ticketevents.fees), ticketevent_items.item_name and ticketevent_items.event_date.

 

I have tried a few variations to SELECT them, but they have failed.  Any hints in the right direction would be great!

[dreamwest]

Im too lazy to create the query for you but heres an example

 

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT table1.column, table2.column ".
"FROM table1, table2 ".
"WHERE table1.column = table2.column";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
echo $row['column']. " - ". $row['column2'];
echo "<br />";
}
?>

[bubbasheeko]

Thanks for the response!  I figured that out right after I posted.  I didn't mark it as resolved because I couldn't get it to work - I keep receiving an error.

 

$event_name = mysql_query('SELECT `ticketevent_items.item_name`, `ticketevent_items.event_date`, SUM(`ticketevents.mc_gross`), SUM(`ticketevents.tax`), SUM(`ticketevents.mc_fee`) FROM `ticketevents`, `ticketevent_items` WHERE YEAR(`ticketevent_items.event_date`) = "' . $year . '" && `ticketevents.item_number` = `ticketevent_items.item_number`');

 

With PHP output I receive this error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/tax_report.php on line 56

 

Tested within PHPMyAdmin and received this error:

 

#1054 - Unknown column 'ticketevent_items.item_name' in 'field list'

 

Double checked my spelling of the field names and the tables.  They are correct.

 

Corrected it - bad quotes...updated the fields to `ticketevents`.`fieldname`.  Worked, however, I did not get anything for the SUM - turned up blank.

 

Event Title Event Date Gross Revenue Total Transactions Costs Total GST Total Earned

 

Awards 2009 2009-03-20         $ $                         $       $0

 

All the sum's I tried to get did not produce anything like I saw in PHPMyAdmin.

 

item_name               event_date SUM(`mc_gross`) SUM(`tax`) SUM(`mc_fee`)

Awards 2009    2009-03-20 3704.40                 176.40         0.00

 

So let's add what I have for code:

 

$event_name = mysql_query('SELECT `ticketevent_items`.`item_name`, `ticketevent_items`.`event_date`, SUM(`ticketevents`.`mc_gross`), SUM(`ticketevents`.`tax`), SUM(`ticketevents`.`mc_fee`) FROM `ticketevent_items`, `ticketevents` WHERE YEAR(`ticketevent_items`.`event_date`) = "2009" && `ticketevent_items`.`item_number` = `ticketevents`.`item_number` GROUP BY `ticketevent_items`.`item_name');



echo "Report for -> " . $year;
echo "<br><br>";
echo "<table width='650' border='0' cellspacing='0' cellpadding='0'>";
echo "<tr>"; // title row
echo "<td>Event Title</td>";
echo "<td>Event Date</td>";
echo "<td>Gross Revenue</td>";
echo "<td>Total Transactions Costs</td>";
echo "<td>Total GST</td>";
echo "<td>Total Earned</td>";
echo "</tr>";
echo "<tr>";
echo "<td colspan='6'> </td>";
echo "</tr>";	

while($event_name_result = mysql_fetch_assoc($event_name))
{
	$gross = $event_name_results['SUM(`ticketevents`.`mc_gross`)'];
	$fees = $event_name_results['SUM(`ticketevents`.`mc_fee`)'];
	$taxes = $event_name_results['SUM(`ticketevents`.`tax`)'];

	$net = ($gross - $fees) - $taxes;	echo "<tr>";
	echo "<td>" . $event_name_result['item_name'] . "</td>";
	echo "<td>" . $event_name_result['event_date'] . "</td>";
	echo "<td>$" . $gross . "</td>";
	echo "<td>$" . $fees . "</td>";
	echo "<td>$" . $taxes . "</td>";
	echo "<td>$" . $net . "</td>";
	echo "</tr>";
}
echo "</table>";

[bubbasheeko]

Anybody have any ideas why my SUM()'s are not working?

[bubbasheeko]

Resolved.  Didn't see the 's' on:

 

$event_name_result's'['SUM(`ticketevents`.`mc_gross`)'];