PHP Code to display cross table

[kumarg19]

Hi

I require a PHP code to get output table. 

 

My Table 

 

Item Qty Date
aa-1 2 2014-10-01
aa-2 5 2014-10-01
aa-3 1 2014-10-01
ab-1 2 2014-10-01
ab-2 1 2014-10-01
bb-1 4 2014-10-01
bb-2 3 2014-10-01
bb-3 2 2014-10-01
aa-1 1 2014-10-02
aa-2 2 2014-10-02
aa-3 5 2014-10-02
ab-1 6 2014-10-02
ab-2 1 2014-10-02
bb-1 9 2014-10-02
bb-2 0 2014-10-02
bb-3 4 2014-10-02
aa-1 1 2014-10-03
aa-2 2 2014-10-03
aa-3 5 2014-10-03
ab-1 4 2014-10-03
ab-2 3 2014-10-03
bb-1 1 2014-10-03
bb-2 8 2014-10-03
bb-3 2 2014-10-03

 

I wrote code as mentioned below.

<?php

$accounts=mysql_connect("localhost", "root", "") or die("could not connect");  

mysql_select_db("shops",$accounts) or die("could not find db!");

if(isset($_POST['search']) && ($_POST['from']) && ($_POST['to'])){
    $searchq=$_POST['search'];
    $searchq=preg_replace("#[^0-9a-z]#i", "" , $searchq);
    $from=$_POST['from'];
    $to=$_POST['to'];

    $dateInput = explode('-',$from);
    $fdate = $dateInput[2].'-'.$dateInput[1].'-'.$dateInput[0];

    $dateInput = explode('-',$to);
    $tdate = $dateInput[2].'-'.$dateInput[1].'-'.$dateInput[0];
    

    for ($date=$fdate; $date<=$tdate; $date++) {
            $sql = "SELECT  item, SUM(CASE WHEN `date` = '$date' THEN Qty ELSE 0 END)
            FROM shop WHERE item LIKE '%$searchq%' GROUP BY item";  
        $query = mysql_query($sql) or die("could not search!"); 

            echo "<table border='1'>";

                echo "<tr>
                        <td>Item </td>
                        <td>$date</td>
                      </tr>" ;

            while ($row=mysql_fetch_array($query)) {

                echo "<tr>
                        <td>". $row[0] ." </td>
                        <td>" . $row[1] . "</td>
                      </tr>";  
            }               
            echo "</table>";
     }   
} 
?>

I am getting result like this as I asked for three days.

Item	2014-10-01
aa-1	2
aa-2	5
aa-3	1
Item	2014-10-02
aa-1	1
aa-2	2
aa-3	5
Item	2014-10-03
aa-1	1
aa-2	2
aa-3	5

But I need result like below


Item	2014-10-01	2014-10-02	2014-10-03
aa-1	2	        1	        1
aa-2	5	        2	        2
aa-3	1	        5	        5

Can anybody help me to write PHP code to display result as needed. Please help. Thanks in advance.

[ginerjm]

We usually help those who help themselves.  That means if you want to be a programmer, act like one and learn how to at least attempt to write code.  Tough words but we're not here to do your bidding.  We're here to help people who are making an attempt to become programmers.

 

Now that I've vented on you - there is another forum for people looking to hire help.  Perhaps you want to post there.

[kumarg19]

I am not bidding anybody. I am unable to get the result as required. So I posted my problem. Please help if you can. I am searching for solution in "Google". If I find the solution I post first here. Thx.

[Barand]

Here's pseudo code for how to do it

Create empty array whose keys are your dates.
Set previous item to blank

Loop through the query results (checking for a change in the item code.)
if item changes (ie not equal to previous item,)
    if prev item is not blank
        output prev item code and the array values into a table row
    endif
    create empty array whose keys are your dates.
    set previous item to current item
endif
Accumulate qty into the array element for the date
endloop
Output the row for the last item code

[kumarg19]

Hi Guru,

I could not collect your info. 

Can you explain with an example.

[Barand]

You need an array for each row of your output table. In your example, the processing of each row would begin with

$start = array (
        '2014-10-01'  => 0,  
        '2014-10-02'  => 0,  
        '2014-10-03'  => 0
    );

You would build this array dynamically from your date range. You can use these array keys to create your table headings. (You copy this empty array when each item code changes ($item_array = $start;)

 

As you process each record in your results you would add the qty into the copy of this array

$item_array[$date] += $qty;

When you get a change in the item code you out the array for the previous item, reset the value of the previous item (so you can test for a change again) and copy the array to start processing the new item code's records.

 

Two things to bear in mind

• you don't output the array on the very first item change as the array will be empty at this point and the previous code is blank.
• After processing the records you still have the totals for the last item saved in the array, so output them

Edited November 7, 2014 by Barand