Page 1 of 2
Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 1:08 pm
by diseman
Hi, beginner here building a fictional website to learn php.
Struggling with drop-down menus when they are comparing to two tables. I know the logic, but can't seem to put it to code. At this point, I'm just slapping stuff in to see if it works 'cause I'm just not seeing it.
I have a main admin page that simply displays rows of data/info for a user.
One of the columns for each row is the drop-down menu that would allow me to select a name from the CONTRACTORS table and save it to the user's record. That's the drop-down I can't get to show the currently saved contractor.
The table with the 2 seeded names to select from is CONTRACTORS and the column I want is: first_name. I'm able to pull the names and have them show in the drop-down menu. What I can't see to get working is when the USER has a contractor saved to their record, why the SELECTED doesn't work on the drop down menu.
I have two user records seeded. User 1 has a CONTRACTOR seeded by the name of JIMMY. User 2 has a CONTRACTOR seeded with name TAMMY.
WHAT I WANT TO HAPPEN: If user has a name saved in the contractor column in USERS tbl, it should show that name as selected in the drop-down menu. Otherwise, we should see the names from CONTRACTORS to select a name from
Can someone SHOW me how it should look to work properly?
GO TO 10th ROW FROM THE BOTTOM to see the FOREACH I'm struggling with.
Code: Select all
$query = "SELECT users.username, users.status, users.first_name, users.last_name, users.contractor, users.packet_sent, users.last_login,
users.last_note, users.affiliate, users.start_date, contractors.first_name AS cfirst_name
FROM users
INNER JOIN contractors
ON users.user_id = contractors.user_id
WHERE users.usertype = '6' && users.active_state = '1' ";
}
// need two results; one for each 'while' statement below
$result1 = mysqli_query($con, $query) or die (mysqli_error($con));
$result2 = mysqli_query($con, $query) or die (mysqli_error($con));
// $array = array(); // save this in-case needed, but seems to be working without it. Thought it was needed 'cause array was being used outside of the while statement.
// create the while loop that will check for all contractors that exist and put them in an array
while($row1 = mysqli_fetch_array($result1)) {
$array[] = $row1['cfirst_name'];
}
// create a while loop that will grab everything on admin main and display it
while ($row = mysqli_fetch_array($result2)) {
// convert unix time back to regular date format
$date_conversion = gmdate("m-d-Y", $row['start_date']);
echo
"<td>"; print number_format(($unix_time - $row['last_login']) / 86400.); echo " days ago</td>".
"<td style=\"text-decoration: none;\"><a href=\"mailto:".$row['username']. "\">".$row['last_name'].", ".$row['first_name']."</a></td>";
if ($row['status'] == 'complete') {
echo "<td><font color=\"#FF0000\">".$row['status']."</font></td>";
} else {
echo "<td>".$row['status']."</td>";
}
echo
"<td>".
"<form id=\"assign_contractor\" method=\"post\">".
"<select name=\"assign_contractor\" class=\"dropmenu100\" onChange=\"selectChange2()\" \">".
"<option value=\"\" selected='selected'>Select</option>";
foreach ($array as $val) {
echo "<option value=\"".$val."\">".$val."</option>";
}
echo
"</select>".
"</form>".
"</td>".
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 2:52 pm
by Celauran
What's the DB structure like for this? Does users.contractor point to contractors.id? Or is it literally a name? Because you could run into a problem real quickly if it's the latter. That said, as you're iterating over the list of contractors, compare the current item with what's in the user record and append 'selected="selected"' if you find a match.
(Also, you're executing the same query twice when there's really no need.)
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 3:06 pm
by diseman
Hi Celauran,
It's pointing to an actual name.
Yes, I know/understand the logic you're suggesting, but I can't seem to make it happen. Would be obliged if you could show me, so I can learn from it. I think the problem I'm having is this time I'm dealing with an array as where last time you helped me the drop-down was just looking at a singular string.
You bring up another question I had. Should EVERY user always be in the users tbl, but say a .... contractor should be in the users tbl and contractor's tbl. Currently, each of my tbls has an ID, usertype, username, but I don't have contractors in users for example. I've been leaning towards believing that is the wrong method, but am unsure as I can't seem to find anyone posting these types of conventions on google.
Thanks again for your help!
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 3:34 pm
by Celauran
I think having separate tables for users and contractors is fine. What I'd recommend, however, is using IDs for your foreign key relations. Say your contractor Tammy updated her profile to have her name display as Tamara instead. Now all your relations are broken. If they simply pointed to a row in the contractors table instead, all would be fine. That, however, is a separate issue from the problem at hand.
Can't promise there are no typos, but this should illustrate the general idea.
Code: Select all
$query = "SELECT users.username, users.status, users.first_name, users.last_name, users.contractor, users.packet_sent, users.last_login,
users.last_note, users.affiliate, users.start_date, contractors.first_name AS cfirst_name
FROM users
INNER JOIN contractors ON users.user_id = contractors.user_id
WHERE users.usertype = '6' AND users.active_state = '1' ";
} // Not sure why this is here
$result = mysqli_query($con, $query) or die (mysqli_error($con));
// create the while loop that will check for all contractors that exist and put them in an array
$contractors = array();
foreach ($result as $row) {
$contractors[] = $row['cfirst_name'];
}
// create a while loop that will grab everything on admin main and display it
foreach ($result as $row) {
// convert unix time back to regular date format
$date_conversion = gmdate("m-d-Y", $row['start_date']);
echo
"<td>"; print number_format(($unix_time - $row['last_login']) / 86400.); echo " days ago</td>".
"<td style=\"text-decoration: none;\"><a href=\"mailto:".$row['username']. "\">".$row['last_name'].", ".$row['first_name']."</a></td>";
if ($row['status'] == 'complete') {
// Don't use font tags. Use CSS classes
echo "<td><font color=\"#FF0000\">".$row['status']."</font></td>";
} else {
echo "<td>".$row['status']."</td>";
}
echo
"<td>".
"<form id=\"assign_contractor\" method=\"post\">".
"<select name=\"assign_contractor\" class=\"dropmenu100\" onChange=\"selectChange2()\" \">".
"<option value=\"\" selected='selected'>Select</option>";
foreach ($contractors as $val) {
$selected = ($row['contractor'] == $val) ? 'selected="selected"' : '';
echo "<option value=\"".$val."\" {$selected}>".$val."</option>";
}
echo
"</select>".
"</form>".
"</td>".
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 8:19 pm
by diseman
Sorry for the late reply. I have been trying to figure out part two of this problem, which is to post the selected name from the drop-down menu to the USERS tbl for that user.
Your last help was great; worked on first go. I did change the selected portion to what I'm familiar with, but same same really.
Can you explain to me why my POST is not saving to the database? I'm getting some weird behavior from what I have. I one point, it was saving something, but it was wrong data. Seems like I should have been able to duplicate a little of what you showed me to make this last piece of the puzzle work.
EDIT: by the way.. I thought I could just pull the username for each user/row and then post the contractor selection to the row with the matching $username (email address). Hours of nothing... little discouraged today/tonight. I'm 0-2 today on what I thought would be two simple things to learn. Thanks again for any help you are willing to provide.
here's where I'm at:
Code: Select all
$query = "SELECT users.username, users.status, users.first_name, users.last_name, users.contractor, users.packet_sent, users.last_login,
users.last_note, users.affiliate, users.start_date, contractors.first_name AS cfirst_name
FROM users
INNER JOIN contractors ON users.user_id = contractors.user_id
WHERE users.usertype = '6' AND users.active_state = '1' ";
} // this bracket is actually for code above that you're not seeing. didn't want to confuse anything with more code than needed.
$result = mysqli_query($con, $query) or die (mysqli_error($con));
// ...
$contractors = array();
$username = array();
foreach ($result as $row) {
$contractors[] = $row['cfirst_name'];
$username[] = $row['username'];
}
// ...
foreach ($result as $row) {
// convert unix time back to regular date format
$date_conversion = gmdate("m-d-Y", $row['start_date']);
$selected = $row['contractor'];
$username = $row['username'];
echo
"<td>"; print number_format(($unix_time - $row['last_login']) / 86400.); echo " days ago</td>".
"<td style=\"text-decoration: none;\"><a href=\"mailto:".$row['username']. "\">".$row['last_name'].", ".$row['first_name']."</a></td>";
if ($row['status'] == 'complete') {
// Don't use font tags. Use CSS classes
echo "<td><font color=\"#FF0000\">".$row['status']."</font></td>";
} else {
echo "<td>".$row['status']."</td>";
}
echo
"<td>".
"<form action=\"admin_main.php\" id=\"assign_contractor\" method=\"post\">".
"<select name=\"assign_contractor\" class=\"dropmenu100\" onChange=\"selectChange2()\" \">".
"<option value=\"\" selected='selected'>Select</option>";
foreach ($contractors as $val) {
if ($val == $selected) {
echo "<option value='".$selected."' selected='selected'>".$selected."</option>";
} else {
echo "<option value=\"".$val."\">".$val."</option>";
}
// post contractor to user when option is selected in drop-down menu
if(isset($_POST['assign_contractor'])) {
$sql = "UPDATE users SET (contractor) VALUES ('".$val."') WHERE username = '".$username."' ";
mysqli_query($con, $sql) or die (mysqli_error($con));
}
}
echo
"<td>";echo $val; echo $username; echo "</td>".
"</select>".
"</form>".
"</td>".
"<td><form method=\"post\" action=\"applicant_info.php\"><input type=\"submit\" class=\"btn_acp\" value=\"Account\" /><input type=\"hidden\" name=\"admin_to_user\" value=\"".$row['username']."\"/></form></td>".
"<td><form method=\"post\" action=\"applicant_control_panel.php\"><input type=\"submit\" class=\"btn_acp\" value=\"Packet\" /><input type=\"hidden\" name=\"admin_to_user\" value=\"".$row['username']."\"/></form></td>".
"<td>".$row['affiliate']."</a></td>".
"<td>".$date_conversion."</td>".
"<td>".$row['packet_sent']."</td>".
"<td>".$row['last_note']."</td>".
"</tr>";
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 8:31 pm
by Celauran
I'm getting some weird behavior from what I have.
Can you elaborate on that?
It looks like you're creating your form inside of a loop, meaning you're going to have many forms for many different users on the same page. I don't see anything in the form that identifies the user to whom these values belong. That's one potential problem. Look at including a hidden field perhaps so you can identify which user the form submission represents.
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 8:49 pm
by diseman
see attached please...
Yes! That's the problem. No means of identifying. I was thinking maybe I've painted myself into a corner by not having a means of identifying the user.
However, I thought I could get the username for each row and that would be good enough to reference. Meaning, UPDATE contractor WHERE username = '".$username."'
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 8:56 pm
by Celauran
You're already using hidden fields in the forms below the one in question. You could use the same approach here.
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 9:06 pm
by diseman
Not sure I understand what you mean. There is nothing below the part that is in question. The two users you see on the page is the area I'm working on with the drop-down menu in the 4th column from the left. The name and email address are seeded. The name is from the contractor table and the email address is username from users table. I was only displaying them to see if my variables for even outputting anything.
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 9:08 pm
by Celauran
Again, not promising it's free of typos, but here are a few simple modifications that hopefully illustrate what I mean.
Code: Select all
<?php
// Try keeping most of your logic at the top, separate from the presentation
// post contractor to user when option is selected in drop-down menu
if (isset($_POST['assign_contractor'])) {
$contractor = mysqli_real_escape_string($con, $_POST['assign_contractor']);
$username = mysqli_real_escape_string($con, $_POST['username']);
$sql = "UPDATE users SET (contractor) VALUES ('{$contractor}') WHERE username = '{$username}' ";
mysqli_query($con, $sql) or die (mysqli_error($con));
}
// ... Other stuff
$query = "SELECT users.username, users.status, users.first_name, users.last_name, users.contractor, users.packet_sent, users.last_login,
users.last_note, users.affiliate, users.start_date, contractors.first_name AS cfirst_name
FROM users
INNER JOIN contractors ON users.user_id = contractors.user_id
WHERE users.usertype = '6' AND users.active_state = '1' ";
}
$result = mysqli_query($con, $query) or die (mysqli_error($con));
$contractors = array();
$username = array();
foreach ($result as $row) {
$contractors[] = $row['cfirst_name'];
// You're setting $username here...
$username[] = $row['username'];
}
foreach ($result as $row) {
// convert unix time back to regular date format
$date_conversion = gmdate("m-d-Y", $row['start_date']);
$selected = $row['contractor'];
// ...and then immediately overwriting it
$username = $row['username'];
echo
"<td>"; print number_format(($unix_time - $row['last_login']) / 86400.); echo " days ago</td>".
"<td style=\"text-decoration: none;\"><a href=\"mailto:".$row['username']. "\">".$row['last_name'].", ".$row['first_name']."</a></td>";
if ($row['status'] == 'complete') {
// Don't use font tags. Use CSS classes
echo "<td><font color=\"#FF0000\">".$row['status']."</font></td>";
} else {
echo "<td>".$row['status']."</td>";
}
echo
"<td>".
"<form action=\"admin_main.php\" id=\"assign_contractor\" method=\"post\">".
"<input type=\"hidden\" name=\"username\" value=\"{$username}\">"
"<select name=\"assign_contractor\" class=\"dropmenu100\" onChange=\"selectChange2()\" \">".
"<option value=\"\" selected='selected'>Select</option>";
foreach ($contractors as $val) {
if ($val == $selected) {
echo "<option value='".$selected."' selected='selected'>".$selected."</option>";
} else {
echo "<option value=\"".$val."\">".$val."</option>";
}
}
echo
"<td>";echo $val; echo $username; echo "</td>".
"</select>".
"</form>".
"</td>".
"<td><form method=\"post\" action=\"applicant_info.php\"><input type=\"submit\" class=\"btn_acp\" value=\"Account\" /><input type=\"hidden\" name=\"admin_to_user\" value=\"".$row['username']."\"/></form></td>".
"<td><form method=\"post\" action=\"applicant_control_panel.php\"><input type=\"submit\" class=\"btn_acp\" value=\"Packet\" /><input type=\"hidden\" name=\"admin_to_user\" value=\"".$row['username']."\"/></form></td>".
"<td>".$row['affiliate']."</a></td>".
"<td>".$date_conversion."</td>".
"<td>".$row['packet_sent']."</td>".
"<td>".$row['last_note']."</td>".
"</tr>";
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Tue Oct 27, 2015 9:42 pm
by diseman
your curly braces are throwing me off 'cause I haven't seen that kind of php before. I'm guessing it's OOP. I changed things like: {$username} to '".$username."' and some other curly braces you had in there.
I was also getting an error with the curly braces.
So, I got the first drop down to save, but not the second. It does nothing.
Code: Select all
if (isset($_POST['assign_contractor'])) {
$contractor = mysqli_real_escape_string($con, $_POST['assign_contractor']);
$username = mysqli_real_escape_string($con, $_POST['username']);
$sql = "UPDATE users SET contractor = '".$contractor."' WHERE username = '".$username."' ";
mysqli_query($con, $sql) or die (mysqli_error($con));
}
// ... Other stuff
$query = "SELECT users.username, users.status, users.first_name, users.last_name, users.contractor, users.packet_sent, users.last_login,
users.last_note, users.affiliate, users.start_date, contractors.first_name AS cfirst_name
FROM users
INNER JOIN contractors ON users.user_id = contractors.user_id
WHERE users.usertype = '6' AND users.active_state = '1' ";
}
$result = mysqli_query($con, $query) or die (mysqli_error($con));
$contractors = array();
$username = array();
foreach ($result as $row) {
$contractors[] = $row['cfirst_name'];
// You're setting $username here...
$username[] = $row['username'];
}
foreach ($result as $row) {
// convert unix time back to regular date format
$date_conversion = gmdate("m-d-Y", $row['start_date']);
$selected = $row['contractor'];
// ...and then immediately overwriting it
$username = $row['username'];
echo
"<td>"; print number_format(($unix_time - $row['last_login']) / 86400.); echo " days ago</td>".
"<td style=\"text-decoration: none;\"><a href=\"mailto:".$row['username']. "\">".$row['last_name'].", ".$row['first_name']."</a></td>";
if ($row['status'] == 'complete') {
// Don't use font tags. Use CSS classes
echo "<td><span style=\"color: #FF0000\">".$row['status']."</span></td>";
} else {
echo "<td>".$row['status']."</td>";
}
echo
"<td>".
"<form action=\"admin_main.php\" id=\"assign_contractor\" method=\"post\">".
"<input type=\"hidden\" name=\"username\" value=\"".$username."\">".
"<select name=\"assign_contractor\" class=\"dropmenu100\" onChange=\"selectChange2()\" \">".
"<option value=\"\" selected='selected'>Select</option>";
foreach ($contractors as $val) {
if ($val == $selected) {
echo "<option value='".$selected."' selected='selected'>".$selected."</option>";
} else {
echo "<option value=\"".$val."\">".$val."</option>";
}
}
echo
"</select>".
"</form>".
"</td>".
"<td><form method=\"post\" action=\"applicant_info.php\"><input type=\"submit\" class=\"btn_acp\" value=\"Account\" /><input type=\"hidden\" name=\"admin_to_user\" value=\"".$row['username']."\"/></form></td>".
"<td><form method=\"post\" action=\"applicant_control_panel.php\"><input type=\"submit\" class=\"btn_acp\" value=\"Packet\" /><input type=\"hidden\" name=\"admin_to_user\" value=\"".$row['username']."\"/></form></td>".
"<td>".$row['affiliate']."</a></td>".
"<td>".$date_conversion."</td>".
"<td>".$row['packet_sent']."</td>".
"<td>".$row['last_note']."</td>".
"</tr>";
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Wed Oct 28, 2015 5:58 am
by Celauran
Curly braces are simple string interpolation. You can use variables inside double-quoted strings without the need to break the string, concatenate the variable, and concatenate the rest of the string.
Code: Select all
$foo = 'bar';
echo "The variable is: " . $foo;
echo "The variable is: {$foo}"
produce the same output.
Looking at your form again, I don't see any sort of submit button. Is that being handled by your onChange handler? What does that code look like? You've got duplicated IDs, so if it's relying on ID, that could explain why one works and the other doesn't.
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Wed Oct 28, 2015 6:24 am
by diseman
yes, form is being submitted with the onChange handler. I'll take a look at that now.
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Wed Oct 28, 2015 6:29 am
by diseman
Here's the onChange handler script. The handler works on the form ID. I have three forms on the page, so I combined all functions on one page. By changing the name of each one by adding a 1,2, or 3, I got it to work. Do the different name not make each function separate?
Code: Select all
function selectChange1(){
$('#recover_applicant').submit(); // admin_main
}
function selectChange2(){
$('#assign_contractor').submit(); // admin_main
}
function selectChange3(){
$('#assign_partner').submit(); // applicant_info
}
Re: Help w/ Drop-Down Menu (Select / Option)
Posted: Wed Oct 28, 2015 6:31 am
by diseman
I just saw I had a mismatch from the form and the onChange script.
EDIT: No I didn't. Misread it. It was correct.