Displaying Data

Question

I have a drop down list which is populated from a MYSQLi query. I want the user to select an option and the values/record associated pulled from the database and displayed to the user based upon FirstName.

my table does not have an id column (I didnt make any. Starts with FirstName)

It displays the table with column titles but it does not fetch the data dynamically from the db which happens to be coded in the while loop in [view code] below. I've spent my whole day and whole night trying to figure this out. Help :(

list code

<!DOCTYPE>
<html>
<head>
<title>Update Data</title>
</head>
<body>
<form name="form_update" method="post" action="update_test.php">
<?php
 $con=mysqli_connect("localhost","root","","ismat_db");
  //============== check connection
 if(mysqli_errno($con))
  {
echo "Can't Connect to mySQL:".mysqli_connect_error();
   }
else
{
echo "Connected to mySQL</br>";
 }

//This creates the drop down box
echo "<select name= 'FirstName'>";
echo '<option value="">'.'--- Please Select Person ---'.'</option>';
//$query=mysqli_query($con,"SELECT id,FirstName FROM persons");
$query = mysqli_query($con,"SELECT FirstName FROM persons");
//$query_display = mysqli_query($con,"SELECT * FROM persons");
while($row=mysqli_fetch_array($query))
 {
echo "<option value='". $row['id']."'>".$row['FirstName']
 .'</option>';
 }
echo '</select>';
 ?> <input type="submit" name="submit" value="Submit"/>
 </form>
 <br/><br/>
  <a href="main.html"> Go back to Main Page </a>
  </body>
  </html>

View Code

<!DOCTYPE>
<html>
<head>
<title>Update Data</title>
</head>
<body>

<?php 
 $con=mysqli_connect("localhost","root","","ismat_db");
 if(mysqli_errno($con))
 {
echo "Can't Connect to mySQL:".mysqli_connect_error();
  }

 if(isset($_POST['FirstName']))
  {
 $name = $_POST['FirstName'];

  $fetch="SELECT Firstname FROM persons WHERE Firstname = '".$name."'";
  $result = mysqli_query($con,$fetch);
  if(!$result)
   {
   echo "Error:".(mysqli_error($con));
    }
   //display the table
  echo '<table border="1">'.'<tr>'.'<td align="center">'. 'From Database'. '</td>'.'</tr>';
  echo '<tr>'.'<td>'.'<table border="1">'.'<tr>'.'<td>'.'First Name'.'</td>'.'<td>'.'
   LastName'.'</td>'.'<td>'. 'Gender' .'</td>'.'<td>'. 'Subject'. '</td>'.'<td>'.'
   Hobbies'  .'</td>'.'</tr>';

 //Supposed to collect data from db-I tried using _array,_assoc instead of _row 
 //and got   mysqli_result()
  //   requires one parameter given boolean

 while($data=mysqli_fetch_row($result))  
     {
         echo ("<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td> <td>$data[4] </td></tr>");
}
    echo '</table>'.'</td>'.'</tr>'.'</table>';
  }
 ?>

Answer 1

You are examining the 'FirstName' post-data, which is the value of the SELECT. However, this value will be the value from one of the OPTIONs, which is set to ID. You should put the FirstName's as the values of the OPTIONs.

Also, you are not currently retrieving the IDs from the database.

Answer 2

I realize I've used PDO instead of mySqli as you have. With that triviality aside, if I've understood your question, this should be more than enough to put you on the right track.

If you run it, you'll note that the only time the customers lastName is displayed is in response to a form submission, in which they were the chosen company.

testQuery.php

<?php
    // check if the name of the select box is present in the POST variables. if so, the form is being submitted.
    // If not, it's a fresh load of the page (the page has been requested by something other than itself)
    if (isset($_POST["selectedCustomer"]))
        $formSubmitted = true;
    else
        $formSubmitted = false;


    // connect to the mysql server
    $pdo = null;
    try
    {
        $userName = 'root';     // enter the appropriate values HERE
        $password = '';             // and HERE

        $pdo = new PDO('mysql:host=localhost', $userName, $password);
    }
    catch (PDOException $e)
    {
        die('Can\'t establish a connection to the database: ');
    }

    // create a database to answer thisa forum question
    $pdo->query("create database forumQuestion");
    $pdo->query("use forumQuestion");

    // create a table
    $queryStr = "CREATE TABLE IF NOT EXISTS `customer` (`id` int(11) NOT NULL AUTO_INCREMENT, `firstName` varchar(128) NOT NULL, `lastName` varchar(128) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ";
    $pdo->query($queryStr);

    // insert some data
    $queryStr = "INSERT INTO `customer` (`id`, `firstName`, `lastName`) VALUES (1, 'ILM 3d studios', 'Industrial Light & Magic'), (2, 'Airwalk clothing', 'Why walk on the ground?');";
    $pdo->query($queryStr);
?>

<!doctype html>
<html>
<head>
</head>
<body>
    <?php if ($formSubmitted == true)
        {
            $submittedCustomerId = $_POST["selectedCustomer"];

            $queryStr = "select * from customer where id = :id";
            $params = array(":id" => $submittedCustomerId);
            $query = $pdo->prepare($queryStr);
            $query->execute($params);

            $onlyResult = $query->fetch();
            printf("Form has been submitted.<br>");
            printf("id(%d), firstName(%s), lastName(%s)", $submittedCustomerId, $onlyResult["firstName"], $onlyResult["lastName"]);
        }
          else
            printf("Form awaiting submission");
    ?>
    <hr>
    <h2>Select a ccustomer</h2>
    <form action="testQuery.php" method="POST">
        <select name='selectedCustomer'>
            <option value='0'>-- Select a customer --</option>
            <?php
                $queryStr = "select * from customer";
                $query = $pdo->prepare($queryStr);
                $query->execute();
                while ($curCustomer = $query->fetch())
                {
                    printf("<option value='%d'>%s</option>", $curCustomer["id"], $curCustomer["firstName"]);
                }
            ?>
        </select>
        <input type='submit' value='submit form'/>
    </form>
</body>
</html>