Sending a SQL command with one click of an HTML button through PHP

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>';`

Answer 2

From your html code, your form looks empty.

You need to add the data to your html form. If you want to avoid the user to see you can use fields. Like it was in the comments said, use $variableName instead of ? in your query. Don't forget use \"$variableName\" to avoid mysql injections.

I took a second reading of your code: You realy should read a php book completly before you program stuff for productive company websites. There are beginner mistakes in your code. And some beginner mistakes leads to insecure websites. I hope this doesn't look an offense, but like an advise.