It's a bit hard to describe what I intend. So I will just show what I want to achieve and you can tell me if it's possible and how.
This is for a stock transaction log.
DATA:
t_type productid t_date t_stock t_stock_after
1 2 2013-11-06 16:52:30 1 80
1 3 2013-11-06 15:50:40 1 60
1 2 2013-11-06 13:52:30 1 81
1 2 2013-11-06 13:48:30 1 82
1 2 2013-11-05 13:52:30 1 83
1 2 2013-11-04 14:56:30 1 84
1 2 2013-11-04 13:55:30 2 85
1 2 2013-11-04 13:54:30 1 87
2 2 2013-11-04 13:53:30 10 88
1 2 2013-11-04 13:52:30 1 78
OBJECTIVE:
t_type productid t_date t_stock t_stock_after
1 2 2013-11-06 3 80
1 2 2013-11-05 1 83
1 2 2013-11-04 4 84
2 2 2013-11-04 10 88
1 2 2013-11-04 1 78
So what I wish to do is group the * (asterisk) ones only because they share the same day and type and they are sequential.
t_type productid t_date t_stock t_stock_after
* 1 2 2013-11-06 16:52:30 1 80
* 1 2 2013-11-06 13:52:30 1 81
* 1 2 2013-11-06 13:48:30 1 82
1 2 2013-11-05 13:52:30 1 83
* 1 2 2013-11-04 14:56:30 1 84
* 1 2 2013-11-04 13:55:30 2 85
* 1 2 2013-11-04 13:54:30 1 87
2 2 2013-11-04 13:53:30 10 88
1 2 2013-11-04 13:52:30 1 78
UPDATE:
I also realized something, t_stock_after does not SUM. It only shows the last t_stock_after. Sample data has been corrected to simulate real life data and help get to an answer.
In order to help as much as possible:
This is the table:
id (int and auto increment), productid, userid, t_type, t_date, t_stock, t_stock_after, t_reason
UPDATE 2 - WORKING CODE
And as I said, here is the code I eventually managed to create =)
SQL:
if(isset($_GET['mindate']) && $_GET['mindate']!="" && isset($_GET['maxdate']) && $_GET['maxdate']!="")
$stocklog = $dataserver->query("SELECT t_type, productid, t_date, SUM(t_stock) AS stock, SUM(t_stock_after) AS stockafter FROM store_log_transactions WHERE productid = ".$_GET['id']." AND (DATE(t_date) BETWEEN DATE_SUB('".$_GET['mindate']."', INTERVAL 1 MONTH) AND '".$_GET['maxdate']."') GROUP BY t_date, t_type");
else
$stocklog = $dataserver->query("SELECT t_type, productid, t_date, SUM(t_stock) AS stock, SUM(t_stock_after) AS stockafter FROM store_log_transactions WHERE productid = ".$_GET['id']." AND (t_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()) GROUP BY t_date, t_type");
PHP:
$id=1;
$temp_date = "";
$temp_type="";
$temp_stock=0;
$temp_stock_after=9999999999;
while($log = $stocklog->fetch_object())
{
$current_date = date("Y-m-d",strtotime($log->t_date));
$current_type = $log->t_type;
$current_stock = $log->stock;
$current_after = $log->stockafter;
if($current_date==$temp_date || $id==1)
{
if($current_type==$temp_type || $id==1)
{
$temp_stock+=$current_stock;
if($temp_stock_after>$current_after);
$temp_stock_after=$current_after;
$temp_date=$current_date;
$temp_type=$current_type;
}
else
{
$transactiontype;
switch($temp_type)
{
case 1: $transactiontype='net sales'; break;
case 2: $transactiontype='net return'; break;
case 3: $transactiontype='transfer in'; break;
case 4: $transactiontype='trasnfer out'; break;
}
echo '<div class="listitem"><div>'.$temp_stock.'</div><div>'.$temp_stock_after.'</div><div>'.$transactiontype.'</div><div>'.$temp_date.'</div></div>';
$temp_date=$current_date;
$temp_type=$current_type;
$temp_stock_after=$current_after;
$temp_stock=$current_stock;
}
}
else
{
$transactiontype;
switch($temp_type)
{
case 1: $transactiontype='net sales'; break;
case 2: $transactiontype='net return'; break;
case 3: $transactiontype='transfer in'; break;
case 4: $transactiontype='trasnfer out'; break;
}
echo '<div class="listitem"><div>'.$temp_stock.'</div><div>'.$temp_stock_after.'</div><div>'.$transactiontype.'</div><div>'.$temp_date.'</div></div>';
$temp_date=$current_date;
$temp_type=$current_type;
$temp_stock_after=$current_after;
$temp_stock=$current_stock;
}
$id++;
if($id>$stocklog->num_rows) {
$transactiontype;
switch($temp_type)
{
case 1: $transactiontype='net sales'; break;
case 2: $transactiontype='net return'; break;
case 3: $transactiontype='transfer in'; break;
case 4: $transactiontype='trasnfer out'; break;
}
echo '<div class="listitem"><div>'.$temp_stock.'</div><div>'.$temp_stock_after.'</div><div>'.$transactiontype.'</div><div>'.$temp_date.'</div></div>';
}
}
