session_start(); ?>
include("lib/dbinfo.inc.php"); // Proceed with query only if Search button has been pressed or if Prev/Next links clicked if ((isset($_POST['flag'])) or (isset($_GET['page']))) { // how many rows to show per page $rowsPerPage = 5; // by default we show first page $pageNum = 1; // if $_GET['page'] defined, use it as page number if(isset($_GET['page'])) { $pageNum = $_GET['page']; } // counting the offset $offset = ($pageNum - 1) * $rowsPerPage; // establish connection to database $dbh=mysql_connect (localhost,$username,$password) or die ('Unable to connect to the database: ' . mysql_error()); mysql_select_db ($username.$database) or die ("Unable to select database"); // create the query string if user clicked Search button if (!isset($_GET['page'])) { $where_clauses = array('1=1'); foreach ($fields as $field) { if (isset($_POST[$field]) && ($_POST[$field] != '')) // has a field name been returned? { if (($field == 'format') or ($field == 'publisher') or ($field == 'country')) // is the field a dropdown field? { if ($_POST[$field] != 0) // has ALL been selected? { $where_clauses[] = $field . ' = \'' . mysql_real_escape_string($_POST[$field]) . '\''; } } else { $where_clauses[] = $field . ' LIKE \'%' . mysql_real_escape_string($_POST[$field]) . '%\''; } } } $query = 'SELECT tbl_main.id, tbl_main.cat_no, tbl_main.title, tbl_formats.format_desc, '. 'tbl_publishers.pub_name, tbl_main.year, tbl_countries.country_name, tbl_main.img, tbl_main.notes '. 'FROM (tbl_main LEFT JOIN tbl_formats ON tbl_main.format = tbl_formats.format_id) '. 'LEFT JOIN tbl_publishers ON tbl_main.publisher = tbl_publishers.pub_id '. 'LEFT JOIN tbl_countries ON tbl_main.country = tbl_countries.country_id '; $where = 'WHERE '.implode(' AND ', $where_clauses); // store query string in session so it can accessed by POST and GET methods //session_register('sql_query'); $_SESSION['sql_query'] = ($query); //session_register('sql_where'); $_SESSION['sql_where'] = $where; } $orderby = ' ORDER BY title LIMIT '.$offset.','.$rowsPerPage; $result = mysql_query($_SESSION['sql_query'].$_SESSION['sql_where'].$orderby); $numrows=mysql_numrows($result); if ($numrows < 1) { echo "No Records Found"; } else { ?>
Cat No. | Title | Format | Publisher | Year | Country | Image | Notes | $i=0; while ($i < $numrows) { $cat_no=mysql_result($result,$i,"cat_no"); $title=mysql_result($result,$i,"title"); $format=mysql_result($result,$i,"format_desc"); $publisher=mysql_result($result,$i,"pub_name"); $year=mysql_result($result,$i,"year"); $country=mysql_result($result,$i,"country_name"); $filename = 'kwkdiscog/db_pics/'.mysql_result($result,$i,"img").'.jpg'; if (file_exists($filename)) { $picture=$filename; } else { $picture='x'; } $notes=mysql_result($result,$i,"notes"); ?>echo "$cat_no"; ?> | echo "$title"; ?> | echo "$format"; ?> | echo "$publisher"; ?> | echo "$year"; ?> | echo "$country"; ?> | if ($picture != 'x') { echo "click"; } else { echo ""; } ?> | echo "$notes"; ?> | ++$i; } echo "
---|
"; } // how many rows do we have in the recordset? $query = 'SELECT COUNT(id) AS totnumrows FROM tbl_main '.$_SESSION['sql_where']; $result = mysql_query($query) or die('Error, count query failed'); $row = mysql_fetch_array($result, MYSQL_ASSOC); $totnumrows = $row['totnumrows']; // how many pages do we have when using paging? $maxPage = ceil($totnumrows/$rowsPerPage); $self = $_SERVER['PHP_SELF']; // create the 'previous' and 'next' links plus 'first page' and 'last page' links // only show if at least 1 page returned if ($maxPage > 0) { // print 'previous' link only if we're not on page one if ($pageNum > 1) { $page = $pageNum - 1; $prev = " [<] "; $first = " [<<] "; } else { $prev = ' [<] '; // we're on page one, don't enable 'previous' link $first = ' [<<] '; // nor 'first page' link } // print 'next' link only if we're not on the last page and if there is more than one page if (($pageNum < $maxPage) && ($maxPage > 1) && ($pageNum > 0)) { $page = $pageNum + 1; $next = " [>] "; $last = " [>>] "; } else { $next = ' [>] '; // we're on the last page, don't enable 'next' link $last = ' [>>] '; // nor 'last page' link } // print the page navigation link echo "
".$first . $prev . " Showing page $pageNum of $maxPage " . $next . $last; mysql_close(); } } ?>