drop down menu from sql db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
elliot_c
Forum Newbie
Posts: 6
Joined: Sun Mar 22, 2009 7:34 pm

drop down menu from sql db

Post 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
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: drop down menu from sql db

Post 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.
elliot_c
Forum Newbie
Posts: 6
Joined: Sun Mar 22, 2009 7:34 pm

Re: drop down menu from sql db

Post 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
 
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Re: drop down menu from sql db

Post 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>";
elliot_c
Forum Newbie
Posts: 6
Joined: Sun Mar 22, 2009 7:34 pm

Re: drop down menu from sql db

Post by elliot_c »

Specialk, Thanks,
both options get rid of the error, but either way the menu is blank.
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Re: drop down menu from sql db

Post 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.

Code: Select all

$dept_name = $row['dept_name'];
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.
elliot_c
Forum Newbie
Posts: 6
Joined: Sun Mar 22, 2009 7:34 pm

Re: drop down menu from sql db

Post 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?
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Re: drop down menu from sql db

Post 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.
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: drop down menu from sql db

Post 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.

Code: Select all

$dept_name = $row['dept_name'];
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.
elliot_c
Forum Newbie
Posts: 6
Joined: Sun Mar 22, 2009 7:34 pm

Re: drop down menu from sql db

Post 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.
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: drop down menu from sql db

Post by mattpointblank »

Looks like your code is overwriting itself each time, not appending itself to the existing code.
elliot_c
Forum Newbie
Posts: 6
Joined: Sun Mar 22, 2009 7:34 pm

Re: drop down menu from sql db

Post 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? :banghead: at least I don't feel so bad that I can't figure it out either...being new to php and all, :P
Post Reply