0

i am trying to use the mysql data items into the select combo box. it basically works well but the problem is when there are multiple combo boxes it is a lot of load to the server since adding each combo box takes a lot of time. i am trying to figure out a better way. may be pull date once into an array just for the session and place it in the combo boxes. The logic is basically it is a quotation form where about 3500 items will be shown as drop down and user will select and then enter price and other details. the rows are dynamically added or deleted by the user. i am currently using the following code:-

<?php
$con = mysql_connect('blah blah blah');
if (!$con)  {
die ('Could not connect: ' . mysql_error());}
$db = mysql_select_db('blah',$con);
$extract1 = mysql_query("query") OR die (mysql_error());
$numrows1 = mysql_num_rows($extract1);
echo "<select name='itemname' title='select Item Name'>";
echo "
<option>Select Item Description</option>
    ";
while ($row1=mysql_fetch_assoc($extract1)) 
{
    $ic=$row1['ItemName'];
echo    "   
    <option>$ic</option>
    ";
}
    echo    "</select>";
mysql_close($con);
?>
mmdel
  • 1,279
  • 4
  • 21
  • 30

3 Answers3

1

Don't echo your option do this in your while statement:

$ic[]=$row1['ItemName'];

then outside of the while loop anywhere on the page:

foreach($ic as $i){
    echo "<option>".$i."</option>";
}
Paul
  • 1,527
  • 2
  • 16
  • 24
0

Yes, if your data changes infrequently enough, it may be a good idea to put the data into an array on the session, and render it from there. Depending on the frequency of change of your data, you might be able to get away with rendering it to a non-session data element (for example, a file on your filesystem) and populating your comboboxes from there (or just rendering all your choices into the combobox elements in that file, and using that data directly); that depends on the frequency by which your data is updated, of course.

Paul Sonier
  • 38,903
  • 3
  • 77
  • 117
  • thanks. new item codes are added frequently so i want to link it up to the database and also in future i would like to also populate the rest of the boxes like the price, etc from the database for the selected items. – mmdel Jun 02 '11 at 19:31
0

First off, Don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

<?php

$con = mysql_connect('blah blah blah') or die(mysql_error());
$db = mysql_select_db('blah',$con) or die(mysql_error());
$result = mysql_query("query MAYBE NARROW DOWN TO MORE RELEVANT RESULT SET") or die (mysql_error());

$option = '<select size="1" name="optionBox">';

if(mysql_num_rows($result)>=1){
    while ($row=mysql_fetch_assoc($result)){
        $option .="<option selected value=\"".$row['ItemName']."\">".$row['ItemName']."</option>\n";
    }
}else{
    $option .='<option selected value="0">No items to list</option>';
}
$option .='</select>';

echo $option;

mysql_close($con);
?>
Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106
  • thanks. i think i should probably narrow down to two select combos the second one populating lesser results based on the first selection. that way the user will be able to add as many rows without affecting the performance. – mmdel Jun 02 '11 at 19:41
  • echoing to the browser is always slower then building the output then echoing once, so it will speed it up there but dynamic selecting without refresh cannot be done with just php, you could obviously pull all the data and store it in the page, but that would defeat what your trying to achieve i suppose.. try to define your sql query to return whats relevant based on what section/category/page the select boxes are on – Lawrence Cherone Jun 02 '11 at 19:51