Displaying Information from a Database in a Drop down form?

[Andrew R]

Hello

I was wondering how I could display all the users from my database in the drop down list so when I submit the form (viewusers.php) it displays what user I selected from the drop down form in viewusers.php.

At the top of the page I have all the database information and the sql query.

[quote]<table width="925" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable-->
  <tr>
    <td width="104" height="24" valign="middle">User:</td>
    <th width="221" valign="middle" scope="col"> <form action="viewusers.php" method="post" name="user" id="user">
        <div align="left">
          <select name="formquery" class="table" id="formquery">
          </select>
          <input name="formtype" type="hidden" id="formtype" value="user">
          <input name="Submit" type="submit" class="table" value="Go!">
        </div>
      </form></th>
    <th width="592">&nbsp;</th>
  </tr>
</table>[/quote]

Help would be much appreciated.

Cheers

[onlyican]

I hope this helps

[code=php:0]
$query = "SELECT * FROM users WHERE username LIKE '%".$usersearch."'";
$result = mysql_query($query);

if(mysql_num_rows($result) == 0){

echo "I am afraid no users were found";
}else{
echo "<form method='post' action='page.php'>\n"
."<select name='users'>\n";
while($row = mysql_fetch_assoc($result)){
echo "<option>".$row["username"]."</option>\n";
}
echo "</select>\n"
."</form>\n";

}
[/code]

[Andrew R]

Cheers for the help onlyican although I was trying to do it this way (Below).  The problem I am having using the below script is that the drop down form only displays one username, not all the users in the database.  Any ideas on how to fix it?

[code]<?php
include 'db.php';

$query_users = "SELECT * FROM users";
$users = mysql_query($query_users) or die(mysql_error());
$row_users = mysql_fetch_assoc($users);
php ?>
<table width="925" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable-->
  <tr>
    <td width="104" height="24" valign="middle">Day:</td>
    <td width="438" valign="top"><form action="viewusers.php" method="post" name="user" id="user">
        <div align="left">
          <input name="formtype" type="hidden" id="formtype" value="users">
          <select name="formquery" id="formquery">
            <option value="<?php echo $row_users['username']?>"><?php echo $row_users['username']?></option>
          </select>
          <input name="Submit" type="submit" class="table" value="Go!">
        </div>
      </form></td>
    <td width="383">&nbsp;</td>
  </tr>
</table>[/code]

[onlyican]

you need to loop it in a while loop
<select name="formquery" id="formquery">
<?
while ($row = mysql_fetch_assoc($users){
echo "<option>".$row["users"]."</option>\n";
}
?>
</select>

Note: if the value of the option is the same as whats between the two options, you dont need to add a value. It will still work

[CTM]

That's because you weren't looping through all the results, simply using the first one. Now, onlyican's solution works, but you could always do something like :

[code=php:0]<?php
include 'db.php' ;  // Including the DB configuration, connection and such I presume

$query_users = "SELECT username FROM users" ;   // Instead of *, since you're only using the username field, only fetch that (for memory issues)
$users = mysql_query ( $query_users ) or die ( mysql_error ( ) ) ;

$select_options = array ( ) ;   // Instead of using a string, we'll use an array into which we'll store every result (in the <option> tag form)
while ( $row_user = mysql_fetch_assoc ( $users ) )   // Loop it up
{
  // The [] after select_options indicates to append a new value to the select_options array
  $select_options[] = "<option value = '" . $row_user['username'] . "'>" . $row_user['username'] . "</option>" ;
}

$select_options = implode ( "<br />", $select_options ) ; // I'm simply using this (and the array form) for formatting preferences (when viewing the HTML source)

// Now, you have a long string which contains every option that goes inside your select element.

?>

<table width="925" border="0" cellpadding="0" cellspacing="0">
 <!--DWLayoutTable-->
 <tr>
   <td width="104" height="24" valign="middle">Day:</td>
   <td width="438" valign="top"><form action="viewusers.php" method="post" name="user" id="user">
       <div align="left">
         <input name="formtype" type="hidden" id="formtype" value="users">
         <select name="formquery" id="formquery">
           <?php print $select_options ; ?>
         </select>
         <input name="Submit" type="submit" class="table" value="Go!">
       </div>
     </form></td>
   <td width="383">&nbsp;</td>
 </tr>
</table>[/code]

Anyway, onlyican's should work fine too, but it can't be re-used.

[Andrew R]

Cheers guys, I tried both scripts, both worked well although I also want to add another drop down box containing the location of all of the members, the problem is USA and UK are being displayed over and over again in the drop down box because they are where most our members are from, how would I stop that so the USA and the UK will only display once in the drop down?

Cheers.

[onlyican]

use another query, and add GROUP BY country

[CTM]

You wouldn't really need a second query if using my method, but then again, a second query would speed the process of treating the country info. I'm not sure in memory usage which one would be faster, but the second query is definitively simpler.

[Andrew R]

Thanks guys for your help.  To add a second select_options array would I simply add [quote]$select_options[2] = "<option value = '" . $row_user['username'] . "'>" . $row_user['username'] . "</option>" ;[/quote] below the first select array?

Cheers

[CTM]

No. And I'm not sure what you want. You'd want an exact replica of the dropdown options above? Or another one with different info?

As I explained my comments, the $select_options[] lines is simply to indicate that you're appending a new value into the $select_options array (which, in that instance, was the usual "<option value='username'>username</option>"). $select_options[2] will simply return the third iteiration of that array, which means a singleton of type "<option value='username'>username</option>".

Anyway, explain what you want.

[redarrow]

Try this please.

[code]
<?php
include 'db.php';

$query_users = "SELECT * FROM users";
$users = mysql_query($query_users) or die(mysql_error());
while($row_users = mysql_fetch_assoc($users)) {
php ?>
<table width="925" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable-->
  <tr>
    <td width="104" height="24" valign="middle">Day:</td>
    <td width="438" valign="top"><form action="viewusers.php" method="post" name="user" id="user">
        <div align="left">
          <input name="formtype" type="hidden" id="formtype" value="users">
          <select name="formquery" id="formquery">
            <option value="<?php echo $row_users['username']?>"><?php echo $row_users['username']?></option>
          </select>
          <input name="Submit" type="submit" class="table" value="Go!">
        </div>
      </form></td>
    <td width="383">&nbsp;</td>
  </tr>
</table>
<?}?>
[/code]