How to add numbers from an array/SQL query?

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

Post Reply
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

How to add numbers from an array/SQL query?

Post by TheBrandon »

Hello all,

Here's the code I am using now:

Code: Select all

    $remainsql="SELECT * FROM `ticket_sales` WHERE `show_id` = $showid";
        $remainresult=mysql_query($remainsql);
        $remaintotalpull = mysql_num_rows($remainresult);
 
        /*Loop for each show*/
        while($remaintotalpull > $a){
        
        $remainarray = mysql_fetch_assoc($remainresult);  
        $remainid=$remainarray["id"];
        $remaintix=$remainarray["ticket_amount"];
        
        print_r($remainarray);
 
        $a++;
        }
I want to add all of the $remaintix values. If the show id is "2", then there are 3 results in the SQL table. Ticketamount in that instance is 14, 1 and 1. I want to add all of those up (16) so I can subtract it from the total number of tickets available.

I understand basically why it isn't working. Because in my while loop, if I add it there it won't be the correct number (14 + 14) and then I reset the variable when it loops again, making it add 1+1 and not continuing with the previous number.

I just don't know how to fix it... any help would be appreciated.

In short, here is my SQL results:
Image

I just need to know how to pull all of the ticket_amount results, add them, then pass on the total amount. So I just need a variable to equal 16 in this case.

Here is the code for my whole page:

Code: Select all

 
<?
include("session.php");
?>
 
<html>
<head>
<? include("style.php");?>
</head>
<body>
 
<table border="2" width="100%">
 
 
<?
/**
 * User has already logged in, so display relavent links, including
 * a link to the admin center if the user is an administrator.
 */
if($session->logged_in){
include("include/sitemenu.php");
 
    $sort = $_GET["sort"];
 
    if (isset($sort)){
    $showsql="SELECT * FROM `shows` ORDER BY `show_date` $sort";
    $showresult=mysql_query($showsql) or die (mysql_error());
    $showtotalpull = mysql_num_rows($showresult) or die (mysql_error());
    }else{
    $showsql="SELECT * FROM `shows` ORDER BY `id` DESC";
    $showresult=mysql_query($showsql) or die (mysql_error());
    $showtotalpull = mysql_num_rows($showresult) or die (mysql_error());
    }
    $i=0;
    $a=0;
    
    echo "\n<table width=\"100%\" border=\"2\">\n"
    ."<tr class=\"label\">\n"
    ."<td>\n"
    ."Controls:"
    ."<td>\n"
    ."Show Name:"
    ."</td>\n<td>\n"
    ."<a href=\"viewshows.php?sort=";
    if ($sort=="ASC"){
    echo "DESC";}else{echo "ASC";}
    echo "\">Show Date</a>"
    ."</td>\n<td>\n";
    if($session->isAdmin()){
    echo "Tickets For Sale Online:"
    ."</td>\n<td>\n";
    }
    echo "Tickets Remaining:"
    ."</td>\n<td>\n"
    ."Showtime:"
    ."</td>\n";
        
    while($showtotalpull > $i){
        
        $array = mysql_fetch_array($showresult);   
        $showid=$array["id"];
        $showname=$array["show_name"];
        $showdate=$array["show_date"];
        $showdatesort=$array["show_date"];
        $showdate = date('m/d/Y', $showdate); 
        $ticketsavailable=$array["tickets_available"];
        $ticketsleft=$array["tickets_left"];
        $showtime=$array["show_time"];
        $showtimehum = date('h:i', $showtime);
        
        $remainsql="SELECT * FROM `ticket_sales` WHERE `show_id` = $showid";
        $remainresult=mysql_query($remainsql);
        $remaintotalpull = mysql_num_rows($remainresult);
 
        /*Loop for each show*/
        while($remaintotalpull > $a){
        
        $remainarray = mysql_fetch_assoc($remainresult);  
        $remainid=$remainarray["id"];
        $remaintix=$remainarray["ticket_amount"];
        
        print_r($remainarray);
        echo "<hr/>";
        echo $remaintix;
        echo "<hr/>";
        $a++;
        }
        
        /*Calculate tickets remaining*/
        $ticketsleft = ((int)$ticketsleft - (int)$remaintix);
        $curtime = time();
        
        /*Generate odd/even row classes*/
        if($i % 2){
            echo "<tr class=\"even\">\n";
        }else{
            echo "<tr class=\"odd\">\n";
        }
        
        
        if ((int)$curtime < $showtime){
            echo "<td>"
            ."<a href=\"reserve-form.php?showid=$showid&rem=$ticketsleft\"><img src=\"images/calendar.gif\"/></a>";
            if($session->isAdmin()){
                echo "<a href=\"delete-form.php?showid=$showid\"><img src=\"images/delete.gif\"/></a>"
                ."<a href=\"show-details.php?showid=$showid\"><img src=\"images/all.gif\"/></a>";
            }
            echo "</td>";
        }else{
            echo "<td>"
            ."<img src=\"images/calendarno.gif\"/>";
            if($session->isAdmin()){
                echo "<a href=\"delete-form.php?showid=$showid\"><img src=\"images/delete.gif\"/></a>"
                ."<a href=\"show-details.php?showid=$showid\"><img src=\"images/all.gif\"/></a>";
            }
            echo "</td>";
        }
        echo "<td>\n"
        .$showname
        ."</td>\n<td>\n"
        .$showdate
        ."</td>\n<td>\n";
        if($session->isAdmin()){
        echo $ticketsavailable
        ."</td>\n<td>\n";}
        echo $ticketsleft
        ."</td>\n<td>\n"
        .$showtimehum
        ."</td>\n"
        ."</tr>\n";
    $i++;
    $a=0;
    }
    echo "</table>\n";
}
else{
?>
</table>
<table border="2">
    <tr><td>Login</td></tr>
<?
/**
 * User not logged in, display the login form.
 * If user has already tried to login, but errors were
 * found, display the total number of errors.
 * If errors occurred, they will be displayed.
 */
if($form->num_errors > 0){
   echo "<font size=\"2\" color=\"#ff0000\">".$form->num_errors." error(s) found</font>";
}
?>
<form action="process.php" method="POST">
<tr><td>Username:</td><td><input type="text" name="user" maxlength="30" value="<? echo $form->value("user"); ?>"></td><td><? echo $form->error("user"); ?></td></tr>
<tr><td>Password:</td><td><input type="password" name="pass" maxlength="30" value="<? echo $form->value("pass"); ?>"></td><td><? echo $form->error("pass"); ?></td></tr>
<tr><td colspan="2" align="left"><input type="checkbox" name="remember" <? if($form->value("remember") != ""){ echo "checked"; } ?>>
<font size="2">Remember me next time &nbsp;&nbsp;&nbsp;&nbsp;
<input type="hidden" name="sublogin" value="1">
<input type="submit" value="Login"></td></tr>
<tr><td colspan="2" align="left"><br><font size="2">[<a href="forgotpass.php">Forgot Password?</a>]</font></td><td align="right"></td></tr>
<tr><td colspan="2" align="left"><br>Not registered? <a href="register.php">Sign-Up!</a></td></tr>
</table>
</form>
 
<?
}
 
/**
 * Just a little page footer, tells how many registered members
 * there are, how many users currently logged in and viewing site,
 * and how many guests viewing site. Active users are displayed,
 * with link to their user information.
 */
echo "</td></tr><tr><td align=\"center\"><br><br>";
echo "<b>Member Total:</b> ".$database->getNumMembers()."<br>";
echo "There are $database->num_active_users registered members and ";
echo "$database->num_active_guests guests viewing the site.<br><br>";
 
include("include/view_active.php");
 
?>
 
 
</td></tr>
</table>
 
 
</body>
</html>
 
 
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: How to add numbers from an array/SQL query?

Post by TheBrandon »

I think maybe I need to do something with an associative array, using [$i] as the key? Thoughts?
enoc22
Forum Commoner
Posts: 33
Joined: Wed Apr 01, 2009 12:45 pm

Re: How to add numbers from an array/SQL query?

Post by enoc22 »

just a newby here
but i was able use the folowing code to make $total contain the sum of a certain colum in a table

Code: Select all

 
<?php
 
$con = mysql_connect("[b]server[/b]","[b]your user name[/b]","[b]your pass word[/b]");
if ($con){
    echo "Conected";
  }ELSE{
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db('select you database');
 
$qr=("SELECT [b]colum name[/b] FROM [b]table name[/b] ");
$sql = mysql_query($qr);
 
$data=array();
WHILE($row=mysql_fetch_assoc($sql)){    
    $data[]=$row["[b]colum name[/b]"];  
}
$total=array_sum($data);
 
?>
Basically once you have you query results in an array you can use array_sum to get the total of all the values
Hope that helps

Oliver
Post Reply