Adding all tables together?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Adding all tables together?

Post 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 (:
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Adding all tables together?

Post 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;

User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post 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 
Last edited by robynprivette on Sat Oct 16, 2010 3:23 pm, edited 1 time in total.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Adding all tables together?

Post by JakeJ »

echo $sum_codes;
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Adding all tables together?

Post 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
User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post 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;
?>
User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Adding all tables together?

Post 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.
User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post 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???
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Adding all tables together?

Post 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).
User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post 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?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Adding all tables together?

Post by McInfo »

What does your script look like at this point?
User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post 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 :(
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Adding all tables together?

Post by McInfo »

Do you have phpMyAdmin? If so, have you tried running the sum queries there?
User avatar
robynprivette
Forum Commoner
Posts: 46
Joined: Sun May 02, 2010 6:22 pm

Re: Adding all tables together?

Post by robynprivette »

i don't know how to do that
Post Reply