• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

PHP CRUD View Related Records Instead of Specific Record in Page

C

CarpeCodice

Coder
Hi!

I have this CRUD project that I am working on, a very typical one. So the main page is a COMMODITIES page where a list of commodities appears:

01.JPG
If you click on VIEW button next to a particular COMMODITY, it would typically show the following:

02.JPG
But I would like it to show something different - a list of all MARKET PRICES for the chosen COMMODITY like in the image below (this image is not filtered yet by commodity):

03.JPG
So, I have managed to build the page in this last image, but I have not managed to figure out the coding required to show only the market prices related to the specific commodity where I clicked the VIEW button in the first image. As a bonus, I would also like to be able to show the selected commodity in the title in this third image, something like "Market Prices for Vodka Smirnoff Red 70cl".

In MySQL I have created the relationship - see below image - I think this is created as a one-to-many, but I am not certain:

04.JPG
And the code of the Market Prices page follows below - any help would be greatly appreciated - thank you in advance!:

Code: 
<?php

    session_start();
    require 'dbcon.php';

?>

<?php include('includes/header.php'); ?>

    <h1>Costing System</h1>

    <div class="container mt-4">

      <?php include('message.php'); ?>

      <div class="row">
        <div class="col-md-12">
          <div class="card-header">
            <h4>Market Prices
              <a href="mp_add.php" class="btn btn-primary float-end">Add Market Price</a>
            </h4>
          </div>
          <div class="card-body">
            <table class="table table-bordered table-striped">
              <thead>
                <tr>
                  <th>ID</th>
                  <th>Supplier</th>
                  <th>Purchase Unit</th>
                  <th></th>
                  <th></th>
                  <th>Price</th>
                  <th>Price Date</th>
                  <th>Notes</th>
                  <th>Action</th>
                </tr>
              </thead>
              <tbody>
                <?php
                  $query = "SELECT * FROM marketprices";
                  $query_run = mysqli_query($con,$query);

                  if(mysqli_num_rows($query_run) > 0) {
                    foreach($query_run as $marketprice) {
                      ?>
                        <tr>
                          <td><?= $marketprice['id'] ?></td>
                          <td><?= $marketprice['supplier'] ?></td>
                          <td><?= $marketprice['p_unit_primary'] ?></td>
                          <td><?= $marketprice['p_unit_secondary'] ?></td>
                          <td><?= $marketprice['p_unit_unit'] ?></td>
                          <td><?= $marketprice['price'] ?></td>
                          <td><?= $marketprice['price_date'] ?></td>
                          <td><?= $marketprice['notes'] ?></td>
                          <td>
                            <a href="mp_view.php?id=<?= $marketprice['id'] ?>" class="btn btn-info btn-sm">View</a>
                            <a href="mp_edit.php?id=<?= $marketprice['id'] ?>" class="btn btn-success btn-sm">Edit</a>
                            <form action="code.php" method="POST" class="d-inline">
                              <button type="submit" name="delete-marketprice" value="<?= $marketprice['id']; ?>" class="btn btn-danger btn-sm">Delete</button>
                            </form>
                          </td>
                        </tr>
                      <?php
                    }
                  } else {
                    echo "<h5> No Records Found </h5>";
                  }
                ?>
              </tbody>
            </table>

          </div>
        </div>
      </div>
    </div>

    <?php include('includes/footer.php'); ?>
 
Last edited:
Solution
C
I think that I have figured it out. In the MARKETPRICES page, I changed the following code:

Code: 
<?php
    $query = "SELECT * FROM marketprices";
    $query_run = mysqli_query($con,$query);

    if(mysqli_num_rows($query_run) > 0) {
        foreach($query_run as $marketprice) {
            ?>

to the following:

Code: 
<?php
    $commodity_id = mysqli_real_escape_string($con,$_GET['id']);
    $query = "SELECT * FROM marketprices WHERE commodity_id='$commodity_id'";
    $query_run = mysqli_query($con,$query);

    if(mysqli_num_rows($query_run) > 0) {
        foreach($query_run as $marketprice) {
            ?>

And now only market prices relating to the selected commodity are showing:

01.JPG
However, now I have a new problem. When...
Sort by date Sort by votes
I think that I have figured it out. In the MARKETPRICES page, I changed the following code:

Code: 
<?php
    $query = "SELECT * FROM marketprices";
    $query_run = mysqli_query($con,$query);

    if(mysqli_num_rows($query_run) > 0) {
        foreach($query_run as $marketprice) {
            ?>

to the following:

Code: 
<?php
    $commodity_id = mysqli_real_escape_string($con,$_GET['id']);
    $query = "SELECT * FROM marketprices WHERE commodity_id='$commodity_id'";
    $query_run = mysqli_query($con,$query);

    if(mysqli_num_rows($query_run) > 0) {
        foreach($query_run as $marketprice) {
            ?>

And now only market prices relating to the selected commodity are showing:

01.JPG
However, now I have a new problem. When I click on VIEW or EDIT next to one of the suppliers, I get this page properly:

02.JPG

But when I click on BACK to return to the MARKETPRICES page for that commodity, I get this page with NO RECORDS FOUND:

03.JPG

The code is the following for the page with the BACK button follows below:

Code: 
<?php

    require 'dbcon.php';

?>

<?php include('includes/header.php'); ?>

    <h1>Costing System</h1>

    <div class="container mt-5">
        <div class="row">
            <div class="col-md-12">
                <div class="card">
                    <div class="card-header">
                        <h4>View Market Price
                            <a href="marketprices.php?id=<?= $commodity['id'] ?>" class="btn btn-danger float-end">BACK</a>
                        </h4>
                    </div>
                    <div class="card-body">
                        <?php
                          if(isset($_GET['id'])) {
                            $marketprice_id = mysqli_real_escape_string($con,$_GET['id']);
                            $query = "SELECT * FROM marketprices WHERE id='$marketprice_id'";
                            $query_run = mysqli_query($con,$query);

                            if(mysqli_num_rows($query_run) > 0) {
                              $marketprice = mysqli_fetch_array($query_run);
                              ?>
                             
                                <div class="mb-3">
                                    <label>Supplier</label>
                                    <p class="form-control">
                                    <?= $marketprice['supplier']; ?>
                                    </p>
                                </div>
                                <div class="mb-3">
                                    <label>Purchase Unit [Primary]</label>
                                    <p class="form-control">
                                    <?= $marketprice['p_unit_primary']; ?>
                                    </p>
                                </div>
                                <div class="mb-3">
                                    <label>Purchase Unit [Secondary]</label>
                                    <p class="form-control">
                                    <?= $marketprice['p_unit_secondary']; ?>
                                    </p>
                                </div>
                                <div class="mb-3">
                                    <label>Purchase Unit [Unit]</label>
                                    <p class="form-control">
                                    <?= $marketprice['p_unit_unit']; ?>
                                    </p>
                                </div>
                                <div class="mb-3">
                                    <label>Price</label>
                                    <p class="form-control">
                                    <?= $marketprice['price']; ?>
                                    </p>
                                </div>
                                <div class="mb-3">
                                    <label>Price Date</label>
                                    <p class="form-control">
                                    <?= $marketprice['price_date']; ?>
                                    </p>
                                </div>
                                <div class="mb-3">
                                    <label>Notes</label>
                                    <p class="form-control">
                                    <?= $marketprice['notes']; ?>
                                    </p>
                                </div>
                             
                              <?php
                            } else {
                              echo "<h4> No Records With That ID Found </h4>";
                            }
                          }
                        ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <?php include('includes/footer.php'); ?>

Thanks
 
Last edited:
Upvote 0 Downvote
Solution
I do this a lot - I post a problem after hours of trying to figure out the solution without any avail, and then find the solution. So this is what worked - I changed the following code in the VIEW/EDIT page:

Code: 
<div class="card-header">
    <h4>View Market Price
        <a href="marketprices.php?id=<?= $commodity['id'] ?>" class="btn btn-danger float-end">BACK</a>
    </h4>
</div>

To this:

Code: 
<?php
    if(isset($_GET['id'])) {
    $marketprice_id = mysqli_real_escape_string($con,$_GET['id']);
    $query = "SELECT * FROM marketprices WHERE id='$marketprice_id'";
    $query_run = mysqli_query($con,$query);

    if(mysqli_num_rows($query_run) > 0) {
        $marketprice = mysqli_fetch_array($query_run);
        ?>

            <h4>View Market Price
                <a href="marketprices.php?id=<?= $marketprice['commodity_id'] ?>" class="btn btn-danger float-end">BACK</a>
            </h4>

            <?php
        } else {
        echo "<h4> No Records With That ID Found </h4>";
        }
    }
    ?>

And it worked. Thank you to whoever took the time to read the thread.
 
Upvote 0 Downvote

New Threads

Latest posts

Share this page

Buy us a coffee!

300x250
Top Bottom