How to make sql query to display 1 result based on id in the database table

Question

I need help, I cannot figure out, I cannot find why I am having errors and I am not able to achieve something freaking simple.

Long story short, I have a website to manage projects, so when I run the search function it throws a table with some records from the database, there is a button called "see details" which is assigned to a project id with database i.e. 21, 1, 48 etc, the problem is that when I click "see details" it throws everything from the table proposals instead of 1 project, no matter which button I click on, if its id 1, 21, 48, it prints everything.

details page details.php:

<?php 
         include '../includes/config.php';

                // Check connection
                  if($link === false){
                    die("ERROR: Could not connect. " . mysqli_connect_error());
                  }
                  // Attempt select query execution
                  $sql = "SELECT * FROM proposals_table WHERE id LIKE '_%'";


                  if($result = mysqli_query($link, $sql)){
                      if(mysqli_num_rows($result) > 0){
                          echo "<table class='table table-bordered'>";
                              echo "<tr>";
                                  echo "<th>Organisation</th>";
                                  echo "<th>Project</th>";
                                  echo "<th>Proposal Date</th>";
                                  echo "<th>Date Received</th>";
                                  echo "<th>Notes</th>";
                              echo "</tr>";
                          while($row = mysqli_fetch_array($result)){
                              echo "<tr>";
                                  echo "<td>" . $row['company'] . "</td>";
                                  echo "<td>" . $row['project'] . "</td>";
                                  echo "<td>" . $row['proposal_date'] . "</td>";
                                  echo "<td>" . $row['date_received'] . "</td>";
                                  echo "<td>" . $row['notes'] . "</td>";

                              echo "</tr>";
                          }
                          echo "</table>";
                          // Free result set
                          mysqli_free_result($result);
                      } else{
                          echo "No records matching your query were found.";
                      }
                  } else{
                      echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
                  }
                  ?>

search/result page proposals.php

      <?php 
         include '../includes/config.php';

                // Check connection
                  if($link === false){
                    die("ERROR: Could not connect. " . mysqli_connect_error());
                  }
                  // Attempt select query execution
                  $sql = "SELECT * FROM proposals_table";
                  if($result = mysqli_query($link, $sql)){
                      if(mysqli_num_rows($result) > 0){
                          echo "<table class='table table-bordered'>";
                              echo "<tr>";
                                  echo "<th>Organisation</th>";
                                  echo "<th>Project</th>";
                                  echo "<th>Proposal Date</th>";
                                  echo "<th>Date Received</th>";
                                  echo "<th>Options</th>";
                              echo "</tr>";
                          while($row = mysqli_fetch_array($result)){
                              echo "<tr>";
                                  echo "<td>" . $row['company'] . "</td>";
                                  echo "<td>" . $row['project'] . "</td>";
                                  echo "<td>" . $row['proposal_date'] . "</td>";
                                  echo "<td>" . $row['date_received'] . "</td>";
                                  echo "<td> <a class='btn btn-primary' href='details.php?id={$row['id']}'>See details</a></td>";
                              echo "</tr>";
                          }
                          echo "</table>";
                          // Free result set
                          mysqli_free_result($result);
                      } else{
                          echo "No records matching your query were found.";
                      }
                  } else{
                      echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
                  }
                  ?>

Dharman · Answer 1 · 2020-06-18T07:47:28.097

If you want to show only the selected element on your details page then you need to fetch only that selected item from the database.

First of all you should separate HTML from PHP. The best would be to have them in separate files. In PHP you prepare the data to be displayed and then in HTML you fill in the blanks with PHP values.

To select a value from MySQL using a given ID you must use prepared statements with parameter binding. So if you create your link in this way:

echo "<td> <a class='btn btn-primary' href='details.php?id=".urlencode($row['id'])."'>See details</a></td>";

You can receive this ID in your details page using $_GET['id']. You can bind that value to your WHERE clause in SQL.

<?php 
include '../includes/config.php';

// Attempt select query execution
$stmt = $link->prepare("SELECT * FROM proposals_table WHERE id=?");
$stmt->bind_param('s', $_GET['id']);
$stmt->execute();
$proposals = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);


if($proposals) {
?>
    <table class='table table-bordered'>
        <tr>
            <th>Organisation</th>
            <th>Project</th>
            <th>Proposal Date</th>
            <th>Date Received</th>
            <th>Notes</th>
        </tr>
    <?php foreach($proposals as $row): ?>
        <tr>
            <td><?=$row['company'] ?></td>
            <td><?=$row['project'] ?></td>
            <td><?=$row['proposal_date'] ?></td>
            <td><?=$row['date_received'] ?></td>
            <td><?=$row['notes'] ?></td>

        </tr>
    <?php endforeach; ?>
    </table>
<?php
} else {
    echo 'No records matching your query were found.';
}

And of course your config.php page should look like this:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli('localhost', 'user', 'pass', 'db');
$link->set_charset('utf8mb4'); // always set the charset

How to make sql query to display 1 result based on id in the database table

1 Answers1