user1710911 user1710911 -3 years ago 152
MySQL Question

php mysql database get price total

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...

Answer Source

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>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download