通过PHP单击HTML按钮发送SQL命令

Question

So, I have a basic PHP site that brings up a list of salespeople from a MySQL server when a selection from a drop-down box is submitted. I've set up a button to appear next to each result, and I want a php script to run when the button is clicked using MySQL data from that specific result. Everything works except the button that runs the second MySQL query. Here's an example of the table after the first query:

<table border="1">
   <tr>
      <td>Last name</td>
      <td>First Name</td>
      <td>Job Title</td>
      <td>City</td>
      <td>Client List</td>
   </tr>
   <tr>
      <td>Bondur</td>
      <td>Gerard</td>
      <td>Sale Manager (EMEA)</td>
      <td>Paris</td>
      <td>
         <form method="POST" action="empLookup.php">
         <input type="submit" name="empLookup" value="Look up clients"
      </td>
   </tr>
</table>

By clicking on the button I would run a MySQL command like 'SELECT clients FROM blah WHERE employeeNumber = ?'

I don't have a problem with any of this except passing the value from the button to the PHP script.

This is what my PHP code looks like for handling the form submission and display of results. The button(s) in question are in the HTML table in the foreach loop.

<?php #this is the default php file for looking up Employees

$page_title = 'Our Associates by City';
require ('./pdoConn.php');
$sql = "SELECT DISTINCT city from Offices";

echo '<h1>Our Associates by City</h1>';

      Type in a Name to view Years</a><br>';
//create the form
echo 'Please select a year: <br>';
echo '<form action="index.php" method="post">';
echo '<select name= "city">';

foreach($conn->query($sql) as $row)
{
    //each option in the drop down menu is each and every year
    //brought up by the query
    echo '<option value ="'. $row['city'].' ">'. $row['city']. '</option>';
} //end of foreach
echo '</select>'; //end drop down menu

//now to create the submit button
echo '<br><input type="submit" name="submit" value="List"><br>';
echo '</form>'; //end of form

//This if statement runs when the submit button is clicked
if ($_SERVER[REQUEST_METHOD] == 'POST')
{
    $flit = $_POST[city]; //the city variable from the HTML form will be used
    echo '<br><br>';
    $sql2 = "SELECT employeeNumber,lastName,firstName,jobTitle,city
             FROM Employees,Offices
             WHERE Employees.officeCode = Offices.officeCode AND city = ?";
    $stmt = $conn->prepare($sql2);
    $stmt->execute(array($flit));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo 'Contact any of our local staff: <br>';

    //create a table of employees
    echo '<table border="1"><tr><td>Last name</td><td>First Name</td>';
    echo '<td>Job Title</td><td>City</td></tr>';

    //time to populate the table, this loop runs for each entry
    foreach($rows as $r)
    {
        echo '<tr><td>'.$r[lastName].'</td><td>'.$r[firstName].'</td><td>';
        echo $r[jobTitle].'</td><td>'.$r[city].'</td><td>';
        echo '<form method="POST" action="empLookup.php">';
        //now to make the button which will search the employee's client list
        echo '<input type="submit" name="empLookup" value="Look up clients"</td></tr>';
    } //end foreach
    echo '</table>';
 } //end if server request post thing
?>

Answer 1

I does not completely understood your exact requirement but I think you want employee number into your button if this is your requirement then you can simply check this code

`echo '<input type="submit" name="empLookup" value="'.$r['emp_id_from_database'].'"</td></tr>';`