0

I have two tables currencies and markets, I want to use id, last, volume, bid, trade_status from markets table and only Symbol from currencies table.

when I using the bellow code as I have volume and id in both tables, that is multiply times showing

    <?php
    $con = mysqli_connect("localhost","Dotland","passwd","Dotland");
    $response = array();
    if($con){
    $sql = "select * from markets, currencies";
    $result = mysqli_query($con,$sql);
    if ($result){
    header("content-Type: JSON");
    $i=0;
    while($row = mysqli_fetch_assoc($result)){
    $response[$i]['id'] = $row ['id'];
    $response[$i]['symbol'] = $row ['symbol'];
    $response[$i]['last'] = $row ['last'];
    $response[$i]['volume'] = $row ['volume'];
    $response[$i]['bid'] = $row ['bid'];
    $response[$i]['trade_status'] = $row ['trade_status'];

    $i++;
   }
   echo json_encode($response,JSON_PRETTY_PRINT);

   }
}
 else{
 echo "Database Connection Failed";
  }
?>
Poch
  • 1
  • 3
    Try selecting specific fields instead of `*`. Eg. `SELECT table1.id AS table1_id, table2.id AS table2_id` https://dev.mysql.com/doc/refman/8.0/en/select.html – ficuscr Feb 22 '22 at 22:20
  • 2
    You also will be better off using standard `join` syntax with defined join relationships by `on` statements. `select .... from markets as m join currencies as c on m.? = c.?` – user3783243 Feb 22 '22 at 22:24
  • @user3783243 can you please explain better? your mean is at this case $sql = "select * from markets as m join currencies as c on m.? = c.?"; – Poch Feb 22 '22 at 22:26
  • There is the `join` function/keyword which defines a join and you can go beyond that with `left` and `right` joins for returns when no match set existed if needed. You also can't define the relationship for your data set with the current approach. You should really define how the data is related the `on` function/keyword allows for that relation. The `?`s in my example are because I don't know which column in each table relates to the other. See https://stackoverflow.com/questions/1599050/will-ansi-join-vs-non-ansi-join-queries-perform-differently for more info – user3783243 Feb 22 '22 at 22:36

0 Answers0