Pulling value from two tables

[countrygirl]

I am trying to do something that is too complicated for my brain. The info I need to work with is stored in two different mysql tables. What I am trying to do is get all of the id's from the first table that belong to one customer then I want to go into another table and add to the total every entry that matches all id's from the first query and keep totalling them until I am out of id's and out of total's. There will be more than one id that matches the customer and more than one total that matches the id. So basically I have to grab an array, assign each value in the array to a variable and loop though them all and total the total column from the other table which will also be an array. I hope I am making some sort of sense

$ttl_inv = mysql_fetch_array(mysql_query("SELECT id FROM si_invoices WHERE customer_id='$_SESSION[user_id]'"));
while($row=mysql_fetch_row($ttl_inv))
{
$total_inv = mysql_fetch_array(mysql_query("SELECT SUM(total) FROM si_invoice_items WHERE id='$row'"));
$total_invoices = $total_invoices + $total_inv[0];
}

[MattF]

I think this is the correct syntax, offhand. Untested though.


$result = mysql_query("SELECT SUM(i.total) FROM si_invoice_items AS i INNER JOIN si_invoices AS v ON i.id=v.customer_id WHERE v.customer_id='$_SESSION[user_id]'"));

while($row = mysql_fetch_row($result))
{
$total_invoices += $row[0];
}

[countrygirl]

it's very very close there was 1 too many brackets in there so this code here almost works but the total isn't right, it is somehow adding extra stuff into $total_invoices because it is $63.50 too high. Any ideas? Also I have another question that is probably a pretty stupid one but I am unclear on it $result, is that an actual variable or is it something that has to be used in order for the code to work, like in this come below could I have used $blah in the place of $result and if I have more than one $result on a page are they going to interfere with each other (that is not the case on this page though)?

$result = mysql_query("SELECT SUM(i.total) FROM si_invoice_items AS i INNER JOIN si_invoices AS v ON i.id=v.customer_id WHERE v.customer_id='$_SESSION[user_id]'");

while($row = mysql_fetch_row($result))
{
$total_invoices += $row[0];
}

[MattF]

it's very very close there was 1 too many brackets in there

Apologies. Typo on my part. :D


I am unclear on it $result, is that an actual variable or is it something that has to be used in order for the code to work, like in this come below could I have used $blah in the place of $result and if I have more than one $result on a page are they going to interfere with each other (that is not the case on this page though)?

$result merely contains the result from the query which you then use to fetch the referenced item(s). You could name it anything you like. Try this code:

$result = mysql_query("SELECT i.total FROM si_invoice_items AS i INNER JOIN si_invoices AS v ON i.id=v.customer_id WHERE v.customer_id='$_SESSION[user_id]'");

while($row = mysql_fetch_row($result))
{
$total_invoices += $row['total'];
}

[countrygirl]

Thank you for the clarification on $result

The code you gave me in this one get's a $total_invoices=0

Thank you for your help in this by the way

[MattF]

$result = mysql_query("SELECT i.total FROM si_invoice_items AS i INNER JOIN si_invoices AS v ON i.id=v.customer_id WHERE v.customer_id='$_SESSION[user_id]'");

while($row = mysql_fetch_assoc($result))
{
$total_invoices += $row['total'];
}

[countrygirl]

lol this one gave me the same result as the first one, $63.50 too much

[guelphdad]

Get rid of your front end application by doing these queries directly in the database, with hard coded values instead of variables.

THEN and ONLY THEN will you know if your query is working correctly. Any errors after that are as a result of your front end application (php or whatever).

Post some sample rows of your database and the expected results. then we can help you decipher why your query isn't working.

[countrygirl]

Ok I have tried all of the above queries directly in phpmyadmin with a hard coded value for customer_id because everything else should be pulled from the database in the query. I got the same error every time:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$result = mysql_query("SELECT i.total FROM si_invoice_items AS i INNER JOIN si_i' at line 1 it doesn't like the queries.

The two tables in question are like this:
si_invoice_items which has the following fields:
`id` int(10) NOT NULL auto_increment,
`invoice_id` int(10) NOT NULL default '0',
`quantity` decimal(25,6) NOT NULL default '0.000000',
`product_id` int(10) default '0',
`unit_price` decimal(25,6) default '0.000000',
`tax_amount` decimal(25,6) default '0.000000',
`gross_total` decimal(25,6) default '0.000000',
`description` text,
`total` decimal(25,6) default '0.000000',

and si_invoices which has the following fields:
`id` int(10) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL default '1',
`biller_id` int(10) NOT NULL default '0',
`customer_id` int(10) NOT NULL default '0',
`type_id` int(10) NOT NULL default '0',
`preference_id` int(10) NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`custom_field1` varchar(50) collate utf8_unicode_ci default NULL,
`custom_field2` varchar(50) collate utf8_unicode_ci default NULL,
`custom_field3` varchar(50) collate utf8_unicode_ci default NULL,
`custom_field4` varchar(50) collate utf8_unicode_ci default NULL,
`note` text collate utf8_unicode_ci,

What I am trying to do is get all of the id's from the first table that belong to one customer then I want to go into another table and add to the total every entry that matches all id's from the first query and keep totalling them until I am out of id's and out of total's. There will be more than one id that matches the customer and more than one total that matches the id. So basically I have to grab an array, assign each value in the array to a variable and loop though them all and total the total column from the other table which will also be an array.

[MattF]

You have to use pure SQL code in the admin interface.


SELECT i.total FROM si_invoice_items AS i INNER JOIN si_invoices AS v ON i.id=v.customer_id WHERE v.customer_id=1;



Change 1 to whatever id you're checking against.

[countrygirl]

Okay what that seems to do is pull 6 values of 200 each, which explains I guess where the 1200 comes from.

It looks like this is what this code is doing.

The customer id that is stored in the session variable is 6

so when go to si_invoices what it should be doing is grabbing all of the values in the id field where the customer id is 6 so we should get (3, 5, 7, 8, 10, 13) I think we are getting a value of 6 or the number of values that match that customer id instead of all of the values.

Then we go to si_invoice items it grabs the total that matches the number of values from the previous query, so id 6 is 200. It should be grabbing the 6 values that match the id that it should have pulled from the first table, those values are (599.000000, 175.000000, 175.000000, 512.500000, 300.000000, 100.000000)

Then it multiplies 6 x 200 = 1200
but it should add 599.000000 + 175.000000 + 175.000000 + 512.500000 + 300.000000 + 100.000000 = 1561.500000

So now that we know what it is doing and what it should be doing can you tell me how to get it to do what I want it to lol

[MattF]

What relationship do the two tables have? Granted, I'm somewhat tired at the moment so may be missing something, but id in the invoice_items table can't tally upto the customer_id, surely, so what column does tally up between the two? I can't make sense of that layout for the life of me, at the moment.

[countrygirl]

Okay I got it to work with this, but it is probably a really long way of doing it regardless I am pretty darn proud of myself lol, is there an easier way to do this? The relationship is id in the table si_invoices is equal to invoice_id in the table si_invoice_items

SELECT SUM(si_invoice_items.total) AS $total_invoices FROM si_invoices LEFT JOIN si_invoice_items ON si_invoices.id = si_invoice_items.invoice_id WHERE si_invoices.customer_id=6

and now I can't figure out how to pass my result to a variable, sheesh I have this:

$results1 = mysql_query("SELECT SUM(si_invoice_items.total) AS $total_invoices FROM si_invoices LEFT JOIN si_invoice_items ON si_invoices.id = si_invoice_items.invoice_id WHERE si_invoices.customer_id='$_SESSION[user_id]'");

[countrygirl]

If anyone is interested, this is what finally worked. Matt thank you so much for all of your help with this.

$sql="
SELECT SUM(si_invoice_items.total) AS total_invoices FROM si_invoices
LEFT JOIN si_invoice_items ON si_invoices.id = si_invoice_items.invoice_id
WHERE si_invoices.customer_id='{$_SESSION['user_id']}'
";

if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
$row = mysql_fetch_assoc($result);
$total_invoices=$row['total_invoices'];
$total_invoices=number_format($total_invoices,2);
}
}