3
\$\begingroup\$

I'm using this way to load a combo box with branch names, but it's taking more time if there is more number of data.

Is there a better way to do this, like binding in PHP?

<select name="CmbBr" id="CmbBr">                                            
  <?php                                          
     include ("dbcon_.php");
     $sql = mysql_query("SELECT BranchName FROM TblBranch ORDER BY BranchName ASC");
     if(!mysql_num_rows($sql) == 0)
     {
        while($row = mysql_fetch_array($sql))
       {
           ?>
              <option value="<?php echo $row["BranchName"];?>"><?php echo $row["BranchName"]; ?></option>
           <?php
       }
     }
  ?>
</select>
\$\endgroup\$

migrated from stackoverflow.com May 23 '12 at 21:44

This question came from our site for professional and enthusiast programmers.

  • 3
    \$\begingroup\$ Separate your HTML and SQL. \$\endgroup\$ – alex May 23 '12 at 12:09
  • 1
    \$\begingroup\$ Separate html & sql? can you show me the code alex? \$\endgroup\$ – Arul Kumar May 23 '12 at 12:11
  • \$\begingroup\$ Unless you have a ridiculous number of options (in the 1000s) there should not be a noticeable execution time increase with this code dependent on the number of rows. If there is, you have a more serious underlying problem with you server setup. \$\endgroup\$ – DaveRandom May 23 '12 at 12:12
  • \$\begingroup\$ @ArulKumar: How you heard something known as MVC? That enables you to separate presentation logic from the business logic?? \$\endgroup\$ – verisimilitude May 23 '12 at 12:18
  • 1
    \$\begingroup\$ @ArulKumar What alex means is that you should do all of you preparatory DB queries at the top of the code, and output all your HTML at the bottom, more like this. I personally prefer not to mix HTML and PHP at all, more like this. \$\endgroup\$ – DaveRandom May 23 '12 at 12:19
3
\$\begingroup\$

I'd just like to note that use of the mysql_* functions are going to be deprecated in the next version or the one after than(link at the end). You should start using mysqli or PDO.

http://news.php.net/php.internals/53799

\$\endgroup\$
0
\$\begingroup\$

Yes, there is a better way but it's micro optimization and not really worth it. In php you can unroll the loop like this:

  <?php
  $n = $ITERATIONS % 8;
  while ($n--) $val++;
  $n = (int)($ITERATIONS / 8);
  while ($n--) {
     $val++;
     $val++;
     $val++;
     $val++;
     $val++;
     $val++;
     $val++;
     $val++; 
  }
  ?> 

See also my question here: https://stackoverflow.com/questions/8104958/duff-device-in-php-not-possible. But you need to fetch all rows first????

\$\endgroup\$
0
\$\begingroup\$
<?php
 include ('dbcon_.php');

 $branches = array();
 $branch_query = mysql_query("SELECT BranchName FROM TblBranch ORDER BY BranchName ASC");
 while ( $row = mysql_fetch_array($branch_query) ) {
   $branches[] = $row['BranchName'];
 }
?>
<select name="CmbBr" id="CmbBr">
<?php
  foreach ( $branches as $branch ) {
?>
  <option value="<?php echo $branch; ?>"><?php echo $branch; ?></option>
<?php
  }
?>
</select>

This has some separation between HTML and SQL (as mentioned in the comments). It's obviously missing most of the page code (no html tag for instance) but hopefully gives you the idea. You should have all your data accesses (SQL queries) before you output any HTML.

Some other changes that I made:

  • Moved the db include to the very top of the page before any variable set up, etc.
  • Removed the extra if(!mysql_num_rows($sql) == 0). The while loop will handle that case. It's rare that you'll use mysql_num_rows.
  • Harmonized the whitespace. I believe it's more consistent now.
  • I always use single quotes rather than double quotes when I'm not doing variable interpolation.
  • I renamed $sql to $branch_query because I'd only call a string $sql. What you have is a query handle.

My only other thought was also mentioned in the comments: make sure that there is an index on BranchName. It does not have to be a PRIMARY key nor even a unique index. Any index will do. Without an index, it has to generate the query results in memory, sort them, and then send them to you. With the index, it can just read this query directly from the index in sorted order.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy