crosstab using php & mysql

Question

I have a database table in this format

Date_from date_to charge amount

====================================

01/01/15 07/01/15 AGTI 500
01/01/15 07/01/15 AGTII 700.50
08/01/15 14/01/15 AGTI 330.19
08/01/15 14/01/15 AGTII 540.19

Now I want to display it like this depending on the date range given by user

charge 01/01/15-07/01/15 08/01/15-14/01/15 total

==================================================================
AGTI 500 330.19 830.19
AGTII 700.50 540.19 1240.69
====================================================================
Total 1200.50 870.38 2070.88

If user select 01/01/15-07/01/15 from date calender then only 01/01/15-07/01/15 column and total value will come and if select 01/01/15-14/01/15 than column for 2 weeks and total value will display. I am badly stuck with this one please help..

I did this one..which I got from stackoverflow. But how can I get the rowwise and column wise summation https://stackoverflow.com/questions/22200229/how-do-i-create-a-crosstab-table-with-php-mysql/31176631#31176631 — susmita sengupta, Jul 02 '15 at 10:54
Please post the CREATE TABLE statements for your database. Are the date values stored as strings or MySQL Datetime format? — Adam Copley, Feb 05 '16 at 20:59

score 0 · Answer 1 · answered Feb 06 '16 at 14:16

This is not a complete solution. However it does add value to the site by answering the question title only. Meaning that this query is not dynamic and doesn't use datepickers. the specified dates are hardcoded as you can see, but easily adjusted. This however will get you a reasonably good starting point to get where you want to be.

Checkout this SQLfiddle to see details of the database schema.

<?php

$host = "localhost"; // the hostname or ip address of the mysql server
$user = "user"; // this is the username for mysql
$pass = "pass"; //password for mysql server
$db = "database"; //name of your database

$conn = mysqli_connect($host, $user, $pass, $db); // connect to the database with the values we defined above

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); // show an error if we fail to connect
  }

  $sql = "SELECT charge,
            SUM((CASE Date_from
                    WHEN '2015-01-01' 
                        THEN amount
                        ELSE NULL
                    END)) AS `week1`,
                SUM((CASE Date_from
                    WHEN '2015-01-08' 
                        THEN amount
                        ELSE NULL
                    END)) AS `week2`,
                SUM(amount) AS total
            FROM charges
            GROUP BY charge
            ORDER BY charge ASC";

  $query = mysqli_query($sql, $conn); //define our query
// leave php and define some html table headers
?>
<table>
    <tr>
        <th>Charge</th>
        <th>01/01/15-07/01/15</th>
        <th>08/01/15-14/01/15 </th>
        <th>Total</th>
    </tr>
<?php //now back to php to display our results.

//we want to add a TOTAL line at the bottom so we're going to need to add these up
//start by declaring a counter as zero for each thing we want to count
$w1total = 0; //week 1 total
$w2total = 0; // week 2 total
$gtotal = 0; // week 3 total

//loop through each row in the query
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){

    $w1total += $row['week1']; // add the values to our counters
    $w2total += $row['week2'];
    $gtotal += $row['total'];

    // build a html row for each of our results
    echo "<tr>";
        echo "<td>".$row['charge']."</td>";
        echo "<td>".$row['week1']."</td>";
        echo "<td>".$row['week2']."</td>";
        echo "<td>".$row['total']."</td>";
    echo "</tr>";
  }

  //and finally add our totals row
  echo "<tr>";
    echo "<td>Total</td>";
    echo "<td>".$w1total."</td>";
    echo "<td>".$w2total."</td>";
    echo "<td>".$gtotal."</td>";
  echo "</tr>";
 echo "</table>";

 mysqli_close($conn); //close our connection when we're finished.

 ?>

crosstab using php & mysql

1 Answers1