CODEWITHSUNDEEP
phpmysqlsqldatabaserelational
HDaniel999
HDaniel999

Reputation: 27

Relational MySQL PHP

I have a table named "prices" in which I have two fields where I reference two other tables named "prodcuts" and "supliers". In the "prices" table I do reference to the "id" of the "prodcuts" and "supliers" and I need to get the name of both in a php code... What I have so far is this, but I don't know how to make it work:

$result = mysqli_query($conn, "SELECT * FROM prices") or die("Could not find");

  if (mysqli_num_rows($result) > 0) {
     while ($rr = mysqli_fetch_assoc($result)) { 
         $a = $rr['id'];
         $b = $rr['date'];
         $c = $rr['product'];
         $d = $rr['suplier'];
         $e = $rr['quantity'];
         $f = $rr['packaging'];
         $g = $rr['event'];
         $h = $rr['price'];

         $prov .="
               <tr>                                  
                 <td>".$b."</td>
                 <td><a href='productos-result.php?search=".$c."'>".$c."</a></td>
                 <td><a href='proveedores-result.php?search=".$d."'>".$d."</a></td>
                 <td>".$e."</td>
                 <td>".$g."</td>
                 <td>$".$h."</td>    
               </tr>";
        }
}

I need to display the name of the product $c and suplier $d instead of the id, my tables are like this:

prices

id, date, productid, suplierid, quantity, event, price

product

id, name, description

suplier

id, name, description

Upvotes: 0

Views: 43

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Change your query to join the products and suppliers and have their names in the list of columns aliased to what you want.

SELECT prices.id,
       prices.date,
       product.name product,
       suplier.name suplier,
       prices.quantity,
       prices.packaging,
       prices.event,
       prices.price
       FROM prices
            INNER JOIN product
                       ON product.id = prices.productid
            INNER JOIN suplier
                       ON suplier.id = prices.suplierid;

(Change the INNER JOIN to LEFT JOIN if you have prices without supplier or product but want to show them anyway (with empty names).)

Upvotes: 1

Related Questions