Populate dropdownlist php with separate sql query

Question

I have a form that allows users to update their client's transactions. This form comes populated with the information already listed for the transaction. I want to have a drop down list for the customer names so that they have to choose an existing customer.

My problem is that the sql to populate the answers with the existing information conflicts with the sql to bring up the customer names in the database.

Is there a way to do this? Here is the code I have that brings up the populated answers:

<table> 

<?php
if (!isset ($p_submitval))
{
    $sql = "SELECT crid, c.custid, 
firstname, lastname, cramount, crdate
            FROM databau7_thomand.customer 
c inner join databau7_thomand.cashrec r on 
c.custid = r.custid WHERE crid= " . 
$p_crid;
    $result = mysqli_query($con, $sql);
    while($row = 
mysqli_fetch_array($result))

    echo "<h2>Update Customer 
Information</h2>\n";
echo "<p><i>Please update the customer information.</i><br>\n";
echo "<form action=\"web4_transactioninput.php\" method=post>\n";
echo "<input type=\"hidden\" name=\"crid\" value = \"$p_crid\">\n";
echo "<table border=\"3\"> \n";

echo "<td align=\"right\"><b>Customer Name: 
</td>";
    echo "<td><select name=\"custid\" 
class=\"dropdown\">";
    echo "<option value=\"0\" selected> 
".$row['firstname']." ".$row['lastname']."  
</option>\"";
    echo "<tr>\n<td align=\"right\">\n";
    echo "<b>Amount:</b>\n";
    echo "</td>\n<td>\n";
    echo "<input type=\"text\" 
name=\"cramount\"size=\"30\"  
value=\"".$row['cramount']."\">\n";
    echo "</td>\n</tr>\n";echo "<tr>\n<td 
align=\"right\">\n";
    echo "<b>Date:</b>\n";
    echo "</td>\n<td>\n";
    echo "<input type=\"date\" 
name=\"crdate\"size=\"30\"  
value=\"".$row['crdate']."\">\n";
    echo "</td>\n</tr>\n";echo "<tr>\n<td 
align=\"right\">\n";

}
    while($row = 
mysqli_fetch_array($result))

    echo "</select>\n</td>\n</tr>\n";
    echo "</table>\n";

Thank you for the help!

You don't have to echo html in your php script. Your life would be easier if you didn't. — bassxzero
– bassxzero, Commented Oct 23, 2018 at 17:19

Strawberry · Accepted Answer · 2018-10-23 18:09:34Z

Remove ALLLLLL the echo "..."; for html portion as you don't need it. Just a recommendation. Much cleaner view and unnecessary

You also should be using prepared statements as inserting a variable a parameter is unsafe.

This is a really rough example, but check it out this way:

<table> 

<?php
if (!isset ($p_submitval)){

    $sql = "
SELECT crid
     , c.custid
     , firstname
     , lastname
     , cramount
     , crdate
  FROM databau7_thomand.customer c 
  JOIN databau7_thomand.cashrec r 
    on c.custid = r.custid 
 WHERE crid= ?;
";

    $stmt = $con->prepare($sql);
    $stmt->bind_param("s",$p_crid);
    $stmt->execute();
    $stmt->bind_result($crid,$custid,$fname,$lname,$cramount,$crdate);
    $stmt->store_result();

?>
<h2>Update Customer 
Information</h2>";
<p><i>Please update the customer information.</i><br>
<form action="web4_transactioninput.php" method=post>
<input type="hidden" name="crid" value = "<?php echo $p_crid;?>">
<table border="3"> 

<td align="right"><b>Customer Name: </td>
<td><select name="custid" class="dropdown">";
    <?php
       echo "<option value=\"0\" selected> $fname $lname</option>";
    ?>'
 <tr><td align="right">
    <b>Amount:</b>
    </td><td>
    <input type="text" name="cramount"size="30"  value="<?php echo $cramount;?>">;


<?php
    $stmt->close();
}

Collectives™ on Stack Overflow

Populate dropdownlist php with separate sql query

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