CODEWITHSUNDEEP
phpjquerymysqlajaxjson
TTC1
TTC1

Reputation: 71

MySQL Combining two queries into JSON object

Consider these two queries:

$sql = "select rq1.itemNo, rq1.bid, rq1.bidDate 
from encan rq1 
left outer join encan rq2
on(rq1.itemNo = rq2.itemNo and rq1.bid < rq2.bid)
where rq2.itemNo is null
order by itemNo";

$sql2 = "SELECT itemNo, count(*) as totalBid 
FROM encan 
GROUP BY itemNo 
ORDER BY itemNo ASC;";

The first query gives me highest bid with the itemNo and the bidDate. Here's the JSON result:

[{"itemNo":"Item #1","bid":"140","bidDate":"2014-03-01"},{"itemNo":"Item #2","bid":"110","bidDate":"2014-01-26"},{"itemNo":"Item #3","bid":"70","bidDate":"2014-01-24"},{"itemNo":"Item #7","bid":"30","bidDate":"2014-01-30"},{"itemNo":"Item #8","bid":"40","bidDate":"2014-01-28"}]

The second one gives me the total # of bids for each itemNo. Here its result:

[{"itemNo":"Item #1","totalBid":4},{"itemNo":"Item #2","totalBid":3},{"itemNo":"Item #3","totalBid":3},{"itemNo":"Item #7","totalBid":2},{"itemNo":"Item #8","totalBid":2}]

I would like to have the total of bids included in the first query so I don't try to transfer to AJAX two different results. I am using the returned result to update data into my HTML.

I managed to figure out how to grab the result of the first query and manipulate it thru AJAX/jQuery and update my HTML. I don't think having a second AJAX call to get the second result is the proper way to go. Having both queries results in one JSON object returned would ease up on the coding of the frontend quite a bit.

Any help will be greatly appreciated. Thanks.

Upvotes: 0

Views: 1242

Answers (1)

Jared Farrish
Jared Farrish

Reputation: 49198

I would think you could just combine the queries:

SELECT 
   rq1.itemNo
 , rq1.bid
 , rq1.bidDate 
 , (
    SELECT COUNT(*)
    FROM encan rq3
    WHERE rq1.itemNo = rq3.itemNo
    GROUP BY rq3.itemNo 
) AS totalBid
FROM encan rq1 
LEFT OUTER JOIN encan rq2 ON (
  rq1.itemNo = rq2.itemNo 
  AND rq1.bid < rq2.bid
)
WHERE rq2.itemNo IS NULL
ORDER BY rq1.itemNo

If you want the bid counts to be a physically separate structure, do what CBroe suggests and put the result of both queries into the same array and json_encode() that array instead.

Upvotes: 1

Related Questions