mysql join and json output

Question

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 );

Behzad Ghanadi · Accepted Answer · 2019-06-02 06:48:25Z

2

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'

edited Jun 2, 2019 at 6:48

answered Jun 2, 2019 at 4:46

Behzad Ghanadi

916 bronze badges

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

8 Comments

Sahidul Islam Over a year ago

Error: Unknown column 'R1.ratefrom' in 'field list'

Sahidul Islam Over a year ago

this is giving output { "itemName":"item 2", "itemcolor":"green", "exchangeFromRate":"5" "exchangeToRate":"3" },{ "itemName":"item 2", "itemcolor":"green", "exchangeFromRate":"6" "exchangeToRate":"5" },{ "itemName":"item 2", "itemcolor":"green", "exchangeFromRate":"6" "exchangeToRate":"10" }

Sahidul Islam Over a year ago

I mean output filtered depending $_GET value

Behzad Ghanadi Over a year ago

yes because of your condition $itemfrom="item 2" if you remove the last line in the query you got all combinations

Sahidul Islam Over a year ago

Maybe my explanation problem. I am creating a exchange system. In previous step user select exchange from item, that exchange from item is $itemfrom, in this step user will select "exchange to" item, this interface I have to show item name, Exchange from rate(that item is selected on previous interface)exchange to rate . Exchange to and from rate both will vary depending on current items

|

Collectives™ on Stack Overflow

mysql join and json output

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related