note 58594 added to function.mysql-query

This is my PHP/MySQL search function. This is a PHP function that looks in specified fields in a MySQL table for search terms. I have tested this on a database with 100,000 large records, and am happy with the average 1-4 sec response time. Also I have included a table printing example that will generate a table with clickable column sorting headings. This is perfect for quick search boxes on the top of your website. it may be ugly, but it works great <?php $GLOBALS['max_page'] = 50; // Max results per page // string $search_string, one or multiple case insensitive words to search for // string $table, the SQL table to search for search terms in // string or array $select_fields, a string of one or array of multiple // fields in $table to return (output) in the SQL result set // string or array $search_fields, a string of one or array of multiple // fields in $table to look for search terms in // string $sort, a $select_field to sort the result set by // integer $page_no, what "page" of data to display // integer $max_page, maximum number of records to return. (display on a "page") function search( $search_string, $table, $select_fields, $search_fields, $sort = '', $page_no = 1, $max_page = '' ) { // Build SQL query $search_terms = explode( ' ', $search_string ); $select_list = ''.implode( ', ', $select_fields ).''; foreach( $search_terms as $term ) { if( $term ) { foreach( $search_fields as $field ) { $where[] = "( $field LIKE '%$term%' )"; } } } $where_clause = implode( ' OR ', $where ); $sql = "SELECT $select_list FROM $table WHERE $where_clause ORDER BY '$sort'"; // Run no-limit Query, and count results if( !$results = mysql_query( $sql ) ) { err( "'$sql' SAID: ".mysql_error(), __LINE__, __FILE__ ); return FALSE; } $num_rows = mysql_num_rows( $results ); // If too large, run limited query $max_page = ( $max_page ) ? $max_page : $GLOBALS['max_page']; if( $num_rows > $max_page ) { $end = $max_page * $page_no; $start = $end - $max_page; $limited_sql = "$sql LIMIT $start, $end"; if( !$results = mysql_query( $limited_sql ) ) { err( "'$limited_sql' SAID: ".mysql_error(), __LINE__, __FILE__ ); return FALSE; } } // Add [num_rows] to, then copy $results to associative $arr $row = 0; while( $db = mysql_fetch_assoc( $results ) ) { foreach( $db as $k => $v ) { $arr[$row][$k] = $v; } $row++; } $arr['num_rows'] = $num_rows; // Not necessary, but can be useful. See example return $arr; } // Example Usage: $select_fields = array( 'id', 'name', 'age' 'Time Admitted' => 'admit_time' ); $results = search( $_GET['search_string'], 'usertable', $select_fields, array( 'id', 'name' ), $_GET['sort'] ); // Example sorting table output if( $results['num_rows'] > 0 ) { print "Found ".number_format( $results['num_rows'] )." matches.<BR>\n"; print "<table border=1 cellspacing=0 cellpadding=5>\n"; print "\t<tr>\n"; foreach( $fields as $k => $field ) { $fname = ( preg_match( '/^\d+$/', $k ) ) ? ucwords( strtolower( $field ) ) : $k; print "\t\t<th><a href='?search_string=$_GET[search_string]". "&sort=$field'>$fname</a></th>\n"; } print "\t</tr>\n"; foreach( $results as $row ) { print "\t<tr>\n"; foreach( $fields as $field ) { print "\t\t<td>$row[$field]</td>\n"; } print "\t</tr>\n"; } print "</table>\n"; } elseif( $results['num_rows'] === 0 ) { print "No Records Found"; } ?> ---- Manual Page -- http://www.php.net/manual/en/function.mysql-query.php Edit -- http://master.php.net/manage/user-notes.php?action=edit+58594 Delete: added to the manual -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes&reason=added+to+the+manual Delete: bad code -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes&reason=bad+code Delete: spam -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes&reason=spam Delete: useless -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes&reason=useless Delete: non-english -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes&reason=non-english Delete: already in docs -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes&reason=already+in+docs Delete: other reasons -- http://master.php.net/manage/user-notes.php?action=delete+58594&report=yes Reject -- http://master.php.net/manage/user-notes.php?action=reject+58594&report=yes Search -- http://master.php.net/manage/user-notes.php