Jump to content
Larry Ullman's Book Forums

Pull Down Menu Populated By Database


Recommended Posts

I have a database table with 32 records in it. I have written PHP code to cycle thru the records, and add the name field from each record into a pull down menu box. So far, I have tried 'select name from users order by name asc' and just 'select name from users', and each time, only 31 of the 32 names are displayed. Has anyone had this happen?

 

 

$query = "SELECT name FROM users ORDER BY name ASC";

 

// run the query

 

if ($r = mysql_query($query, $dbc)) {

 

// retrieve every record and add to a dropdown box

while ($row = mysql_fetch_array($r)) {

print "<option value=\" {$row['name']} \"> {$row['name']} </option>";

}

 

// query did not run

 

} else {

print '<p style="color: red;">Could not retrieve the data because:<br />' . mysql_error($dbc)

. '.</p><p>The query being run was: ' . $query . '</p>';

} // end of query if

 

print "</select></td>";

Link to comment
Share on other sites

That could be it, because I do have two of '$row = mysql_fetch_array($r)' in my code. I am trying to create a form that has a table with a pull down menu of these 32 users, then a pull down menu with values in one row, then the same two pull down menus in the row below before i close the table. So, I am using the statement above two times to get the data twice. Would using $row2 and $q to get the data the second time solve the problem, or is there a more efficient way to get the data once to populate 2 different drop downs?

Link to comment
Share on other sites

Everytime you call mysql_fetch_array, it returns the current row and moves its internal pointer to the next row. So the second time you use it, it will be starting at the second row.

 

For your requirements, you could either repeat the d/b query or store the query results in an array which you can access as you need to e.g.

$names = array(); // create a variable to hold the query results
$query = "SELECT name FROM users ORDER BY name ASC";
$r = mysql_query($query, $dbc);
while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
$names[] = $row; // add the row in to the results array
}

Then you can loop through $names for your various dropdown menu options.

  • Upvote 1
Link to comment
Share on other sites

My code:

 

 

$names = array(); // create a variable to hold the query results

 

$query = "SELECT name FROM users ORDER BY name ASC";

 

// run the query

 

if ($r = mysql_query($query, $dbc)) {

 

// retrieve every record and add to a dropdown box

while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {

$names[] = $row; // add the row in to the results array

}

 

// query did not run

 

} else {

print '<p style="color: red;">Could not retrieve the data because:<br />' . mysql_error($dbc)

. '.</p><p>The query being run was: ' . $query . '</p>';

} // end of query if

 

mysql_close($dbc); // close the connection

 

// set up combo box

 

print '<tr><td>Name:</td><td><select name="name"';

 

foreach ($names as $num => $tname) {

print "<option value=\"$num\">$tname</option>";

}

 

print "</select></td>";

 

I get a dropdown box filled with the word 'Array' several times.

Link to comment
Share on other sites

That's because with the way you've written your code, $names is an array of arrays (in other words, a multidimensional, or in this case, 2D array).

 

The following code is grabbing multiple rows from the DB, which are each arrays in and of themselves:

 

while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
 $names[] = $row; // add the row in to the results array
}

 

For example, if the first set of row values returned from the DB were ['Doug', 22, 'Chocolate chip'] and the second set of row values returned were ['Jenny', 19, 'Strawberry'], then your $names 2D array would have the following structure:

 

$names[0][0] = 'Doug';
$names[0][1] = 22;
$names[0][2] = 'Chocolate chip';
$names[1][0] = 'Jenny';
$names[1][1] = 19;
$names[1][2] = 'Strawberry';

 

In that case, to actually output the names, which are the first values in each of the respective rows returned from the DB, you'd have to rewrite the line outputting the value to the HTML as follows:

 

print "<option value=\"$num\">$tname[0]</option>";

 

Please note that this applies even when you only return one field/column from the DB (which seems to be the case with your SQL query), as $row is still an array (with only one element in it).

  • Upvote 2
Link to comment
Share on other sites

 Share

×
×
  • Create New...