Skip to main content

Calculate Or Auto Sum All Price In Database Table Using PHP/MySQL

Screenshot
Body
This tutorial will teach you on how to Compute or Autosum the price of all products in database table using sum function in PHP. To understand more lets follow the steps bellow.

Creating Our Database

First we are going to create our database which stores our data. To create a database: 1. Open phpmyadmin 2. Then create database and name it as "tutorial". 3. After creating a database name, click the SQL and paste the following code.
  1. CREATE TABLE IF NOT EXISTS `checkbox` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `name` varchar(100) NOT NULL,
  4. `price` int(11) NOT NULL,
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

Creating our Database Connection

Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format.
  1. <?php
  2. /* Database config */
  3. $db_host = 'localhost';
  4. $db_user = 'root';
  5. $db_pass = '';
  6. $db_database = 'tutorial';
  7.  
  8. /* End config */
  9.  
  10. $db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
  11. $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12.  
  13. ?>

Creating Our Display Autosum Script

The code bellow will retrieve data from our database table. This also include our script that get the total of all price in database table using php sum function, copy the code bellow and save it as "index.php".
  1. <table id="resultTable" data-responsive="table" style="text-align: left; width: 400px;" border="1" cellspacing="0" cellpadding="4">
  2. <thead>
  3. <tr>
  4. <th> Name </th>
  5. <th> Price </th>
  6. </tr>
  7. </thead>
  8. <tbody>
  9. <?php
  10. include('connect.php');
  11. $result = $db->prepare("SELECT * FROM checkbox");
  12. $result->execute();
  13. for($i=0; $row = $result->fetch(); $i++){
  14. ?>
  15. <tr class="record">
  16. <td><?php echo $row['name']; ?></td>
  17. <td><?php echo $row['price']; ?></td>
  18. </tr>
  19. <?php
  20. }
  21. ?>
  22. </tbody>
  23. <thead>
  24. <tr>
  25. <th> Total </th>
  26. <th>
  27. <?php
  28. $results = $db->prepare("SELECT sum(price) FROM checkbox");
  29. $results->execute();
  30. for($i=0; $rows = $results->fetch(); $i++){
  31. echo $rows['sum(price)'];
  32. }
  33. ?>
  34. </th>
  35. </tr>
  36. </thead>
  37. </table>
Hope this tutorial will help you.

Add new comment