only show X records in dropdown

Question

hi is there a way with the code i have posted to only show a cretain amount of records in a dropdown list. i am not talking about the LIMIT 0,5 in mysql. i have 1000,s of records and it is causing IE to hang. firefox is quick. if someone could give me some guidance i would be grateful. thanks.

<p><fieldset><legend class="style8">Select a Box</legend>

      <select name="suggestTextField1" id="suggestTextField1">
      <option value="">Select a Box</option>
        <?php
do {  
?>
        <option value="<?php echo $row_rsSuggest1['boxref']?>"><?php echo $row_rsSuggest1['boxref']?></option>
        <?php
} while ($row_rsSuggest1 = mysql_fetch_assoc($rsSuggest1));
  $rows = mysql_num_rows($rsSuggest1);
  if($rows > 0) {
      mysql_data_seek($rsSuggest1, 0);
   $row_rsSuggest1 = mysql_fetch_assoc($rsSuggest1);
  }
?>
      </select>
      </fieldset>
      </p>


$colname_rsSuggest1 = "-1";
if (isset($_SESSION['kt_idcode_usr'])) {
  $colname_rsSuggest1 = (get_magic_quotes_gpc()) ? $_SESSION['kt_idcode_usr'] : addslashes($_SESSION['kt_idcode_usr']);
}
mysql_select_db($database_conn, $conn);
$query_rsSuggest1 = sprintf("SELECT DISTINCT `boxref` FROM `files` WHERE customer = '%s' AND boxstatus = 1 ORDER BY boxref ASC", $colname_rsSuggest1);
$rsSuggest1 = mysql_query($query_rsSuggest1, $conn) or die(mysql_error());
$row_rsSuggest1 = mysql_fetch_assoc($rsSuggest1);
$totalRows_rsSuggest1 = mysql_num_rows($rsSuggest1);

Answer 1

Unless you are using all the records later in the code, it is better to use a LIMIT clause. This will speed up your query and your script by extension. Look at some of the pagination scripts out there to get started.

Answer 2

You could change your do{}while() loop that is inserting the <option> to stop after 5 loops, but if you're only going to use 5 the better answer is to only fetch 5 from the database.

Answer 3

    $i = 0;
while ( $i < x ) {
$i++;
echo the records
}

That'll work I think

Answer 4

You can accomplish by incorporating paging into your dropdown list. The idea is to only show a handful of items at a time and provide Back/Next buttons that allow the user to view more. This is most easily done with a third-party library, but you can also do it yourself with CSS and Ajax.

Search for drop down lists with paging support. I haven't used PHP, so I can't point you to any good PHP libraries. There appears to be something at http://www.nitobi.com/products/combobox/paging/ that claims to be PHP compatible, but doesn't seem to work in Firefox. You might also have luck using the YUI Paginator (http://developer.yahoo.com/yui/paginator/) to build your own paging dropdown list.

Another option is to add Ajax search support to your dropdown list. This will allow the user to type what they are searching for, reducing the list from thousands to, hopefully, something much smaller. This is fairly common, so you shouldn't have trouble finding libraries to do this.

Answer 5

thank you robert. any idea where i would start with that? i have searched google but couldn't find anything to modify. thanks