Click Data from SQL Query to run another query with that data as LIKE 'clicked value'-CodePudding

I have a table on my site coming from an SQL JOIN query (from another shown table) and made a column of data that directs me to a different page. I would like to have a query where I select from another table based on LIKE (the data that was clicked) Does anyone have recommendations based on the below example?

Sorry if this question is a bit confusing, I am totally new to this.

First table (comes from a search)

require_once "db.php";
if(isset($_GET['term']))
    {   

    $term = '%' . $_GET['term'] . '%';
    $noresult = array(
      "MFG_Item_ID" => "",
      "MFG_Simple" => "No Results",
  );

    $sql = "SELECT * FROM Main_Search WHERE SearchName LIKE ? LIMIT 10";

    $stmt = $conn->prepare($sql); 
    $stmt->bind_param("s", $term);   
    $stmt->execute();
    $result = $stmt->get_result();
      while($row = $result->fetch_assoc()) {
        echo "<td>" . $row["MFG_Simple"] . "</td>";
        echo "<td>" . $row["Parent"] . "</td>";
        echo "<td>" . $row["MFG_Item_ID"] . "</td>";
        echo "<td>" . $row["Description"] . "</td>";
        echo "<td>" . $row["UOM"] . "</td>";
        echo "<td>" . $row["Conversion"] . "</td>";
        echo "<td>" . "$ " . $row["Average_Price"] . "</td>";
        echo "<td>" . "$ " . $row["Best_Price"] . "</td>";

         }
  
     }
?>

Second Table (shows multiple sources the first came from and I would like to be able to click on the contract number as the LIKE for the next query)

$sql1 = "SELECT Full_Item_Master.Source, Full_Item_Master.Contract_Number, Full_Item_Master.Price_Effective, 
Full_Item_Master.Price_Expiration, Full_Item_Master.UOM, Full_Item_Master.Conversion, Full_Item_Master.Average_Price, Full_Item_Master.Min_Price
FROM Full_Item_Master
JOIN Main_Search
ON Full_Item_Master.MFG_ID = Main_Search.MFG_ID
AND Full_Item_Master.MFG_Item_ID = Main_Search.MFG_Item_ID
AND Full_Item_Master.UOM = Main_Search.UOM
WHERE Main_Search.SearchName LIKE ? LIMIT 10";

$stmt = $conn->prepare($sql1); 
$stmt->bind_param("s", $term);   
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
    echo "<td>" . $row["Source"] . "</td>";
    echo "<td><a target='_blank' rel='noopener noreferrer' href='contracts.php?term=?'>" . $row["Contract_Number"] . "</td>";
    echo "<td>" . $row["Price_Effective"] . "</td>";
    echo "<td>" . $row["Price_Expiration"] . "</td>";
    echo "<td>" . $row["UOM"] . "</td>";
    echo "<td>" . $row["Conversion"] . "</td>";
    echo "<td>" . "$ " . $row["Average_Price"] . "</td>";
    echo "<td>" . "$ " . $row["Min_Price"] . "</td>"; ?>
        <td><button type="button" >View</button></td>
    </tr><?php

     }
$conn->close();

Here is what I have for the 3rd table

$term = '%' . $_GET['term'] . '%';
    

$sql = "SELECT * FROM Full_Item_Master WHERE Contract_Number LIKE (would want this as the link i clicked) LIMIT 100";

$stmt = $conn->prepare($sql); 
$stmt->bind_param("s", $term);   
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
    echo "<td>" . $row["MFG_Simple"] . "</td>";
    echo "<td>" . $row["Parent"] . "</td>";
    echo "<td>" . $row["MFG_Item_ID"] . "</td>";
    echo "<td>" . $row["Description"] . "</td>";
    echo "<td>" . $row["UOM"] . "</td>";
    echo "<td>" . $row["Conversion"] . "</td>";
    echo "<td>" . "$ " . $row["Average_Price"] . "</td>";
    echo "<td>" . "$ " . $row["Min_Price"] . "</td>"; 
}

CodePudding user response：

Put the contract number in the URL in the href.

    echo "<td><a target='_blank' rel='noopener noreferrer' href='contracts.php?number=" . $row["Contract_Number"] . "'>" . $row["Contract_Number"] . "</td>";

Then bind $_GET['number'] to the parameter in the query.

$contract = $_GET['number'];
$sql = "SELECT * FROM Full_Item_Master WHERE Contract_Number = ? LIMIT 100";=
$stmt = $conn->prepare($sql); 
$stmt->bind_param("s", $contract);

There's no need to use LIKE for this, since you just want to match a specific contract number, not a pattern.