how to sum table data values in php

Question

how to SUM table data values using php but without selecting mysql table

mysql_query("SELECT  * FROM pay_detec  WHERE (`nic_num` LIKE '%".$query."%') OR (`cust_name` LIKE '%".$query."%')"); 
if(mysql_num_rows($raw_results) > 0)
{
while($results = mysql_fetch_array($raw_results))
{ 

echo "<tr align='CENTER' bgcolor='FBE946'>

<td height='25px'>".$results['cust_name']."</td> <td>".$results['nic_num']."</td><td>".$results['acc_num']."</td><td>".$results['due_date']."</td>
<td>".$results['loan']."</td><td>".$results['due_months']."</td> <td>".$results['blanc_due']."</td><td>".$results['month_pay']."</td>
<td >".$results['payment']."</td><td>".$results['INTRST']."<td bgColor=red colSpan=0>".$results['short']."</td><td>".$results['othr_chrg']."</td><td>".$results['DELAY_CHRG']."</td><td>".$results['ADVNC']."</td><td>".$results['ttl']."</td><td>".$results['blanc2']."</td></tr>" ;
}

If you want to sum values of one column across the rows, simply $totalSum += $results['some_column'] inside your loop. Tip: don't use mysql extension, it's deprecated, use mysqli or PDO instead. — Honza Haering
– Honza Haering, Commented May 6, 2015 at 9:43

vrooze · Accepted Answer · 2015-05-06 10:05:18Z

1

Like Honza said use a variable to sum up as you loop over the rows. You could also use mysqls WITH ROLLUP with your group by (https://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html)

I would also take a look at securing your SQL as the above query, if that's what you're using, takes $query as user input, will leave you open to SQL injection.

example:

$totalCol = 0;
    mysql_query("SELECT  * FROM pay_detec  WHERE (`nic_num` LIKE '%".$query."%') OR (`cust_name` LIKE '%".$query."%')"); 
    if(mysql_num_rows($raw_results) > 0)
    {
        while($results = mysql_fetch_array($raw_results))
        { 
            $totalCol += $results['a_col'];
            echo "<tr align='CENTER' bgcolor='FBE946'>
            <td height='25px'>".$results['cust_name']."</td> <td>".$results['nic_num']."</td><td>".$results['acc_num']."</td><td>".$results['due_date']."</td>
            <td>".$results['loan']."</td><td>".$results['due_months']."</td> <td>".$results['blanc_due']."</td><td>".$results['month_pay']."</td>
            <td >".$results['payment']."</td><td>".$results['INTRST']."<td bgColor=red colSpan=0>".$results['short']."</td><td>".$results['othr_chrg']."</td><td>".$results['DELAY_CHRG']."</td><td>".$results['ADVNC']."</td><td>".$results['ttl']."</td><td>".$results['blanc2']."</td></tr>";
        }
        /* row for totals */
    }

edited May 6, 2015 at 10:05

answered May 6, 2015 at 9:53

vrooze

687 bronze badges

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

2 Comments

hanshenrik Over a year ago

also, customer name should probably be escaped. htmlentities($results['cust_name'],ENT_SUBSTITUTE);

pushparajha jeevanesan Over a year ago

doesn't work properly under i ll show my full code please help me

pushparajha jeevanesan · Accepted Answer · 2015-05-07 03:37:56Z

this my full code 

<?php

     echo '<body style="background-color:046C33">';


     echo '<body style="background-color:orange">';

    mysql_connect("127.0.0.1", "root", "") or die(mysql_error());
    mysql_select_db("customers") or die(mysql_error());
    ?>
    <html>
    <head>
    <title>Search</title>
    <link href="style.css" rel="stylesheet" type="text/css" />
    </head>
    <body>
    <form method="post" >
    <table border="0" cellpadding="0" cellspacing="0">
    <tr>ENTER NIC NUMBER
    <td><input type="text" name="query" id="text" />&nbsp;</td>
    <td><input type="submit" name="submit" id="search" value="Search" /></td>
    </tr>
    </table>
    </form>

    <?php 

    if(isset($_POST['submit']))
    {
    $query = $_POST['query']; 
    $min_length = 1;
    if(strlen($query) >= $min_length)
    { 
    $query = htmlspecialchars($query); 
    $query = mysql_real_escape_string($query); 
    echo "<table border='0' width='' align='center' cellpadding='1' cellspacing='1'>";
    echo "<tr align='center' bgcolor='62E15E'> 

    <td height='35px' width='150px'>NAME</td> <td>NIC NUMBER</td><td>GVS AC/NUM</td><td>DUE DATE</td><td>AMNT</td><td>DUE</td>
    <td>DUE BALANCE</td><td>PAYMNET MONTH</td><td>AMOUNT</td><td>INTERST</td><td bgColor=red colSpan=0>SHORT</td><td>OTHER CHRG</td>
    <td>DELAY CHRG</td><td>ADVANCE</td><td>TOTAL DEDEC</td><td>BALANCE</td></tr>"; 
    $totalCol = 0;
    $raw_results = 

        mysql_query("SELECT  * FROM pay_detec  WHERE (`nic_num` LIKE '%".$query."%') OR (`cust_name` LIKE '%".$query."%')"); 
        if(mysql_num_rows($raw_results) > 0)
        {
            while($results = mysql_fetch_array($raw_results))
            { 

                echo "<tr align='CENTER' bgcolor='FBE946'>
                <td height='25px'>".$results['cust_name']."</td> <td>".$results['nic_num']."</td><td>".$results['acc_num']."</td><td>".$results['due_date']."</td>
                <td>".$results['loan']."</td><td>".$results['due_months']."</td> <td>".$results['blanc_due']."</td><td>".$results['month_pay']."</td>
                <td >". $results['payment']."</td><td>".$results['INTRST']."<td bgColor=red colSpan=0>".$results['short']."</td><td>".$results['othr_chrg']."</td><td>".$results['DELAY_CHRG']."</td><td>".$results['ADVNC']."</td><td>".$results['ttl']."</td><td>".$results['blanc2']."</td></tr>";
            }



    }
    else{ 
    echo "<tr align='center' bgcolor='#6C0000'>

    <td colspan='2' height='25px'>No results</td><tr>"; 
    echo "</table>"; 
    } 
    }
    else{ 
    echo "Minimum length is ".$min_length;

     }
    }
    echo "<a href='index.html'>HOME PAGE</a>";
    ?>

    </body>
    </html>

Collectives™ on Stack Overflow

how to sum table data values in php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related