• (2 Pages)
  • +
  • 1
  • 2

Populating a dropdown/Combo box from MySQL. How to get your database information into a dropdown box. Rate Topic: ***** 2 Votes

#1 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Posted 21 March 2009 - 02:07 PM

Chances are, if you're reading this now, you've been wracking your brain over how to get that pesky combo box to accept your database information. Today, I have a solution for you. I'm going to demonstrate how you can make this happen.

We'll start by assuming we have a table called 'users'. We're using MySQL, and we want a dropdown box containing all of our users from our users table, by name. So, we'll go step by step. I'll assume for the purposes of this tutorial that, prior to this code, the database connection has already been established. The first thing we'll want to do is to run our query to get the information into a result resource. Since we want to display their user names in the dropdown box, we can speed up the operation and use up less memory by pulling only the field that we need.

// Write out our query.
$query = "SELECT username FROM users";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());


Now $result contains the result resource we'll be using to populate our dropdown box.

Fast forward to where we'll actually be doing the HTML for the dropdown box. As you're probably aware, the dropdown box is a select element, and each choice is an option sub-element inside of the select. We can use string concatenation to build our dropdown box, and use the echo construct to output it afterward. We'll name our dropdown box 'users', in keeping with its contents.

$dropdown = "<select name='users'>";
while($row = mysql_fetch_assoc($result)) {
  $dropdown .= "\r\n<option value='{$row['username']}'>{$row['username']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;


You'll notice that our dropdown box is output containing option elements for all of our users. To control that, make the dropdown box part of a form, and do a select statement based on the value of 'usernames', after it has been cleaned and validated (gotta watch out for spoofed pages), and you can make this a select box that control what information gets passed to the form for modifying information on users in your database.

I hope you find this helpful.

Is This A Good Question/Topic? 2
  • +

Replies To: Populating a dropdown/Combo box from MySQL.

#2 wrighty  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 45
  • Joined: 09-December 08

Posted 15 May 2009 - 03:31 AM

Any way of getting the value out of this and storing as a variable?
Was This Post Helpful? 0
  • +
  • -

#3 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Posted 20 June 2009 - 09:36 PM

Yes. After the form containing the combo box is submitted, simply use $_GET or $_POST, depending on your preference, just as you would for regular form elements.
Was This Post Helpful? 0
  • +
  • -

#4 N1tr0  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-May 09

Posted 13 April 2010 - 05:00 PM

I am doing something similar but I would like to take it a step further and have been unable to find a good solution, surprisingly.
I can get my dropdown to populate and even update my table but I would like the dropdown to select the current value in the database for that row.
How can I do that?

Thanks,
- N1tr0

This post has been edited by N1tr0: 14 April 2010 - 10:12 AM

Was This Post Helpful? 0
  • +
  • -

#5 N1tr0  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-May 09

Posted 14 April 2010 - 10:11 AM

OK, I have it to where the dropdown selects the current value in the database but when I choose a new value and update the record, that field is set to '0' instead of the value I selected. So it looks like an either / or right now. :-/

Any ideas on how to get it to grab the new value?

This post has been edited by N1tr0: 14 April 2010 - 10:12 AM

Was This Post Helpful? 0
  • +
  • -

#6 N1tr0  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-May 09

Posted 14 April 2010 - 10:26 AM

OK, I figured it out. Man that feels good. :-)

I now have a form with a dropdown menu that is populated via a lookup table and defaults to the value currently in the master table for that row and field.
I can select a new value from the lookup table (via the dropdown) and have the new value updated on the master table.

Ping me if you need a similar solution. I'd be happy to share it.
Was This Post Helpful? 0
  • +
  • -

#7 Scotty G  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 59
  • Joined: 09-April 10

Posted 14 April 2010 - 10:26 AM

View PostN1tr0, on 14 April 2010 - 09:11 AM, said:

OK, I have it to where the dropdown selects the current value in the database but when I choose a new value and update the record, that field is set to '0' instead of the value I selected. So it looks like an either / or right now. :-/

Any ideas on how to get it to grab the new value?


Sorry, I'm having troubles trying to follow here. I've done something similar my self. Are you having troubles getting the new selected value into your database?
Was This Post Helpful? 0
  • +
  • -

#8 deathmeat  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 72
  • Joined: 25-August 10

Posted 27 February 2011 - 05:32 AM

View PostValek, on 21 March 2009 - 02:07 PM, said:

Chances are, if you're reading this now, you've been wracking your brain over how to get that pesky combo box to accept your database information. Today, I have a solution for you. I'm going to demonstrate how you can make this happen.

We'll start by assuming we have a table called 'users'. We're using MySQL, and we want a dropdown box containing all of our users from our users table, by name. So, we'll go step by step. I'll assume for the purposes of this tutorial that, prior to this code, the database connection has already been established. The first thing we'll want to do is to run our query to get the information into a result resource. Since we want to display their user names in the dropdown box, we can speed up the operation and use up less memory by pulling only the field that we need.

// Write out our query.
$query = "SELECT username FROM users";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());


Now $result contains the result resource we'll be using to populate our dropdown box.

Fast forward to where we'll actually be doing the HTML for the dropdown box. As you're probably aware, the dropdown box is a select element, and each choice is an option sub-element inside of the select. We can use string concatenation to build our dropdown box, and use the echo construct to output it afterward. We'll name our dropdown box 'users', in keeping with its contents.

$dropdown = "<select name='users'>";
while($row = mysql_fetch_assoc($result)) {
  $dropdown .= "\r\n<option value='{$row['username']}'>{$row['username']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;


You'll notice that our dropdown box is output containing option elements for all of our users. To control that, make the dropdown box part of a form, and do a select statement based on the value of 'usernames', after it has been cleaned and validated (gotta watch out for spoofed pages), and you can make this a select box that control what information gets passed to the form for modifying information on users in your database.

I hope you find this helpful.

nothing is being displayed in the combo box:S
Was This Post Helpful? 0
  • +
  • -

#9 okayRICE  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 25-July 11

Posted 25 July 2011 - 08:41 AM

View PostValek, on 20 June 2009 - 09:36 PM, said:

Yes. After the form containing the combo box is submitted, simply use $_GET or $_POST, depending on your preference, just as you would for regular form elements.


I know this is pretty late to the party, but I'm having some trouble returning a variable from the drop table.
echo "<form action='edit_data.php?page=3' method='post'>";
						$dropdown = "<select name='issue_table'>";
						while($row = mysql_fetch_assoc($result)) {
						$dropdown .= "\r\n<option value='{$row['pcb_id']}'>{$row['pcb_id']}</option>";
						}
						$dropdown .= "\r\n</select>";
						echo $dropdown; 
						echo "<input type='submit' name='filter' value='Filter' />";
						echo "</form>";


Could you give me an example of what I need to change to return the selected value with $POST?
Was This Post Helpful? 0
  • +
  • -

#10 Kaybin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-August 08

Posted 23 November 2011 - 07:58 AM

What if you would like the Option Captions to output related information from another table? For example. The value would be an indexed primary key (int). Say the value corresponds to a reference table. For example the values would be 1,2,3,4 but I would like to take that 1-4 and associate via a different table which would pull (Color and Code) Red, Blue, Yellow, Green and 101,102,103,104. The option box should read off ass Red - 101 or Blue - 202.

My problem is my code seems to be breaking my combobox. The snippet of code works fine outside of the combobox so I know it is simply the placement.
Was This Post Helpful? 0
  • +
  • -

#11 Terry.Harris  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-January 10

Posted 28 June 2012 - 03:37 PM

View PostN1tr0, on 14 April 2010 - 10:26 AM, said:

OK, I figured it out. Man that feels good. :-)

I now have a form with a dropdown menu that is populated via a lookup table and defaults to the value currently in the master table for that row and field.
I can select a new value from the lookup table (via the dropdown) and have the new value updated on the master table.

Ping me if you need a similar solution. I'd be happy to share it.

.....................

Hello N1tr0,
Please post your dropdown menu code.
Thanks!
Terry
Was This Post Helpful? 0
  • +
  • -

#12 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Posted 29 June 2012 - 10:54 PM

I did a lot of searching on the net for this task and I must admit, this is the easiest and cleanest tutorial I came across. Thank you. :)
Was This Post Helpful? 0
  • +
  • -

#13 waji134  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 12-July 12

Posted 12 July 2012 - 11:53 PM

I n have a form with a dropdown menu that is populated via a lookup table and defaults to the value currently in the master table for that row and field.
I can select a new value from the lookup table (via the dropdown) and have the new value updated on the master table.

.................

Hello N1tr0,
Please post your dropdown menu code ASAP.
Thanks!
waji134
[/quote]
Was This Post Helpful? 0
  • +
  • -

#14 fivbag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-July 12

Posted 21 July 2012 - 05:14 PM

View PostN1tr0, on 14 April 2010 - 10:26 AM, said:

OK, I figured it out. Man that feels good. :-)

I now have a form with a dropdown menu that is populated via a lookup table and defaults to the value currently in the master table for that row and field.
I can select a new value from the lookup table (via the dropdown) and have the new value updated on the master table.

Ping me if you need a similar solution. I'd be happy to share it.


I have an issue, which may have been the same as yours. How did you achieve this??
Was This Post Helpful? 0
  • +
  • -

#15 mweston620  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 04-August 12

Posted 04 August 2012 - 12:05 PM

Thank you so much for this! Simple and to the point. However, I was wondering if there was a simpler way to pull dynamic material into the result divs. I have different categories and I want all product under the selected category to display. What I have right now is:

    <?php
    $dropdown = "<select name='subcategory' id='CatSelect'>";
	while($row = mysql_fetch_assoc($result)) {
		$dropdown .= "\r\n<option value='{$row['subcategory']}'>{$row['subcategory']}</option>";
	}
	$dropdown .= "\r\n</select>";
	echo $dropdown;
	?>
  	<br />
    <br />
    <br />
    <br />
  	<div id="Hats" class="categories"><?php include_once ('hat_category.php'); ?></div>
    <div id="Shirts" class="categories"><?php include_once ('shirt_category.php'); ?></div>
    <div id="MP3s" class="categories"><?php include_once ('mp3_category.php'); ?></div>
    <div id="DVDs" class="categories"><?php include_once ('dvd_category.php'); ?></div>
    <div id="CDs" class="categories"><?php include_once ('cd_category.php'); ?></div>
    <div id="Books" class="categories"><?php include_once ('book_category.php'); ?></div>
    <div id="Empowerment" class="categories"><?php include_once ('empowerment_category.php'); ?></div>
    <div id="WOT" class="categories"><?php include_once ('wot_category.php'); ?></div>


The result files look like this:

<?php 
// Connect to the MySQL database  
include "storescripts/connect_to_mysql.php"; 
$sql = mysql_query("SELECT * FROM products WHERE subcategory='MP3s' ORDER BY product_name");
$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0) {
	while($row = mysql_fetch_array($sql)){ 
             $id = $row["id"];
			 $product_name = $row["product_name"];
			 $author = $row["author"];
			 $price = $row["price"];
			 $details = $row["details"];
			 $category = $row["category"];
			 $subcategory = $row["subcategory"];
			 $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
	echo '<div style="float:left; width:316px;"><table width="100%" border="0" cellspacing="0" cellpadding="6">
        <tr>
          <td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="77" height="77" border="1" /></a></td>
          <td width="83%" valign="top"><b>' . $product_name . '</b><br /><span style="font-size:12px;">' . $author . '<br />
            $' . $price . '<br />
            <a href="product.php?id=' . $id . '">View Product Details</a></span></td>
        </tr>
      </table></div>';
    }
}
mysql_close();
?>


View PostValek, on 21 March 2009 - 02:07 PM, said:

Chances are, if you're reading this now, you've been wracking your brain over how to get that pesky combo box to accept your database information. Today, I have a solution for you. I'm going to demonstrate how you can make this happen.

We'll start by assuming we have a table called 'users'. We're using MySQL, and we want a dropdown box containing all of our users from our users table, by name. So, we'll go step by step. I'll assume for the purposes of this tutorial that, prior to this code, the database connection has already been established. The first thing we'll want to do is to run our query to get the information into a result resource. Since we want to display their user names in the dropdown box, we can speed up the operation and use up less memory by pulling only the field that we need.

// Write out our query.
$query = "SELECT username FROM users";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());


Now $result contains the result resource we'll be using to populate our dropdown box.

Fast forward to where we'll actually be doing the HTML for the dropdown box. As you're probably aware, the dropdown box is a select element, and each choice is an option sub-element inside of the select. We can use string concatenation to build our dropdown box, and use the echo construct to output it afterward. We'll name our dropdown box 'users', in keeping with its contents.

$dropdown = "<select name='users'>";
while($row = mysql_fetch_assoc($result)) {
  $dropdown .= "\r\n<option value='{$row['username']}'>{$row['username']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;


You'll notice that our dropdown box is output containing option elements for all of our users. To control that, make the dropdown box part of a form, and do a select statement based on the value of 'usernames', after it has been cleaned and validated (gotta watch out for spoofed pages), and you can make this a select box that control what information gets passed to the form for modifying information on users in your database.

I hope you find this helpful.

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2