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

Question

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" class="btn btn-primary">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>"; 
}

Why do you have term=? in the href instead of whatever you want to match with LIKE? — Barmar
– Barmar, Commented Jul 7, 2022 at 17:04
Change (would want this as the link i clicked) to ?. And fix the href URL to contain what you want to match. — Barmar
– Barmar, Commented Jul 7, 2022 at 17:07
I should have better explained that part. The href takes me to contracts.php, where the 3rd table will be. I would like the term to be term=(whichever contract number I clicked) such as one of the 3 in my second image posted. — ac4570
– ac4570, Commented Jul 7, 2022 at 17:38
@Barmar was going to change it after, I felt that LIKE would be easier to explain my question more clearly. I would like it to be WHERE Contract_Number = '8005' LIMIT 100"; WHERE Contract_Number = '9020' LIMIT 100"; or WHERE Contract_Number = '0001' LIMIT 100"; depending on which option I select in the example — ac4570
– ac4570, Commented Jul 7, 2022 at 18:27

Barmar · Accepted Answer · 2022-07-07 18:41:20Z

1

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.

answered Jul 7, 2022 at 18:41

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related