There are a couple of ways to go about this. You can either make multiple
queries, or you can use a UNION statement in SQL to unify all of your
queries. Using multiple queries would look like this (pseudocoded for
brevity):
<set up $table>
<create query 1>
<populate $table['rows'] from query1>
<create query 2>
<populate $table['rows'] from query2>
<create query 3>
<populate $table['rows'] from query3>
<create $jsonTable from $table>
Using a UNION statement:
$query = "SELECT
'nextoilchange' as type,
vehicle_reg.`oilchange`-SUM(gps.`Distance`) AS distance
FROM gps INNER JOIN vehicle_reg ON (gps.`DeviceId`=25) AND
(vehicle_reg.`DeviceId`=25) INNER JOIN LOG ON TIME BETWEEN
DATE(log.`lastoilchange`) AND CURDATE()
UNION
SELECT
'nextfilter' as type,
vehicle_reg.`filterchange`-SUM(gps.`Distance`) AS distance
FROM gps INNER JOIN vehicle_reg ON (gps.`DeviceId`=25) AND
(vehicle_reg.`DeviceId`=25) INNER JOIN LOG ON TIME BETWEEN
DATE(log.`lastfilterchange`) AND CURDATE()";
The while loop:
while ($nt = mysqli_fetch_array($result)) {
$temp = array();
$temp[] = array('v' => $nt['type']);
$temp[] = array('v' => $nt['distance']);
// insert the temp array into $rows
$rows[]['c'] = $temp;
}
On Saturday, November 30, 2013 1:00:22 PM UTC-5, Qurat Haider wrote:
>
> i am using jsonTable to parse the data in to the Google table and it is
> working fine. now i have a problem to add multiple queries at the same time
> and display the data only in two columns of array which is already defined.
> here is my code:
>
> $conn = mysqli_connect("$host", "$username", "$password","$db_name")or
> die("cannot connect");
> if (mysqli_connect_errno($conn))
> {
> echo "Failed to connect to MySQL: " . mysqli_connect_error();
> }
>
> $query = "SELECT vehicle_reg.`oilchange`-SUM(gps.`Distance`) AS
> Nextoilchange FROM gps INNER JOIN vehicle_reg ON (gps.`DeviceId`=25) AND
> (vehicle_reg.`DeviceId`=25) INNER JOIN LOG ON TIME BETWEEN
> DATE(log.`lastoilchange`) AND CURDATE()";
>
> // $query.= "SELECT vehicle_reg.`filterchange`-SUM(gps.`Distance`) AS
> filterchange FROM gps INNER JOIN vehicle_reg ON (gps.`DeviceId`=25) AND
> (vehicle_reg.`DeviceId`=25) INNER JOIN LOG ON TIME BETWEEN
> DATE(log.`lastfilterchange`) AND CURDATE()";
>
> $result = mysqli_multi_query($conn, $query);
> if ($result) {
> do {
> // grab the result of the next query
> if (($result = mysqli_store_result($conn)) === false &&
> mysqli_error($conn) != '') {
> echo "Query failed: " .mysqli_error($conn);
> }
> } while (mysqli_more_results($conn) && mysqli_next_result($conn)); //
> while there are more results
> } else {
> echo "First query failed..." .mysqli_error($conn);
> }
>
>
> $table = array();
> $table['cols'] = array(
> array('label' => 'Vehicle', 'type' => 'number'),
> array('label' => 'Distance Left', 'type' => 'number')
>
> );
> $rows = array();
> while ($nt = mysqli_fetch_array($result))
>
> {
>
> $temp = array();
>
> $temp[] = array('v' => 'Nextoilchange');
> $temp[] = array('v' =>$nt['Nextoilchange']);
>
> // insert the temp array into $rows
> $rows[]['c'] = $temp;
>
> }
> $table['rows'] = $rows;
> $jsonTable = json_encode($table);
>
> // echo $jsonTable;
> mysqli_close($conn);
>
> i want to display the table like:
>
> Vehicle Distance Left
> ---------------------------------
> nextoilchange 500
> nextfilter 300
> nextcheckup 400
>
> i am confused how to use multiple queries and each query gives one value in
> distance left. if any one knows how to solve this problem please tell me.
> Thanks
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.
