使用外键查看关系表中的数据

Question

Trying to fetch data from users table by user_id which is the foreign key in vehicle table. The relation is working properly like as when I insert data in the vehicle table then user_id also inserting as expected, but the problem is when I want to fetch data from users table using user_id.

I used this SQL query for viewing data

 <?php 
  $qry = mysql_query("select vehicle. *, users.zip AS zip from vehicle  JOIN users ORDER BY id DESC ");
  while($data = mysql_fetch_array($qry))

 {?>


  <tr role="row" class="even">

      <td style="display:none;"><?php echo $data['id'];?></td>  

      <td class="sorting_1">  <?php $datess = $data['create_date'];
         $old_date_timestamp = strtotime($datess);
         echo date('m-d-Y', $old_date_timestamp);?>

      </td>
      <td><?php echo $data['bid_type'];?></td>

      <td><?php echo $data['zip'];?></td>
      <td>
        <a href="bid-detail.php?id=<?php echo base64_encode($data['id']);?>" class="btn btn-success btn-small">$ Make Offer</a>
      </td>

   </tr>

<?php
 }
 ?>

This query displaying data, but it's repeating one again and again.

Anyone can help me please to solve the issue or any other easy solution for this.

Answer 1

Issue in your query.

You have to add WHERE clause in your query which will match your PK of parent table with FK of child table.

Try This:

select v.*, u.zip AS zip from vehicle as v,users as u WHERE v.user_id=u.user_id ORDER BY v.id DESC