AviNahum Posted August 4, 2009 Share Posted August 4, 2009 hey, im using the DB Driver of IPB system... and tried to select from DB into the while loop... it's selects only the first row, insted the all rows in the table... function get_friends($id, $friends_per_row) { global $ibforums, $DB, $std, $print; // select all user freinds $DB->query("SELECT * FROM ibf_member_friends WHERE member_id='{$id}' ORDER BY id"); // display the start of the friends table $friends_list .= $this->html->show_friends_start(); while ( $flist = $DB->fetch_row() ) { // add to the count var 1 $count++; // setting the friend id to var $fid = $flist['friend_id']; // select the friend details $DB->query("SELECT * FROM ibf_members WHERE id='{$fid}'"); $friend = $DB->fetch_row(); // display the row of the friends table $friends_list .= $this->html->show_friends_row($friend); // checking if we have to break the line if ( $count == $friends_per_row ) { // displaying the break row $friends_list .= $this->html->show_friends_break(); // setting the $count var to 0 again $count = 0; } } // display the end of the friends table $friends_list .= $this->html->show_friends_end(); return $friends_list; } and this is the DB class: <?php /* +-------------------------------------------------------------------------- | Invision Power Board v1.4 PF1 | ======================================== | by Matthew Mecham | (c) 2001 - 2003 Invision Power Services | http://www.invisionpower.com | ======================================== | Web: http://www.invisionboard.com | Time: Fri, 14 Nov 2003 19:02:31 GMT | Release: 1d33364ae768b0ad802fa73163bd7204 | Email: matt@invisionpower.com | Licence Info: http://www.invisionboard.com/?license +--------------------------------------------------------------------------- | | > mySQL DB abstraction module | > Module written by Matt Mecham | > Date started: 14th February 2002 | | > Module Version Number: 1.0.0 +-------------------------------------------------------------------------- */ class db_driver { var $obj = array ( "sql_database" => "" , "sql_user" => "root" , "sql_pass" => "" , "sql_host" => "localhost", "sql_port" => "" , "persistent" => "0" , "sql_tbl_prefix" => "ibf_" , "cached_queries" => array(), 'debug' => 0, ); var $query_id = ""; var $connection_id = ""; var $query_count = 0; var $record_row = array(); var $return_die = 0; var $error = ""; var $failed = 0; /*========================================================================*/ // Connect to the database /*========================================================================*/ function connect() { if ($this->obj['persistent']) { $this->connection_id = mysql_pconnect( $this->obj['sql_host'] , $this->obj['sql_user'] , $this->obj['sql_pass'] ); } else { $this->connection_id = mysql_connect( $this->obj['sql_host'] , $this->obj['sql_user'] , $this->obj['sql_pass'] ); } if ( !mysql_select_db($this->obj['sql_database'], $this->connection_id) ) { echo ("ERROR: Cannot find database ".$this->obj['sql_database']); } } /*========================================================================*/ // Process a query /*========================================================================*/ function query($the_query, $bypass=0) { //-------------------------------------- // Change the table prefix if needed //-------------------------------------- if ($bypass != 1) { if ($this->obj['sql_tbl_prefix'] != "ibf_") { $the_query = preg_replace("/ibf_(\S+?)([\s\.,]|$)/", $this->obj['sql_tbl_prefix']."\\1\\2", $the_query); } } if ($this->obj['debug']) { global $Debug, $ibforums; $Debug->startTimer(); } $this->query_id = mysql_query($the_query, $this->connection_id); if (! $this->query_id ) { $this->fatal_error("mySQL query error: $the_query"); } if ($this->obj['debug']) { $endtime = $Debug->endTimer(); if ( preg_match( "/^select/i", $the_query ) ) { $eid = mysql_query("EXPLAIN $the_query", $this->connection_id); $ibforums->debug_html .= "<table width='95%' border='1' cellpadding='6' cellspacing='0' bgcolor='#FFE8F3' align='center'> <tr> <td colspan='8' style='font-size:14px' bgcolor='#FFC5Cb'><b>Select Query</b></td> </tr> <tr> <td colspan='8' style='font-family:courier, monaco, arial;font-size:14px;color:black'>$the_query</td> </tr> <tr bgcolor='#FFC5Cb'> <td><b>table</b></td><td><b>type</b></td><td><b>possible_keys</b></td> <td><b>key</b></td><td><b>key_len</b></td><td><b>ref</b></td> <td><b>rows</b></td><td><b>Extra</b></td> </tr>\n"; while( $array = mysql_fetch_array($eid) ) { $type_col = '#FFFFFF'; if ($array['type'] == 'ref' or $array['type'] == 'eq_ref' or $array['type'] == 'const') { $type_col = '#D8FFD4'; } else if ($array['type'] == 'ALL') { $type_col = '#FFEEBA'; } $ibforums->debug_html .= "<tr bgcolor='#FFFFFF'> <td>$array[table] </td> <td bgcolor='$type_col'>$array[type] </td> <td>$array[possible_keys] </td> <td>$array[key] </td> <td>$array[key_len] </td> <td>$array[ref] </td> <td>$array[rows] </td> <td>$array[Extra] </td> </tr>\n"; } if ($endtime > 0.1) { $endtime = "<span style='color:red'><b>$endtime</b></span>"; } $ibforums->debug_html .= "<tr> <td colspan='8' bgcolor='#FFD6DC' style='font-size:14px'><b>mySQL time</b>: $endtime</b></td> </tr> </table>\n<br />\n"; } else { $ibforums->debug_html .= "<table width='95%' border='1' cellpadding='6' cellspacing='0' bgcolor='#FEFEFE' align='center'> <tr> <td style='font-size:14px' bgcolor='#EFEFEF'><b>Non Select Query</b></td> </tr> <tr> <td style='font-family:courier, monaco, arial;font-size:14px'>$the_query</td> </tr> <tr> <td style='font-size:14px' bgcolor='#EFEFEF'><b>mySQL time</b>: $endtime</span></td> </tr> </table><br />\n\n"; } } $this->query_count++; $this->obj['cached_queries'][] = $the_query; return $this->query_id; } /*========================================================================*/ // Fetch a row based on the last query /*========================================================================*/ function fetch_row($query_id = "") { if ($query_id == "") { $query_id = $this->query_id; } $this->record_row = mysql_fetch_array($query_id, MYSQL_ASSOC); return $this->record_row; } /*========================================================================*/ // Fetch the number of rows affected by the last query /*========================================================================*/ function get_affected_rows() { return mysql_affected_rows($this->connection_id); } /*========================================================================*/ // Fetch the number of rows in a result set /*========================================================================*/ function get_num_rows() { return mysql_num_rows($this->query_id); } /*========================================================================*/ // Fetch the last insert id from an sql autoincrement /*========================================================================*/ function get_insert_id() { return mysql_insert_id($this->connection_id); } /*========================================================================*/ // Return the amount of queries used /*========================================================================*/ function get_query_cnt() { return $this->query_count; } /*========================================================================*/ // Free the result set from mySQLs memory /*========================================================================*/ function free_result($query_id="") { if ($query_id == "") { $query_id = $this->query_id; } @mysql_free_result($query_id); } /*========================================================================*/ // Shut down the database /*========================================================================*/ function close_db() { return mysql_close($this->connection_id); } /*========================================================================*/ // Return an array of tables /*========================================================================*/ function get_table_names() { $result = mysql_list_tables($this->obj['sql_database']); $num_tables = @mysql_numrows($result); for ($i = 0; $i < $num_tables; $i++) { $tables[] = mysql_tablename($result, $i); } mysql_free_result($result); return $tables; } /*========================================================================*/ // Return an array of fields /*========================================================================*/ function get_result_fields($query_id="") { if ($query_id == "") { $query_id = $this->query_id; } while ($field = mysql_fetch_field($query_id)) { $Fields[] = $field; } //mysql_free_result($query_id); return $Fields; } /*========================================================================*/ // Basic error handler /*========================================================================*/ function fatal_error($the_error) { global $INFO; // Are we simply returning the error? if ($this->return_die == 1) { $this->error = mysql_error(); $this->error_no = mysql_errno(); $this->failed = 1; return; } $the_error .= "\n\nmySQL error: ".mysql_error()."\n"; $the_error .= "mySQL error code: ".$this->error_no."\n"; $the_error .= "Date: ".date("l dS of F Y h:i:s A"); $out = "<html><head><title>Invision Power Board Database Error</title> <style>P,BODY{ font-family:arial,sans-serif; font-size:11px; }</style></head><body> <br><br><blockquote><b>There appears to be an error with the {$INFO['board_name']} database.</b><br> You can try to refresh the page by clicking <a href=\"javascript:window.location=window.location;\">here</a>, if this does not fix the error, you can contact the board administrator by clicking <a href='mailto:{$INFO['email_in']}?subject=SQL+Error'>here</a> <br><br><b>Error Returned</b><br> <form name='mysql'><textarea rows=\"15\" cols=\"60\">".htmlspecialchars($the_error)."</textarea></form><br>We apologise for any inconvenience</blockquote></body></html>"; echo($out); die(""); } /*========================================================================*/ // Create an array from a multidimensional array returning formatted // strings ready to use in an INSERT query, saves having to manually format // the (INSERT INTO table) ('field', 'field', 'field') VALUES ('val', 'val') /*========================================================================*/ function compile_db_insert_string($data) { $field_names = ""; $field_values = ""; foreach ($data as $k => $v) { $v = preg_replace( "/'/", "\\'", $v ); //$v = preg_replace( "/#/", "\\#", $v ); $field_names .= "$k,"; $field_values .= "'$v',"; } $field_names = preg_replace( "/,$/" , "" , $field_names ); $field_values = preg_replace( "/,$/" , "" , $field_values ); return array( 'FIELD_NAMES' => $field_names, 'FIELD_VALUES' => $field_values, ); } /*========================================================================*/ // Create an array from a multidimensional array returning a formatted // string ready to use in an UPDATE query, saves having to manually format // the FIELD='val', FIELD='val', FIELD='val' /*========================================================================*/ function compile_db_update_string($data) { $return_string = ""; foreach ($data as $k => $v) { $v = preg_replace( "/'/", "\\'", $v ); $return_string .= $k . "='".$v."',"; } $return_string = preg_replace( "/,$/" , "" , $return_string ); return $return_string; } /*========================================================================*/ // Test to see if a field exists by forcing and trapping an error. // It ain't pretty, but it do the job don't it, eh? // Posh my ass. // Return 1 for exists, 0 for not exists and jello for the naked guy // Fun fact: The number of times I spelt 'field' as 'feild'in this part: 104 /*========================================================================*/ function field_exists($field, $table) { $this->return_die = 1; $this->error = ""; $this->query("SELECT COUNT($field) as count FROM $table"); $return = 1; if ( $this->failed ) { $return = 0; } $this->error = ""; $this->return_die = 0; $this->error_no = 0; $this->failed = 0; return $return; } } // end class ?> i dont really know if the problem is in the DB class or in my code, but when im doing this in an normal way it's works... ***sorry if i had some spell mistakes*** Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 4, 2009 Share Posted August 4, 2009 running queries in a loop is NOT efficient. indeed many a host limit you to 50 queries per script run. better to select ALL in one query like so... <?php function get_friends($id, $friends_per_row) { global $ibforums, $DB, $std, $print; // select all user freinds $DB->query("SELECT * FROM ibf_member_friends WHERE member_id=".$id." ORDER BY b.id"); $farr = array(); while($flist = $DB->fetch_row()) { $farr[] = $flist['friend_id']; } // display the start of the friends table $friends_list .= $this->html->show_friends_start(); $DB->query("SELECT * FROM ibf_members WHERE id IN (" . implode(',',$farr) . ")"); while ( $friend = $DB->fetch_row() ) { // add to the count var 1 $count++; // display the row of the friends table $friends_list .= $this->html->show_friends_row($friend); // checking if we have to break the line if ( $count == $friends_per_row ) { // displaying the break row $friends_list .= $this->html->show_friends_break(); // setting the $count var to 0 again $count = 0; } } // display the end of the friends table $friends_list .= $this->html->show_friends_end(); return $friends_list; } ?> that should be a bit nicer [could be improved as you can get the details of friends in one query]. Quote Link to comment Share on other sites More sharing options...
AviNahum Posted August 4, 2009 Author Share Posted August 4, 2009 thanks u!!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.