CODEWITHSUNDEEP
mysqlsqljsonjoin
Sahidul Islam
Sahidul Islam

Reputation: 89

mysql join and json output

I have to table like bellow

ProductTable

    +---------------------------------------------------+
    | id| itemName |itemColor|(some other specification)|
    +---------------------------------------------------+
    | 1 | item 1   | red     |        -------           |
    | 2 | item 2   | green   |  --------                |
    | 3 | item 3   | blue    |     -------              |
    +---------------------------------------------------+

ExchangeRateTable

+----------------------------------+
|id|itemFrom|itemTo|rateFrom|rateTo|
+----------------------------------+
| 1|   1    |   3  |   1    |  30  |
| 2|   1    |   2  |   30   |  20  |
| 3|   1    |   1  |   3    |  2   | 
| 4|   2    |   1  |   5    |  3   |
| 5|   2    |   3  |   6    |  10  |
| 6|   2    |   2  |   6    |   5  |
| 7|   3    |   1  |   1    |   1  |
| 8|   3    |   2  |   5    |   3  |
| 9|   3    |   3  |   2    |   1  |
+----------------------------------+

now lets say $itemfrom="item 2"(using $_GET) I need json output like bellow 

{
"itemName":"item 1",
"itemcolor":"red",
"exchangeFromRate":"5"
"exchangeToRate":"3"
},{
"itemName":"item 2",
"itemcolor":"green",
"exchangeFromRate":"6"
"exchangeToRate":"5"
},{
"itemName":"item 3",
"itemcolor":"blue",
"exchangeFromRate":"6"
"exchangeToRate":"10"
}

what is correct query to get output item name,specification and rates?I tried with "join" statement but could not configure it properly.

bellow is my scratches

     $itemfrom=$_GET['itemfrom'];
     $qry="select id from productTable where itemName='$itemfrom'"
     $result=mysqli_query($conn,$qry) or die("error in selecting ".mysqli_error($conn));
     while($row =mysqli_fetch_assoc($result))
            {
                $itemFromID=$row['id'];
            }
     $finalqry="select P.itemName,
                       P.itemColor,
                       R1.ratefrom
                       R2.rateto 
                       from ProductTable P
                       Join ExchangeRateTable R
                       R1.ratefrom=?????
                       R2.rateto=???????";
    $finalResult = mysqli_query($conn, $finalqry) or die("Error in Selecting " .mysqli_error($conn));

    while($row =mysqli_fetch_assoc($finalResult ))
    {
        $exchangeToNameRates[] = $row;
    }

    $exchangeToNameRateArrey =  ['status' =>"true", 
                        "message"=> "Data fetched successfully!", 
                        'data' =>$exchangeToNameRates];

echo json_encode($exchangeToNameRateArrey );

Upvotes: 1

Views: 82

Answers (1)

Behzad Ghanadi
Behzad Ghanadi

Reputation: 91

you didn't need twice query, you can use query like below and join tables with connector and condition :

SELECT  P.itemName,
        P.itemColor,
        R.rateFrom
        R.rateTo 
        from ProductTable P
        Join ExchangeRateTable R
        on R.itemFrom=P.id
        where P.itemName='$itemfrom'

** Update:**

your first query is ok and finds $itemFromID after that use this query you see all result for exchange :

SELECT  P.itemName,
        P.itemColor,
        R.rateFrom
        R.rateTo 
        from ProductTable P
        Join ExchangeRateTable R
        on R.itemTo=P.id
        where R.itemFrom='$itemFromID'

Upvotes: 2

Related Questions