php mysql database get price total

Question

I have using php code with mysql database showing data into html table, and i'm using filter on listview, now I'm stack with get price Total of the list after filtered. I'm not good enough with php code, am I made mistake with php or sql?

MySQL table:

id INT - name VARCHAR - code VARCHAR - price INT

here is my code so far:

<?php

if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM `toy` WHERE CONCAT(`id`, `name`, `code`, `price`) 
LIKE '%".$valueToSearch."%'";
$search_result = filterTable($query);

}
else {
$query = "SELECT * FROM `toy` LIMIT 5";
$search_result = filterTable($query);
}

// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("localhost", "user", "pass", 
"database");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}

?>

<!DOCTYPE html>
<html>
<head>
    <title>Wu</title>
    <link href="style.css" type="text/css" rel="stylesheet" />

</head>
<body>

    <div id="toys-grid"> 
        <div class="search-box">
        <br>            
        <form action="index.php" method="post">
        <input type="text" name="valueToSearch" placeholder="Name To Search">
        <br><br>
        <input type="submit" name="search" value="Filter"><br><br>
        </div>

        <table cellpadding="10" cellspacing="1">

    <thead>
            <tr>
                <th>Id</th>
                <th>First Name</th>
                <th>Code</th>
                <th>Price</th>
            </tr>
    </thead>
  <!-- populate table from mysql database -->
            <?php while($row = mysqli_fetch_array($search_result)):?>
            <tbody>
            <tr>
                <td><?php echo $row['id'];?></td>
                <td><?php echo $row['name'];?></td>
                <td><?php echo $row['code'];?></td>
                <td><?php echo $row['price'];?></td>
            </tr>
            </tbody>
            <?php endwhile;?>

            <thead>
            <tr>
            <th> Total </th>
        <th> 
        <?php
        $results = mysql_query('SELECT SUM(price) FROM toy');
        $results->execute();
        for($i=0; $rows = $results->fetch(); $i++){
        echo $rows['SUM(price)'];
        }
        ?>
        </th>
        </tr>
        </thead>
        </table>
        </form>
    </div>
</body>

I appreciate your help...

you miss an 'i' in **mysql_query('SELECT SUM(price) FROM toy');** and your for loop got wrong syntax on second parameter. Better use while statement. E.G : while ($rows = mysqli_fetch_array($result)) { echo $rows['SUM(price)']; } — Camilo Go Jr., Aug 16 '17 at 07:15

score 1 · Accepted Answer · answered Aug 16 '17 at 07:16

No need to execute query for getting sum. You can do it with php

<table cellpadding="10" cellspacing="1">

    <thead>
            <tr>
                <th>Id</th>
                <th>First Name</th>
                <th>Code</th>
                <th>Price</th>
            </tr>
    </thead>
  <!-- populate table from mysql database -->
    <?php $sum = 0; ?>
    <?php while($row = mysqli_fetch_array($search_result)):?>
    <tbody>
    <tr>
        <td><?php echo $row['id'];?></td>
        <td><?php echo $row['name'];?></td>
        <td><?php echo $row['code'];?></td>
        <td><?php echo $row['price'];?></td>
        <?php
        $sum += $row['price'];
        ?>
    </tr>
    </tbody>
    <?php endwhile;?>

    <thead>
      <tr>
        <th> Total </th>
        <th> 
        <?php
        echo $sum;
        ?>
        </th>
      </tr>
    </thead>
</table>

perfect....works great, thanks mate – user1710911 Aug 16 '17 at 07:21 — user1710911, Aug 16 '17 at 07:21

php mysql database get price total

1 Answers1