LB1234
asked on
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 the code with comments:
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>
Ray Paseur
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.
ASKER
Thanks Ray, but as users may wish to come back to the form, days or weeks later, I cannot use a session -oriented approach.
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.
Just add another column to their profile to store the state of the select's
Or set a cookie to store the state.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
<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>ASKER
Nevermind, was using the same variable name $row. Duh. Sorry.
I don't see how you could have gotten to line 68. The script has a parse error on line 55.
ASKER
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>ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"]);
}
?>
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.
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.
ASKER
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.
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.
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.