#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    64
    Rep Power
    3

    Exclamation [PHP] Display data from foreign keys


    PHP Code:
    $sql3 "SELECT * FROM products, product_category, supplier WHERE products.categoryID = product_category.id ";
    $query3 mysqli_query($con$sql3) or die(mysqli_error($con)); 
    Hello guys, I want to display my product_category's name instead of ID when displaying all product data in my page


    I want to display like :

    ----Code-----Name-----Category-----Supplier------------Info----Quantity----Price
    -----001-----Biscuits----Biscuit---------Farmers Co. -----N/A-----20-----------10

    (Which is Biscuit ID is 1 and Farmer Co's ID is 1

    instead of :
    ----Code-----Name-----Category-----Supplier------Info----Quantity----Price
    -----001-----Biscuits----1----------------1 --------------N/A-----20-----------10
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    64
    Rep Power
    3
    UPDATE : My overall current code is ...

    PHP Code:
    <?php 
    include('./_core/db.config.php');
    if (!isset(
    $_SESSION['user'])) {
        
    header("Location: index.php");
        exit;
    }

    $username $_SESSION['user'];

    //Product Category query
    $sql1 "SELECT * FROM product_category";
    $query1 mysqli_query($con$sql1) or die(mysqli_error($con));
    $row1 mysqli_fetch_array($query1);

    $prodCatID $row1['id'];

    //Supplier query
    $sql2 "SELECT * FROM supplier";
    $query2 mysqli_query($con$sql2) or die(mysqli_error($con));
    $row2 mysqli_fetch_array($query2);

    $suppID $row2['id'];

    //Products  query
    $sql3 "SELECT * FROM products";
    $query3 mysqli_query($con$sql3) or die(mysqli_error($con));

    if(isset(
    $_POST['delete'])) {
        
    $delVar $_POST['rowDel'];
        
    $dSQL "DELETE FROM products WHERE id = '$delVar'";
        
    mysqli_query($con$dSQL) or die(mysqi_error($con));
        
    header('Location: inventory.php');
    }

    ?>

    <!DOCTYPE html>
    <html>
    <head>
        <title>Inventory</title>
        <link href="./_css/home.css" rel="stylesheet" />
        <link href="./_css/icon.css" rel="stylesheet" />
    </head>
    <body>
    <?php include('side.php'); ?>
    <div class="wrapper">
        <div class="wrapper-title">Inventory System</div>
        <div class="t-wrapper">
            <div class="t-header">Inventory System</div>
            <div style="float:left">
                <a href="addProduct.php"><button class="add">add product</button></a> <button class="add">add category</button> <button class="add">add supplier</button>
            </div>
            <form action="inventory.php" method="post">
                <div style="float:right">
                    <button class="remove" name="delete">Delete</button>
                </div>
                <table class="i-table" border="0">
                    <tr style="background-color: #E06F6F; color: #FFF;text-align: center;">
                        <td width="5%">Select</td>
                        <td width="5%">Code</td>
                        <td width="20%">Name</td>
                        <td width="10%">Category</td>
                        <td width="15%">Supplier</td>
                        <td width="20%">Info</td>
                        <td width="10%">Quantity</td>
                        <td width="10%">Price</td>
                    </tr>

                    <?php 
                        
    while($row3 mysqli_fetch_array($query3)) {
                            echo 
    '<tr class="tr-repeat">';
                            echo 
    '<td><input type="checkbox" name="rowDel" value="'.$row3['id'].'"></td>';
                            echo 
    '<td>'.$row3['code'].'</td>';
                            echo 
    '<td>'.$row3['name'].'</td>';
                            echo 
    '<td>'.$row1['name'].'</td>';
                            echo 
    '<td>'.$row2['name'].'</td>';
                            echo 
    '<td>'.$row3['info'].'</td>';
                            echo 
    '<td>'.$row3['quantity'].'</td>';
                            echo 
    '<td>'.$row3['price'].'</td>';
                            echo 
    '</tr>';
                        }
                    
    ?>
                </table>
            </form>
        </div>
    </div>
    </body>
    </html>
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,238
    Rep Power
    601
    First please wrap your code in the proper tags, [PHP] not [CODE]. See the sticky at the top of this forum that says READ THIS BEFORE POSTING.

    As for your question, there are 2 possibilities. The best is probably to add another table to the schema that relates the ID to the product then use a join in the query to extract it. Alternatively, if the product names never change, create an array with the product names and use the ID as the index in that array.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2015
    Location
    Philippines
    Posts
    64
    Rep Power
    3
    Originally Posted by gw1500se
    First please wrap your code in the proper tags, [PHP] not [CODE]. See the sticky at the top of this forum that says READ THIS BEFORE POSTING.

    As for your question, there are 2 possibilities. The best is probably to add another table to the schema that relates the ID to the product then use a join in the query to extract it. Alternatively, if the product names never change, create an array with the product names and use the ID as the index in that array.
    Oops sorry. I didn't noticed that I clicked [CODE] instead of [PHP]

    Thank you! You saved me.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,238
    Rep Power
    601
    You can edit your original post to fix it.

    Comments on this post

    • markroberts agrees : I got it.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo