Running two select queries in php and encoding it in json format?

Question

I have two tables billing_all and payment_details. I am trying to write php code such that I can run the 2 queries in the same php code and encode their data in json format.I am currently using the following code to achieve that without json encode :-

<?php
        include "config.php";
        $dbname ="webappdb";
        $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$dbname);
         if(!$con)
         {
           echo "Connection Error".mysqli_connect_error();
         }
         else{
        //echo "";
         }
$sql = "SELECT SUM(total_wt) FROM `billing_all` WHERE date ='15-Apr-2016';";
$sql .= "SELECT pymt_amount, mode_of_pymt FROM  `payment_details` WHERE DATE ='15-Apr-2016';";
// Execute multi query
if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con)) {
      // Fetch one and one row
      while ($row=mysqli_fetch_row($result))
        {
        printf("%s%s\n",$row[0],$row[1]);
        }
      // Free result set
      mysqli_free_result($result);
      }
    }
  while (mysqli_next_result($con));
}
mysqli_close($con);
?>

How can I encode the received data in json format? I tried something like:-

 // Store first result set
    if ($result=mysqli_store_result($con)) 
    {
    $r = array();
    while($row = mysqli_fetch_array($result))
    {
        array_push($r,
        array('total_wt'=>$row[0],
        'pymt_amount'=>$row[0],$row[1],
        'mode_of_pymt'=>$row[0],$row[1]));
    }

    echo json_encode(array("result"=>$r));

Which does not give me the expected result.How can I encode the data in the right way? The following is the structure for the 2 tables

I am new to programming any help is appreciated.Thank you?

What give you this line echo json_encode(array("result"=>$r)); ? — Nutshell
– Nutshell, Commented Apr 28, 2016 at 7:20
It looks like you can use JOIN to avoid having to query the database twice to get the required results. Is there any way that those two tables can be joined? A foreign key or something? — dimlucas
– dimlucas, Commented Apr 28, 2016 at 7:23
can need to use Join instead of concatenating both queries, you cannot execute two queries at the same string. for Json try like this $sth = mysqli_query("SELECT ..."); $rows = array(); while($r = mysqli_fetch_assoc($sth)) { $rows[] = $r; } print json_encode($rows); — Khalid Habib
– Khalid Habib, Commented Apr 28, 2016 at 7:32
@dimlucas the first query returns a single value while the 2nd query returns two values how can I join the two? Also the date can be the same in both the places so join can be done on date. — user3295583
– user3295583, Commented Apr 28, 2016 at 7:32
It is possible to join the two tables. Can you add the structure of both tables to your original post? — dimlucas
– dimlucas, Commented Apr 28, 2016 at 7:34

Khalid Habib · Accepted Answer · 2016-04-28 09:30:17Z

1

<?php

//Joining both tables on the basis of doc_no column

 $sql = "SELECT billing_all.SUM(total_wt) AS total
        ,payment_details.pymt_amount
        ,payment_details.mode_of_pymt
    FROM billing_all
        ,payment_details
    WHERE billing_all.doc_no = payment_details.doc_no
        AND billing_all.DATE = '15-Apr-2016'";


// Executing query
$res = mysqli_query($con,$sql); 

//initializing array to store result
$rows = array(); 

//Iterating result and storing each row in $r then array $rows to covert result set into array because json accept array as parameter.

while($r = mysqli_fetch_assoc($res)) 
{
    $rows[] = $r; 
}

//print whole array in json format
echo  json_encode($rows); ?>

edited Apr 28, 2016 at 9:30

answered Apr 28, 2016 at 8:09

Khalid Habib

1,1561 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running two select queries in php and encoding it in json format?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related