PHP Select from multiple tables and add value together to get total

Question

i am trying to run these queries in PHP to a mysql table

$sql="SELECT * from customer where customerid > '' and voicemail_max > '' and company_status = '' ";
$rs=mysql_query($sql,$conn);
while($customer=mysql_fetch_array($rs)) {
    $sql2="SELECT * from client where parent_client_id = '".$customer["customerid"]."' ";
    $rs2=mysql_query($sql2,$pbx01_conn);
    while($result2=mysql_fetch_array($rs2))
    {
        $sql3="SELECT * from extension where client_id = '".substr($result2["id"],1,3)."' ";
        $rs3=mysql_query($sql3,$pbx01_conn);
        while($result3=mysql_fetch_array($rs3))
        {
            $sql4="SELECT * from extension_voicemail where extension_id = '".$result3["id"]."' ";
            $rs4=mysql_query($sql4,$pbx01_conn);
            $voicemail_size_total=0;
            while($result4=mysql_fetch_array($rs4)) {
                $voicemail_size_total = $voicemail_size_total+$result4["filesize"];
            }
        }
    }
    echo $voicemail_size_total;
}

it should be adding up multiple values from one column

the $sql4 query looks like:

SELECT * from extension_voicemail where extension_id = '1454'

and when i run that in the database, it returns one row with the file size column as 31780

but when i echo $voicemail_size_total variable, it shows nothing.

it works fine if i echo that variable one line up from where it is, but where it is now shows nothing

Hanlet Escaño · Accepted Answer · 2014-01-23 22:15:35Z

1

Declare $voicemail_size_total=0; outside of all of the while loops. Everytime you get back to the loop you re-set that back to 0. That might be the problem.

answered Jan 23, 2014 at 22:15

Hanlet Escaño

17.4k10 gold badges54 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Minciu · Accepted Answer · 2014-01-23 22:31:29Z

Well, it depends on what you want to achieve: 1. If you want to get a total of all the filesizes (basicly you are expecting just one value to be presented ) try this:

$sql="SELECT * from customer where customerid > '' and voicemail_max > '' and company_status = '' ";
$rs=mysql_query($sql,$conn);
$voicemail_size_total=0;
while($customer=mysql_fetch_array($rs)) {
    $sql2="SELECT * from client where parent_client_id = '".$customer["customerid"]."' ";
    $rs2=mysql_query($sql2,$pbx01_conn);
    while($result2=mysql_fetch_array($rs2))
    {
        $sql3="SELECT * from extension where client_id = '".substr($result2["id"],1,3)."' ";
        $rs3=mysql_query($sql3,$pbx01_conn);
        while($result3=mysql_fetch_array($rs3))
        {
            $sql4="SELECT * from extension_voicemail where extension_id = '".$result3["id"]."' ";
            $rs4=mysql_query($sql4,$pbx01_conn);
            while($result4=mysql_fetch_array($rs4)) {
                $voicemail_size_total = $voicemail_size_total+$result4["filesize"];
            }
        }
    }
}
echo $voicemail_size_total;

2. If you're looking for the filesizes of each client (ie. expecting a series of filesizes to be presented ) try this:

$sql="SELECT * from customer where customerid > '' and voicemail_max > '' and company_status = '' ";
$rs=mysql_query($sql,$conn);
while($customer=mysql_fetch_array($rs)) {
    $sql2="SELECT * from client where parent_client_id = '".$customer["customerid"]."' ";
    $rs2=mysql_query($sql2,$pbx01_conn);
    $voicemail_size_total=0;
    while($result2=mysql_fetch_array($rs2))
    {
        $sql3="SELECT * from extension where client_id = '".substr($result2["id"],1,3)."' ";
        $rs3=mysql_query($sql3,$pbx01_conn);
        while($result3=mysql_fetch_array($rs3))
        {
            $sql4="SELECT * from extension_voicemail where extension_id = '".$result3["id"]."' ";
            $rs4=mysql_query($sql4,$pbx01_conn);
            while($result4=mysql_fetch_array($rs4)) {
                $voicemail_size_total = $voicemail_size_total+$result4["filesize"];
            }
        }
    }
    echo $voicemail_size_total;
}

Collectives™ on Stack Overflow

PHP Select from multiple tables and add value together to get total

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related