Page 1 of 2

Adding all tables together?

Posted: Sat Oct 16, 2010 1:53 pm
by robynprivette
I have three tables in one data base. Is it possible to add all the tables together to get the number of how many entries there are? and if so how?

my tables are egg, hatch, and er. each has the fields user and code. I want to add all the codes up from each table and get one number.

Thanks (:

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 2:18 pm
by JakeJ
assuming your database is MySQL:

Code: Select all

$qry = mysql_query("SELECT sum(codes) FROM egg");
$qry_egg = mysql_fetch_row($qry);
$eggsum = $qry_egg[0];

$qry = mysql_query("SELECT sum(codes) FROM hatch");
$qry_hatch = mysql_fetch_row($qry);
$hatchsum = $qry_hatch[0];

$qry = mysql_query("SELECT sum(codes) FROM eg");
$qry_eg = mysql_fetch_row($qry);
$egsum = $qry_eg[0];

$sum_codes = $eggsum + $hatchsum + $egsum;


Re: Adding all tables together?

Posted: Sat Oct 16, 2010 3:20 pm
by robynprivette
thank you :) how do I echo that out?

i tried this:

Code: Select all

echo "$sum_codes";
and got this :

Code: Select all

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource 

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 3:21 pm
by JakeJ
echo $sum_codes;

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 3:29 pm
by McInfo
I suggest using one query with subqueries:

Code: Select all

SELECT
    (a.sum + b.sum + c.sum) AS total
FROM
    (SELECT SUM(code) AS sum FROM egg  ) AS a,
    (SELECT SUM(code) AS sum FROM hatch) AS b,
    (SELECT SUM(code) AS sum FROM er   ) AS c

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 3:32 pm
by robynprivette
Yea I put that and got this error

Code: Select all

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource 
this is exactly what i have:

Code: Select all

<?php
mysql_connect("localhost", "---", "---") or die(mysql_error());
mysql_select_db("thehatch_dragon") or die(mysql_error());

$qry = mysql_query("SELECT sum(codes) FROM egg");
$qry_egg = mysql_fetch_row($qry);
$eggsum = $qry_egg[0];

$qry = mysql_query("SELECT sum(codes) FROM hatch");
$qry_hatch = mysql_fetch_row($qry);
$hatchsum = $qry_hatch[0];

$qry = mysql_query("SELECT sum(codes) FROM eg");
$qry_eg = mysql_fetch_row($qry);
$egsum = $qry_eg[0];

$sum_codes = $eggsum + $hatchsum + $egsum;

echo $sum_codes;
?>

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 3:34 pm
by robynprivette
McInfo wrote:I suggest using one query with subqueries:

Code: Select all

SELECT
    (a.sum + b.sum + c.sum) AS total
FROM
    (SELECT SUM(code) AS sum FROM egg  ) AS a,
    (SELECT SUM(code) AS sum FROM hatch) AS b,
    (SELECT SUM(code) AS sum FROM er   ) AS c
Could you show me how to put this into what I have now? posted above

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:16 pm
by McInfo
I thought about it, but no. All of the necessary pieces are there. You just need to figure out how they fit together. I am willing to explain what is happening in detail if you want, but it won't do any good for me to write the code for you.

If you don't understand something, ask about it or look up the function in The Manual.

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:20 pm
by robynprivette
I figured out what the problem was in the first code... eg should be er and codes should be code.

It's echoing "1.0E+19"... what is that???

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:31 pm
by McInfo
1.0E+19 is a number in scientific notation. It means 1.0 times 10 to the 19th power.

Code: Select all

$sum_codes = 10000000000000000000;
Either the number is really big or it is a negative number being treated as an unsigned number (a number that can only be positive because it has no negative sign).

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:32 pm
by robynprivette
McInfo wrote:1.0E+19 is a number in scientific notation. It means 1.0 times 10 to the 19th power.

Code: Select all

$sum_codes = 10000000000000000000;
Either the number is really big or it is a negative number being treated as an unsigned number (a number that can only be positive because it has no negative sign).
can i fix this?

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:41 pm
by McInfo
What does your script look like at this point?

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:47 pm
by robynprivette

Code: Select all

<?php
mysql_connect("localhost", "---", "---") or die(mysql_error());
mysql_select_db("thehatch_dragon") or die(mysql_error());

$qry = mysql_query("SELECT sum(code) FROM egg");
$qry_egg = mysql_fetch_row($qry);
$eggsum = $qry_egg[0];

$qry = mysql_query("SELECT sum(code) FROM hatch");
$qry_hatch = mysql_fetch_row($qry);
$hatchsum = $qry_hatch[0];

$qry = mysql_query("SELECT sum(code) FROM er");
$qry_er = mysql_fetch_row($qry);
$ersum = $qry_er[0];

$sum_code = $eggsum + $hatchsum + $ersum;

echo $sum_code;
?>
I couldn't figure out yours :(

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:50 pm
by McInfo
Do you have phpMyAdmin? If so, have you tried running the sum queries there?

Re: Adding all tables together?

Posted: Sat Oct 16, 2010 4:54 pm
by robynprivette
i don't know how to do that