Json php convert error

PHP
1

Table:

tid 	vid		 dates		 type
---------------------------------------
 1	TN01VD2365	2017-05-01	Cash
 2	TN01VD1254	2017-05-02	Cash
 3	TN03JG2589	2017-05-01	Credit
 4	TN12KL5874	2017-05-01	Cash
 5	TN14DS4569	2017-05-05	Compliment
 6	TN45KJ6987	2017-05-06	Credit
 7	TN45AS6542	2017-05-06	Cash
 8 	TN78DF6589	2017-05-10	Complimant

<?php
require "db_config.php";
$json="";

if(isset($_POST['created_date1'])){
	$created_date1= $_POST['created_date1'];
}

if(isset($_POST['created_date2'])){
	$created_date2= $_POST['created_date2'];
}

if(isset($_POST['emp_name'])){
	$driver_name= $_POST['emp_name'];
}

$created_date1="5-1-2017";
$created_date2="5-10-2017";

if(isset($driver_name))	
{
	//get eid from emp_name
	$getEmpname="SELECT eid FROM add_employees WHERE name='".$driver_name."'";
	$getEid=mysql_query($getEmpname);
	$getRes=mysql_fetch_array($getEid);
	$emp_id=$getRes['eid'];
}


if(isset($created_date1))
{
	$x=explode("-",$created_date1);
	$created_date1=$x[1]."-".$x[0]."-".$x[2];
	$created_date1 = strtotime($created_date1);
	$created_date1 = date('Y-m-d',$created_date1);	
}

if(isset($created_date2))
{
	$y=explode("-",$created_date2);
	$created_date2=$y[1]."-".$y[0]."-".$y[2];
	$created_date2 = strtotime("$created_date2");
	$created_date2 = date('Y-m-d',$created_date2);
}

$date2=$created_date2;
$i=0;
while($created_date1<=$date2)
{
	$created_date2=$created_date1;
	
		$mycount = "select 
							count(tid) as mycount 
							
							from 
							
							third_table 
						
							where 
						
							dates BETWEEN '".$created_date1."' AND '".$created_date2."'";
	
	
	$execte=mysql_query($mycount);
	$mynum=mysql_fetch_array($execte);
	$mynum_count=$mynum['mycount'];
	
	if($mynum_count>0)
	{
		$trip_per_day="select 
					
							a.eid,a.name,
							
							COUNT(b.tid) AS trips_per_day, 
							
							SUM(b.type_of_trip='Cash') as cash_trips, 
							
							SUM(b.type_of_trip='Credit') as credit_trips, 
							
							SUM(b.type_of_trip='Compliment') as compliment_trips 
							
							from 
							
							third_table b
							
							LEFT JOIN add_employees a ON b.emp_id=a.eid
						
							where 
						
							b.dates BETWEEN '".$created_date1."' AND '".$created_date2."'
							
							GROUP by b.dates,b.tid";
			$run_qry=mysql_query($trip_per_day);
			while($row=mysql_fetch_assoc($run_qry)) 
			{
				$name = $row['name'];
				$trip_per_day = $row['trips_per_day'];
				$cash_trips = $row['cash_trips'];
				$credit_trips = $row['credit_trips'];
				$compliment_trips = $row['compliment_trips'];
			}
				
				$arr[$i]["name"]=$name;
				$arr[$i]["date"]=$created_date1;//particular data
				$arr[$i]["trips_per_day"] = $trip_per_day;//for trip_per_day
				$arr[$i]["cash_trips"] = $cash_trips;//for cash_trips
				$arr[$i]["credit_trips"] = $credit_trips;//for credit_trips
				$arr[$i]["compliment_trips"] = $compliment_trips;//for compliment_trips
			
	}
			
			$inc_qry="select '".$created_date1."' + INTERVAL 1 DAY";
			$query=mysql_query($inc_qry);
			while($val=mysql_fetch_array($query))
			{
				$created_date1=$val[0];
			}
			$i++;
}

	$json['all_counts_reports']=$arr;
	$json=json_encode($json);
	$array = json_decode($json,true);
	$array['all_counts_reports']=array_values($array['all_counts_reports']);
	//var_export(json_encode($array));
	echo $result = str_replace('', '', json_encode($array));

?>

when i this query i get the exact result but i want to pass the json to my android app. so i just convert into json format but

there is problem with convert. It show always first single row data.

i want the result like this

dates		 typeCash		typeCredit		typeCompliment
---------------------------------------------------------------
2017-05-01	     2		     1		       0
2017-05-02	     1		     0		       0
2017-05-05	     0		     0		       1
2017-05-06	     1		     1		       0
2017-05-10	     0		     0		       1
2

because that’s the only one you fetch.

3

But i want records based on my expected o/p like given in the post

4

i use the while loop also but its still fetching only one record

5

you still fetch only one result per query.

6

This bit of code

while($row=mysql_fetch_assoc($run_qry)) 
		{
			$name = $row['name'];
			$trip_per_day = $row['trips_per_day'];
			$cash_trips = $row['cash_trips'];
			$credit_trips = $row['credit_trips'];
			$compliment_trips = $row['compliment_trips'];
		}

			$arr[$i]["name"]=$name;
			$arr[$i]["date"]=$created_date1;//particular data
			$arr[$i]["trips_per_day"] = $trip_per_day;//for trip_per_day
			$arr[$i]["cash_trips"] = $cash_trips;//for cash_trips
			$arr[$i]["credit_trips"] = $credit_trips;//for credit_trips
			$arr[$i]["compliment_trips"] = $compliment_trips;//for compliment_trips

retrieves each row in your while() loop, but doesn’t add them into the array until after the loop has finished. So each time you run the second query, you discard all but the last row that it returns, and that last row is stored in the $arr array. Is that what you intended to do?

1 Like
7

I want to store all the rows

8

Then move the section of code where you add each row to the $arr array to be inside the while() loop, rather than after you close it with the } on line 8 in post #6.

1 Like
9

Ok Thank you

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.