Get rid of mysql_data_seek(). Use pre-cached PHP array for mysqli

compatibility and bandwidth saving.

Memory issues by caching should not arise, as the used cache will (in our
previously used cases) only cache index fields and max. 200 relation rows.

I tested performance on my machine: By fetching 400kb of data twice via
mysql_fetch_row() -> mysql_data_seek() -> mysql_fetch_row() this was
performed in 0.25 seconds. By fetching it once and caching in a PHP array
via mysql_fetch_row -> foreach this is performed in 0.32 seconds.

With usual data as used in PMA (10kb) I could only detect a performance
change of 0.009 seconds, which is respectable I think.

By not using mysql_data_seek we are later able to use global mysqli
USE_RESULT options to fetch rows singly. I discussed that yesterday with
Rabus, and if there are any objections we can surely discuss that further on
the list.

9 files changed, 36 insertions, 15 deletions

diff --git a/ChangeLog b/ChangeLog
index af5a8f6de2..dfb01968c4 100755
--- a/ChangeLog
+++ b/ChangeLog
@@ -5,6 +5,13 @@ phpMyAdmin - Changelog
 $Id$
 $Source$
 
+2004-01-28 Garvin Hicking <MyDoom@supergarv.de>
+    * browser_foreigners, tbl_change.php, tbl_indexes.php,
+      tbl_properties_structure.php, tbl_relation.php, tbl_select.php,
+      libraries/get_foreign.lib.php, libraries/relation.lib.php:
+      Get rid of mysql_data_seek(). Use pre-cached PHP array for
+      mysqli compatibility and bandwidth saving.
+
 2004-01-27 Marc Delisle  <lem9@users.sourceforge.net>
     * main.php: bug 884606, MySQL version check before server choice
 
diff --git a/browse_foreigners.php b/browse_foreigners.php
index e7e1c0fd7c..7d032a37bd 100644
--- a/browse_foreigners.php
+++ b/browse_foreigners.php
@@ -138,7 +138,7 @@ $header = '    <tr>
 
 echo $header;
 
-if (isset($disp) && $disp) {
+if (isset($disp_row) && is_array($disp_row)) {
     function dimsort($arrayA, $arrayB) {
         $keyA = key($arrayA);
         $keyB = key($arrayB);
@@ -153,7 +153,7 @@ if (isset($disp) && $disp) {
     $mysql_key_relrow = array();
     $mysql_val_relrow = array();
     $count = 0;
-    while ($relrow = @PMA_DBI_fetch_assoc($disp)) {
+    foreach($disp_row AS $disp_row_key => $relrow) {
         if ($foreign_display != FALSE) {
             $val = $relrow[$foreign_display];
         } else {
diff --git a/libraries/get_foreign.lib.php b/libraries/get_foreign.lib.php
index 35957cf084..5bfb1a820f 100644
--- a/libraries/get_foreign.lib.php
+++ b/libraries/get_foreign.lib.php
@@ -37,6 +37,17 @@ if ($foreigners && isset($foreigners[$field])) {
                          . (($foreign_display == FALSE) ? '' :' ORDER BY ' . PMA_backquote($foreign_table) . '.' . PMA_backquote($foreign_display))
                          . (isset($foreign_limit) ? $foreign_limit : '');
         $disp            = PMA_DBI_query($dispsql);
+        if ($disp) {
+            // garvin: If a resultset has been created, pre-cache it in the $disp_row array
+            // This helps us from not needing to use mysql_data_seek by accessing a pre-cached
+            // PHP array. Usually those resultsets are not that big, so a performance hit should
+            // not be expected.
+            $disp_row = array();
+            while ($single_disp_row = @PMA_DBI_fetch_assoc($disp)) {
+                $disp_row[] = $single_disp_row;
+            }
+            @PMA_DBI_free_result($disp);
+        }
     }
     else {
         unset($disp);
diff --git a/libraries/relation.lib.php b/libraries/relation.lib.php
index f5fa52267c..de9162726c 100644
--- a/libraries/relation.lib.php
+++ b/libraries/relation.lib.php
@@ -601,7 +601,8 @@ function PMA_foreignDropdown($disp, $foreign_field, $foreign_display, $data, $ma
     $ret = '<option value=""></option>' . "\n";
 
     $reloptions = array('content-id' => array(), 'id-content' => array());
-    while ($relrow = @PMA_DBI_fetch_assoc($disp)) {
+    
+    foreach($disp AS $disp_key => $relrow) {
         $key   = $relrow[$foreign_field];
         if (strlen($relrow[$foreign_display]) <= $cfg['LimitChars']) {
             $value  = (($foreign_display != FALSE) ? htmlspecialchars($relrow[$foreign_display]) : '');
diff --git a/tbl_change.php b/tbl_change.php
index 08c99d8eef..3a6e998d70 100644
--- a/tbl_change.php
+++ b/tbl_change.php
@@ -507,18 +507,18 @@ foreach($loop_array AS $vrowcount => $vrow) {
             </script>
             </td>
             <?php
-        } else if (isset($disp) && $disp) {
+        } else if (isset($disp_row) && is_array($disp_row)) {
             ?>
             <td bgcolor="<?php echo $bgcolor; ?>">
             <?php echo $backup_field . "\n"; ?>
             <input type="hidden" name="fields_type<?php echo $vkey; ?>[<?php echo urlencode($field); ?>]" value="foreign" />
             <input type="hidden" name="fields<?php echo $vkey; ?>[<?php echo urlencode($field); ?>]" value="" id="field_<?php echo $i; ?>_1" />
             <select name="field_<?php echo md5($field); ?><?php echo $vkey; ?>[]" <?php echo $chg_evt_handler; ?>="return unNullify('<?php echo urlencode($field); ?>', '<?php echo $vkey; ?>')" tabindex="<?php echo (($i * $m_rows) + 1); ?>" id="field_<?php echo ($i * $m_rows); ?>_3">
-                <?php echo PMA_foreignDropdown($disp, $foreign_field, $foreign_display, $data, 100); ?>
+                <?php echo PMA_foreignDropdown($disp_row, $foreign_field, $foreign_display, $data, 100); ?>
             </select>
             </td>
             <?php
-            unset($disp);
+            unset($disp_row);
         }
         else if ($cfg['LongtextDoubleTextarea'] && strstr($type, 'longtext')) {
             ?>
diff --git a/tbl_indexes.php b/tbl_indexes.php
index cab4dcf95a..1eeaa04904 100644
--- a/tbl_indexes.php
+++ b/tbl_indexes.php
@@ -109,16 +109,18 @@ if (defined('PMA_IDX_INCLUDED')) {
 
 // Get fields and stores their name/type
 // fields had already been grabbed in "tbl_properties.php"
-if (defined('PMA_IDX_INCLUDED')) {
-    mysql_data_seek($fields_rs, 0); // !UNWRAPPED FUNCTION!
-} else {
+if (!defined('PMA_IDX_INCLUDED')) {
     $fields_rs   = PMA_DBI_query('SHOW FIELDS FROM ' . PMA_backquote($table) . ';');
     $fields_cnt  = PMA_DBI_num_rows($fields_rs);
+    $save_row   = array();
+    while ($row = PMA_DBI_fetch_assoc($fields_rs)) {
+        $save_row[] = $row;
+    }
 }
 
 $fields_names           = array();
 $fields_types           = array();
-while ($row = PMA_DBI_fetch_assoc($fields_rs)) {
+foreach($save_row AS $saved_row_key => $row) {
     $fields_names[]     = $row['Field'];
     // loic1: set or enum types: slashes single quotes inside options
     if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
diff --git a/tbl_properties_structure.php b/tbl_properties_structure.php
index 7dff576119..4a8b3cc34c 100644
--- a/tbl_properties_structure.php
+++ b/tbl_properties_structure.php
@@ -112,8 +112,10 @@ if ($GLOBALS['cfg']['ShowPropertyComments']) {
 $i         = 0;
 $aryFields = array();
 $checked   = (!empty($checkall) ? ' checked="checked"' : '');
+$save_row  = array();
 
 while ($row = PMA_DBI_fetch_assoc($fields_rs)) {
+    $save_row[] = $row;
     $i++;
     $bgcolor          = ($i % 2) ? $cfg['BgcolorOne'] : $cfg['BgcolorTwo'];
     $aryFields[]      = $row['Field'];
diff --git a/tbl_relation.php b/tbl_relation.php
index 84b4b6c22a..d39e6a6663 100644
--- a/tbl_relation.php
+++ b/tbl_relation.php
@@ -505,8 +505,7 @@ if ($col_rs && PMA_DBI_num_rows($col_rs) > 0) {
         <option value="">---</option>
         <?php
         echo "\n";
-        mysql_data_seek($col_rs, 0); // !UNWRAPPED FUNCTION!
-        while ($row = @PMA_DBI_fetch_array($col_rs)) {
+        foreach($save_row AS $row) {
             echo '        <option value="' . htmlspecialchars($row['Field']) . '"';
             if (isset($disp) && $row['Field'] == $disp) {
                 echo ' selected="selected"';
diff --git a/tbl_select.php b/tbl_select.php
index 6068505c5a..297823d3e3 100644
--- a/tbl_select.php
+++ b/tbl_select.php
@@ -158,12 +158,11 @@ if (!isset($param) || $param[0] == '') {
         // we got a bug report: in some cases, even if $disp is true,
         // there are no rows, so we add a fetch_array
 
-        if ($foreigners && isset($foreigners[$field]) && isset($disp) && $disp && PMA_DBI_fetch_row($disp)) {
+        if ($foreigners && isset($foreigners[$field]) && isset($disp_row) && is_array($disp_row)) {
             // f o r e i g n    k e y s
             echo '                    <select name="fields[]">' . "\n";
             // go back to first row
-            mysql_data_seek($disp,0); // !UNWRAPPED FUNCTION!
-            echo PMA_foreignDropdown($disp, $foreign_field, $foreign_display, $data, 100);
+            echo PMA_foreignDropdown($disp_row, $foreign_field, $foreign_display, $data, 100);
             echo '                    </select>' . "\n";
         } else if (isset($foreign_link) && $foreign_link == true) {
         ?>