Page 1 of 1
drop down menu from sql db
Posted: Sun Mar 22, 2009 7:54 pm
by elliot_c
sorry to ask such a dumb question, but man, i've spent over a day googling, looking through forums, etc, and it seems because everyone's situation is different, it's hard to translate the solutions to my own problem. I just need a simple drop down menu, populated by an sql db. I'm making a simple support ticket system for my site...i figured it's a good way to learn php.
my html code:
Code: Select all
<td>
<select name="DEPARTMENT" id="DEPARTMENT" value="{DEPARTMENT)">
<option>{DEPARTMENT}</option>
</select></td>
my current php which just gives me the id#1 value but no others.
Code: Select all
$query= "SELECT dept_name FROM ticket_dept";
$result =mysql_query($query);
$system->check_mysql($result, $query, __LINE__, __FILE__);
$DEPARTMENT = mysql_fetch_array($result);
$template->assign_vars(array(
'DEPARTMENT' => $DEPARTMENT['dept_name'],
));
db has table: "ticket_dept" with fields: "dept_id" and "dept_name"
if you could make a suggestion, thanks a million
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 5:16 am
by mattpointblank
<select name="DEPARTMENT" id="DEPARTMENT" value="{DEPARTMENT)">
Code: Select all
$query= "SELECT dept_name FROM ticket_dept";
$result =mysql_query($query);
while($row = mysql_fetch_array($result)) {
list($dept_name) =$row;
echo "<option value="">$dept_name</option>";
}
</select>
It would probably be worth using $dept_id (if you have a field for that) as the VALUE for the option.
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 2:38 pm
by elliot_c
Hey Matt, thanks 4 reply,
I'm getting
Parse error: syntax error, unexpected '"', expecting ',' or ';' in /home/mysite/public_html/ticket_new.php on line 43
i did it like this. I do have to break up the php tags for the 'select' portions, correct?
Code: Select all
?>
<select name="DEPARTMENT" id="DEPARTMENT" value="{DEPARTMENT)">
<?php
$query= "SELECT dept_name FROM ticket_dept";
$result =mysql_query($query);
while($row = mysql_fetch_array($result)) {
list($dept_name) =$row;
echo "<option value="">$dept_name</option>";
}
?>
</select>
<?php
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 2:51 pm
by SpecialK
Check out this line
Code: Select all
echo "<option value="">$dept_name</option>";
The first open quote will be closed after value. You would either need to escape it or use single quotes
Code: Select all
echo "<option value=\"\">$dept_name</option>";
or
Code: Select all
echo "<option value=''>$dept_name</option>";
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 3:08 pm
by elliot_c
Specialk, Thanks,
both options get rid of the error, but either way the menu is blank.
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 3:21 pm
by SpecialK
It could be that this line is incorrect
list($dept_name) =$row;
I've never done it that way personally, so am unsure if it works.
Check your logs because if it isn't working, errors would be thrown.
If it still isn't working, take a look at the row count with mysql_num_rows($result) because it may be returning no rows.
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 3:42 pm
by elliot_c
hey that worked!! Thanks SK. but one slight little problem...I have 2 menus now, the menu that works is way up at the top left of the page, and the one (I believe from the html page/form) is in the correct place but blank. any ideas?
also matt had mentioned that he thought I should keep a value ID also...from my perspective, this is just to let the support team know what department the problem has been sent to + the site admin could change the names of the departments where the id wouldn't match up anyway (if looking up a historical ticket). but I'm wondering if matt sees something down the road that I'm not seeing yet?
Re: drop down menu from sql db
Posted: Mon Mar 23, 2009 4:15 pm
by SpecialK
The code from matt will generate the populated list. If you have another <select> elsewhere, that would be creating another drop downlist.
Code: Select all
<select name="DEPARTMENT" id="DEPARTMENT" value="{DEPARTMENT)">
<?php
$query= "SELECT dept_name,dept_id FROM ticket_dept";
$result =mysql_query($query);
while($row = mysql_fetch_array($result)) {
$dept_name = $row['dept_name'];
$dept_id = $row['dept_id'];
echo "<option value='$dept_id'>$dept_name</option>";
}
?>
The reason for IDs is because each department should have a key in the database. Using that key is more effecient that a long string name. A relational DB would be able to look up the ID. If the names of the department changes, the ID should not change. If it's important to know the "old department name" then a new department name and ID should be created and used.
Re: drop down menu from sql db
Posted: Tue Mar 24, 2009 4:50 am
by mattpointblank
SpecialK wrote:It could be that this line is incorrect
list($dept_name) =$row;
I've never done it that way personally, so am unsure if it works.
Check your logs because if it isn't working, errors would be thrown.
If it still isn't working, take a look at the row count with mysql_num_rows($result) because it may be returning no rows.
Cheers, should have spotted escaping those quote marks.
With regards to the list() function, I always use it because for larger queries, it's much faster than writing $dept_name = $row['dept_name']; for every variable. It should have worked in this code, that's how I always do it.
Re: drop down menu from sql db
Posted: Thu Mar 26, 2009 11:36 pm
by elliot_c
sorry I've been away...kind of on the brick side of a nickel.
The code from matt will generate the populated list. If you have another <select> elsewhere, that would be creating another drop downlist.
I've been playing around with this for a while cuz I didn't want to keep bugging... The <select> that I need to use is on a template page (with form), separate from the php code. I've been trying something like this:
Code: Select all
$query= "SELECT dept_name,dept_id FROM ticket_dept";
$result =mysql_query($query);
while($row = mysql_fetch_array($result)) {
$dept_name = $row['dept_name'];
$dept_id = $row['dept_id'];
$template->assign_vars(array(
'DEPARTMENT' => "<option value='$dept_id'>$dept_name</option>"
)); }
the problem is, say ticket_dept has the following id/dept entries: 1. PC Support 2.Website Support 3.General Support.....currently, "General Support" is the only selectable option from the menu.
Re: drop down menu from sql db
Posted: Fri Mar 27, 2009 4:09 am
by mattpointblank
Looks like your code is overwriting itself each time, not appending itself to the existing code.
Re: drop down menu from sql db
Posted: Fri Mar 27, 2009 2:43 pm
by elliot_c
post 1
my current php which just gives me the id#1 value but no others.
post 10
"General Support" is the only selectable option from the menu.
post 11
Looks like your code is overwriting itself each time, not appending itself to the existing code.
was this what it was doing a week ago?

at least I don't feel so bad that I can't figure it out either...being new to php and all,
