Have HTML dropdown list in form, need to show value in MYSQL database along with non-selected options

I have a form with several dropdown lists and users can select an option and its saved in MYSQL.  However if the user later logs back and pulls up the same form, they should see the previously selected option in the dropdown (and if they hit the down arrow should see the other available options).  As it is now, the form dropdown is created from a query of options in the database (this was done due to the high number of pickable options in this dropdown), but does not reflect the value previously chosen.  Here's the code.  What makes this tricky is that the form itself is actually a table , and each row has its own drop down (select embedded picture).
 here's what the form looks like
Here's the code with comments:

<body>

<table>
  <tr>
        <th scope="col">Date</th> 
        <th scope="col">Amount</a></th>
        <th scope="col">Vendor</th>
        <th scope="col">Description</th>
        <th scope="col">Type</th>
        <th scope="col">Notes</th>
        <th scope="col">Bind Values?</th>
        <th scope="col">Trip</th>
        <th scope="col">Last Updated</th>
        <th scope="col">Updated By</th>
        <th scope="col">Done</th>
  </tr>


<form action="process_table.php" method="post">


  
<?php

[b]//queries table for relevant expenses for user based on login name[/b]

$transactions_query = "SELECT transactions_id, `date`, amount, vendor, description, `TYPE` , trip, last_updated, last_updated_by, done, notes\n"
    . "FROM users, transactions\n"
    . "WHERE users.user_name = '{$_SESSION["user"]}' AND users.users_id = transactions.users_id";


[b]//queries database for list of options in the dropdown, wraps them all up in a variable which is echoed later in code for row of the table form.[/b]

$type_query = "SELECT * FROM type order by type_name ASC";
$type_result = mysqli_query($connection, $type_query);

$type_options = NULL;


//continued -- my (feeble and incorrect) attempt to create two options: one for if the done column has been checked, thereby signaling that the drop should reflect the selected answer or just the normal dropdown

if($row["done"] == "checked") {
		while ($row = mysqli_fetch_assoc($type_result)) {
		$type_options .= sprintf("<option value='%s'>%s</option>".PHP_EOL, $row["type_name"], $row["type_name"]);
	
} else {
		while ($row = mysqli_fetch_assoc($type_result)) {
		$type_options .= sprintf("<option value='%s'>%s</option>".PHP_EOL, $row["type_name"], $row["type_name"]);
}


					
$transactions_result = mysqli_query($connection, $transactions_query);

while ($row = mysqli_fetch_assoc($transactions_result)) { 
$id = $row["transactions_id"];
	
	

?>




 <tr>
 <td><?php echo date("F d, Y", strtotime($row["date"]));?></td>
 <td><?php echo number_format($row["amount"], 2);?></td>
 <td><?php echo $row["vendor"];?></td>
 <td><?php echo $row["description"];?></td>
 
 
 <td><?php echo "<select name='expense[" . $id . "][type]'>" . $type_options . "</select>"?></td>
 
 
 <td><?php echo "<textarea name='expense[" . $id . "][notes]'>" . $row["notes"] . "</textarea>";?></td>
 
 <td><input name="bind" type="checkbox" value="YES"></td>
 
 <td nowrap="nowrap">Trip place holder</td>
 
 <td><?php echo date("m/d/Y")?></td>
     <td>
     <?php 
	 	if (!isset($_SESSION["user"])) {
			echo "temp"	;
		} else {
		  echo 	$_SESSION["user"];
		}
     
     ?>
     </td>

  
 <td><?php echo "<input name='expense[" . $id . "][done]' type='checkbox'"?> <?php  if($row["done"] == "checked") {
	 echo "checked = 'checked'>";
 } else {
		echo ">"; 
 }
 
 ?>
 
  </td>
 
 </tr>
 


 
<?php
}
?>




	
</table>





<input name="submit" type="submit" id="submit" >
</form>

</body>

Open in new window

LVL 1
LB1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
There are a few different ways to remember selections (the general design works for checkboxes, too).  You can have a column in the data base table for each element of the select list and use either zero (unselected) or non-zero (selected) to remember previous choices.  If you do not require any long-term memory, you can put this information into the PHP session and skip the data base.  I'll try to give you a session-based example in a moment.
0
LB1234Author Commented:
Thanks Ray, but as users may wish to come back to the form, days or weeks later, I cannot use a session -oriented approach.
0
GaryCommented:
Are these logged in users?
Just add another column to their profile to store the state of the select's
Or set a cookie to store the state.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

LB1234Author Commented:
Gary, the state of the select has already been stored.  I need to pull that value out and insert back into the form as the selected option, along with the rest of the options in the dropdown, should they need to select a new one.
0
GaryCommented:
while ($row = mysqli_fetch_assoc($type_result)) {
$row["done"] == "checked"?$selected=" selected":$selected="";
	$type_options .= sprintf("<option value='%s' ".$selected.">%s</option>".PHP_EOL, $row["type_name"], $row["type_name"]);
...
...

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Please see http://www.laprbass.com/RAY_temp_lb1234.php

<?php // RAY_temp_lb1234.php
error_reporting(E_ALL);

// USE THE SESSION TO REMEMBER THE SELECTIONS FROM ONE REQUEST TO THE NEXT
// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28247033.html

// ALWAYS START THE SESSION UNCONDITIONALLY ON EVERY PAGE
session_start();

// INITIALIZE THE EMPTY SELECTION DATA
if (!isset($_SESSION['pick'])) $_SESSION['pick'] = array();

// HERE ARE THE THINGS THAT WE CAN CHOOSE AMONG
$colors = array
( 'red'
, 'orange'
, 'yellow'
, 'green'
, 'blue'
, 'indigo'
, 'violet'
)
;

// IF THERE IS A REQUEST IN THE DROP-DOWN SELECT LIST
if (!empty($_POST['pick']))
{
    $_SESSION['pick'] = $_POST['pick'];

    // ACTIVATE THIS TO SHOW THE REMEMBERED SELECTION
    // var_dump($_SESSION['pick']);
}

// CREATE THE OPTIONS FOR THE SELECT LIST
$opts = NULL;
foreach ($colors as $c)
{
    // ONE OPTION TAG FOR EACH POSSIBLE SELECTION
    $opts .= '<option value="' . $c . '"';

    // IF THIS WAS PREVIOUSLY REMEMBERED
    if (in_array($c, $_SESSION['pick'])) $opts .= ' selected';
    $opts .= '>' . $c . '</option>';
    $opts .= PHP_EOL;
}

// CREATE THE SELECT LIST
$select = <<<EOD
<select name="pick[]">
$opts
</select>
EOD;

// CREATE THE FINAL HTML DOCUMENT
$html = <<<EOD
<form method="post">
$select
<input type="submit" />
</form>
EOD;

echo $html;

Open in new window

I don't have a data base example handy, but if you wanted to use the data base for long-term memory, you would just substitute a data base table for the session array.  The key to the row would be the client's id.  The choices in the row would be the $colors array.  You should be able to get the same functionality that way.
0
LB1234Author Commented:
Gary, I put your line of code in right before the part of the code that creates the row of the table with the drop down box, but for some reason I'm getting an error on $row["done"].  Notice: Undefined index: done in C:\wamp\www\expenses\table.php on line 68 and line "68" refers to the bolded line below.  Doesn't make any sense since the query for $row was already done earlier in the code.  
<body>

<table>
  <tr>
        <th scope="col">Date</th> 
        <th scope="col">Amount</a></th>
        <th scope="col">Vendor</th>
        <th scope="col">Description</th>
        <th scope="col">Type</th>
        <th scope="col">Notes</th>
        <th scope="col">Bind Values?</th>
        <th scope="col">Trip</th>
        <th scope="col">Last Updated</th>
        <th scope="col">Updated By</th>
        <th scope="col">Done</th>
  </tr>



<form action="process_table.php" method="post">


  
<?php

$transactions_query = "SELECT transactions_id, `date`, amount, vendor, description, `TYPE` , trip, last_updated, last_updated_by, done, notes\n"
    . "FROM users, transactions\n"
    . "WHERE users.user_name = '{$_SESSION["user"]}' AND users.users_id = transactions.users_id";
						

$type_query = "SELECT * FROM type order by type_name ASC";
$type_result = mysqli_query($connection, $type_query);

$type_options = NULL;




$transactions_result = mysqli_query($connection, $transactions_query);

while ($row = mysqli_fetch_assoc($transactions_result)) { 
$id = $row["transactions_id"];
?>



 <tr>
 <td><?php echo date("F d, Y", strtotime($row["date"]));?></td>
 <td><?php echo number_format($row["amount"], 2);?></td>
 <td><?php echo $row["vendor"];?></td>
 <td><?php echo $row["description"];?></td>
 
<?php 
 while ($row = mysqli_fetch_assoc($type_result)) {
[b]$row["done"] == "checked" ? $selected= " selected" : $selected="";[/b]
	$type_options .= sprintf("<option value='%s' ".$selected.">%s</option>".PHP_EOL, $row["type_name"], $row["type_name"]);
}
?>
 
 <td><?php echo "<select name='expense[" . $id . "][type]'>" . $type_options . "</select>"?></td>
 
 
 <td><?php echo "<textarea name='expense[" . $id . "][notes]'></textarea>";?></td>
 
 <td><input name="bind" type="checkbox" value="YES"></td>
 
 <td nowrap="nowrap">Trip place holder</td>
 <td><?php echo date("m/d/Y")?></td>
     <td>
     <?php 
	 	if (!isset($_SESSION["user"])) {
			echo "temp"	;
		} else {
		  echo 	$_SESSION["user"];
		}
     
     ?>
     </td>

  
 <td><?php echo "<input name='expense[" . $id . "][done]' type='checkbox' value='YES'>";?></td>
 
 </tr>
 
 
<?php
}
?>




	
</table>

Open in new window

0
LB1234Author Commented:
Nevermind, was using the same variable name $row. Duh.  Sorry.
0
Ray PaseurCommented:
I don't see how you could have gotten to line 68.  The script has a parse error on line 55.
0
LB1234Author Commented:
Ok Gary, I added the code and changed the $row variable, but instead of getting the user's valued stored in the database, I just get the last value from the type table on every line.  So it's looping through to the end of the type table and just giving me that value.  Not sure what's wrong.

<body>

<table>
  <tr>
        <th scope="col">Date</th> 
        <th scope="col">Amount</a></th>
        <th scope="col">Vendor</th>
        <th scope="col">Description</th>
        <th scope="col">Type</th>
        <th scope="col">Notes</th>
        <th scope="col">Bind Values?</th>
        <th scope="col">Trip</th>
        <th scope="col">Last Updated</th>
        <th scope="col">Updated By</th>
        <th scope="col">Done</th>
  </tr>



<form action="process_table.php" method="post">


  
<?php

$transactions_query = "SELECT transactions_id, `date`, amount, vendor, description, `TYPE` , trip, last_updated, last_updated_by, done, notes\n"
    . "FROM users, transactions\n"
    . "WHERE users.user_name = '{$_SESSION["user"]}' AND users.users_id = transactions.users_id";
						

$type_query = "SELECT * FROM type order by type_name ASC";
$type_result = mysqli_query($connection, $type_query);

$type_options = NULL;




$transactions_result = mysqli_query($connection, $transactions_query);

while ($row = mysqli_fetch_assoc($transactions_result)) { 
$id = $row["transactions_id"];
?>



 <tr>
 <td><?php echo date("F d, Y", strtotime($row["date"]));?></td>
 <td><?php echo number_format($row["amount"], 2);?></td>
 <td><?php echo $row["vendor"];?></td>
 <td><?php echo $row["description"];?></td>
 
<?php 
 while ($row2 = mysqli_fetch_assoc($type_result)) {
$row["done"] == "checked" ? $selected= " selected" : $selected="";
	$type_options .= sprintf("<option value='%s' ".$selected.">%s</option>".PHP_EOL, $row2["type_name"], $row2["type_name"]);
}
?>
 
 <td><?php echo "<select name='expense[" . $id . "][type]'>" . $type_options . "</select>"?></td>
 
 
 <td><?php echo "<textarea name='expense[" . $id . "][notes]'>" . $row["notes"] . "</textarea>";?></td>
 
 <td><input name="bind" type="checkbox" value="YES"></td>
 
 <td nowrap="nowrap">Trip place holder</td>
 <td><?php echo date("m/d/Y")?></td>
     <td>
     <?php 
	 	if (!isset($_SESSION["user"])) {
			echo "temp"	;
		} else {
		  echo 	$_SESSION["user"];
		}
     
     ?>
</td>

  <td><?php echo "<input name='expense[" . $id . "][done]' type='checkbox'"?> <?php  if($row["done"] == "checked") {
	 echo "checked = 'checked'>";
 } else {
		echo ">"; 
 }
 
 ?>
 
  </td>
  
 </tr>
 
 
<?php
}
?>




	
</table>

<input name="submit" type="submit" id="submit" >
</form>

</body>

Open in new window

0
LB1234Author Commented:
Ray, the numbering of the code in my IDE is different than the code here.  55 = 68.

I fixed that error, but now in every row of the table i have the last value for the type result, not the selected value.
0
Ray PaseurCommented:
This article might be helpful to you at this point in the learning process.  In particular, you might want to make a Google search for "coding standards" and choose one.  Then adhere to it actively and rigidly until it becomes a permanent habit.  I don't care so much what standard you choose, so long as you choose one.  Neatness counts for a lot more than you may think in programming because it allows you to spot errors much faster than you can when the code is all jumbled together without alignment and without well constructed variable names.

I also think if you switch over to the object-oriented version of MySQLi you will find the syntax easier, and it will be easier to integrate the programming with OOP notation.  There is a lot less fiddly punctuation in OOP notation.  And you don't have to type the name of the connection into every call to the query functions.  

And please, please don't name a column "date" or "type" -- that will cause a debugging cycle for you or someone else who tries to use your code.  Choose a meaningful name that cannot be confused with a MySQL reserved word or function.

Here's a necessary test for query success, and a debugging technique that will help you know whether the query worked the way you wanted.  It can help you decide whether to change the query, or to look in the PHP logic.  This pertains to lines 26-39 of the code snippet at ID: 39520892.  You can use the same var_dump() function to print out the rows from the query results set.  This will let you see if the code/query are working correctly.  Usually a professional programmer would debug this thoroughly before trying to integrate the query results set into an HTML document.

$transactions_query 
= 
"
SELECT 
  transactions_id
, `date`
, amount
, vendor
, description
, `TYPE` 
, trip
, last_updated
, last_updated_by
, done
, notes
FROM 
  users
, transactions
WHERE users.user_name = '{$_SESSION["user"]}' 
AND users.users_id = transactions.users_id
"
;
$transactions_result = mysqli_query($connection, $transactions_query);

$type_query 
= 
"
SELECT 
  * 
FROM 
  type 
ORDER BY type_name ASC
"
;
$type_result = mysqli_query($connection, $type_query);

// SHOW WHAT HAPPENED WITH THE QUERIES
var_dump($transactions_result, $type_result);

Open in new window

0
GaryCommented:
What is the name of the db field that stores the dropdown value - are you storing the actual value
0
LB1234Author Commented:
I figured out that it should be something like this, but I am having a heck of a time getting the quotes right around {$row["type"]} in the bolded section below.

<?php 
 while ($row2 = mysqli_fetch_assoc($type_result)) {
[b]$row["done"] == "checked" ? $selected= " selected = "{$row["type"]}"" : $selected="";[/b]
	
	$type_options .= sprintf("<option value='%s' ".$selected.">%s</option>".PHP_EOL, $row2["type_name"], $row2["type_name"]);
}
?>

Open in new window

0
Ray PaseurCommented:
The "bold" tag inserts a parse error into the code.  It shouldn't be used in the EE code snippet.  For that matter, EE shouldn't allow it to be used in the code snippet!

You won't have any trouble with the quotes if you choose the object-oriented notation to retrieve the rows from the results set.  Instead of $row["type"] you would write $row->type and almost all of the fiddly punctuation is gone!

You can choose the fetch_object() variant to retrieve the rows of data.  Then you can use the OOP notation.
0
LB1234Author Commented:
Ray, After this project is over, the first I'm doing is learning OOP.  I have a feeling this code would've been much easier with OOP.  Kind of intimidating though.  I was studying OOP yesterday and ran into references.  Oy vey... a little confusing.
0
Ray PaseurCommented:
You don't need to jump into OOP all at once.  You can just use it for retrieving the rows of the query results set.  It saves a ton of trouble.  Here is all you need to do:

1. Change from mysqli_fetch_assoc() to mysqli_fetch_object()
2. find the data in the rows with something like $row->type instead of $row["type"]

The only place you might run into trouble is if you tried to put the entire row object into the PHP session.  If you decide you need to do that, post a question here at EE about it -- it's a longer discussion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.