php - not able to get results to paginate -
good day all, have been beating head against this, unless have missed how supposed work had better drawing board. went , got script working search database , 50,000 records have. searched internet there know , pagination or how work found coding it. since relative newbie on mysql , php, @ loss.
<?php error_reporting(0); include("config.php"); ?> <!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <title>gt entertainment song database</title> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script> <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/> <style> body, td { font-family:arial, helvetica, sans-serif; font-size:12px; } </style> </head> <body> <form id="form1" name="form1" method="post" action="search.php"> <label>date added</label> <select name="clock"> <option value="">--</option> <?php $sql = "select * ".$settings["data_table"]." group clock order clock limit 1,40"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["clock"]."'". ($row["clock"]==$_request["clock"] ? " selected" : "").">".$row["clock"]."</option>"; } ?> </select> <label>title or artist:</label> <input type="text" name="string" id="string" value="<?php echo stripcslashes($_request["string"]); ?>" /> <label>disk</label> <select name="disk"> <option value="">--</option> <?php $sql = "select * ".$settings["data_table"]." group disk order disk limit 1,40"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["disk"]."'".($row["disk"]==$_request["disk"] ? " selected" : "").">".$row["disk"]."</option>"; } ?><br> </select><br> <label>duet</label> <select name="duet"> <option value="">--</option> <?php $sql = "select * ".$settings["data_table"]." group duet order duet limit 1,40"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["duet"]."'".($row["duet"]==$_request["duet"] ? " selected" : "").">".$row["duet"]."</option>"; } ?> </select> <label>comment</label> <select name="comment"> <option value="">--</option> <?php $sql = "select * ".$settings["data_table"]." group comment order comment limit 1,40"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["comment"]."'". ($row["comment"]==$_request["comment"] ? " selected" : "").">".$row["comment"]."</option>"; } ?> </select> <input type="submit" name="button" id="button" value="filter" /> </label> <a href="search.php"> reset</a> </form> <br /><br /> <table width="700" border="1" cellspacing="0" cellpadding="4"> <tr> <td width="90" bgcolor="#cccccc"><strong>date added</strong></td> <td width="95" bgcolor="#cccccc"><strong>track</strong></td> <td width="159" bgcolor="#cccccc"><strong>artist</strong></td> <td width="191" bgcolor="#cccccc"><strong>disk</strong></td> <td width="113" bgcolor="#cccccc"><strong>duet</strong></td> <td width="113" bgcolor="#cccccc"><strong>comment</strong></td> </tr> <?php // start query $sql = "select * ".$settings["data_table"]." 1=1 "; if ($_request["string"]!='') { $sql .= " , (track '%".mysql_real_escape_string($_request["string"])."%' or artist '%".mysql_real_escape_string($_request["string"])."%')"; } if ($_request["disk"]!='') { $sql .= " , disk='".mysql_real_escape_string($_request["disk"])."'"; } if ($_request["comment"]!='') { $sql .= " , comment='".mysql_real_escape_string($_request["comment"])."'"; } if ($_request["clock"]!='') { $sql .= " , clock='".mysql_real_escape_string($_request["clock"])."'"; } if ($_request["duet"]!='') { $sql .= " , duet='".mysql_real_escape_string($_request["duet"])."'"; } $sql .= " limit 40"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); if (mysql_num_rows($sql_result)>0) { while ($row = mysql_fetch_assoc($sql_result)) { ?> <tr> <td><?php echo $row["clock"]; ?></td> <td><?php echo $row["track"]; ?></td> <td><?php echo $row["artist"]; ?></td> <td><?php echo $row["disk"]; ?></td> <td><?php echo $row["duet"]; ?></td> <td><?php echo $row["comment"]; ?></td> </tr> <?php } } else { ?> <tr><td colspan="6">no results found.</td> <?php } ?> now have looked @ following code , cannot seem incorporate working script results can split separate pages if amount of results above limit have set.
i found information here
<?php if (isset($_get["page"])) { $page = $_get["page"]; } else { $page=1; }; $start_from = ($page-1) * 20; $sql = "select * students order name asc limit $start_from, 20"; $rs_result = mysql_query ($sql,$connection); ?> <table> <tr><td>name</td><td>phone</td></tr> <?php while ($row = mysql_fetch_assoc($rs_result)) { ?> <tr> <td><? echo $row["name"]; ?></td> <td><? echo $row["phonenumber"]; ?></td> </tr> <?php }; ?> </table> <?php $sql = "select count(name) students"; $rs_result = mysql_query($sql,$connection); $row = mysql_fetch_row($rs_result); $total_records = $row[0]; $total_pages = ceil($total_records / 20); ($i=1; $i<=$total_pages; $i++) { echo "<a href='pagination.php?page=".$i."'>".$i."</a> "; }; ?> everytime try make work or place code should fit, keep getting errors , @ loss.
below have gotten in attempt in making pagination work me, not sure if going in right direction
<?php if (isset($_get["page"])) { $page = $_get["page"]; } else { $page=1; }; $start_from = ($page-1) * 20; $sql = "select * ".$settings["data_table"]." order clock asc limit $start_from, 20"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["clock"]."'". ($row["clock"]==$_request["clock"] ? " selected" : "").">".$row["clock"]."</option>"; } ?> </select> <label>title or artist:</label> <input type="text" name="string" id="string" value="<?php echo stripcslashes($_request["string"]); ?>" /> <label>disk</label> <select name="disk"> <option value="">--</option> <?php if (isset($_get["page"])) { $page = $_get["page"]; } else { $page=1; }; $start_from = ($page-1) * 20; $sql = "select * ".$settings["data_table"]." order disk asc limit $start_from, 20"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["disk"]."'".($row["disk"]==$_request["disk"] ? " selected" : "").">".$row["disk"]."</option>"; } ?><br> </select><br> <label>duet</label> <select name="duet"> <option value="">--</option> <?php if (isset($_get["page"])) { $page = $_get["page"]; } else { $page=1; }; $start_from = ($page-1) * 20; $sql = "select * ".$settings["data_table"]." order duet asc limit $start_from, 20"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["duet"]."'".($row["duet"]==$_request["duet"] ? " selected" : "").">".$row["duet"]."</option>"; } ?> </select> <label>comment</label> <select name="comment"> <option value="">--</option> <?php if (isset($_get["page"])) { $page = $_get["page"]; } else { $page=1; }; $start_from = ($page-1) * 20; $sql = "select * ".$settings["data_table"]." order comment asc limit $start_from, 20"; $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); while ($row = mysql_fetch_assoc($sql_result)) { echo "<option value='".$row["comment"]."'". ($row["comment"]==$_request["comment"] ? " selected" : "").">".$row["comment"]."</option>"; } ?> </select> <input type="submit" name="button" id="button" value="filter" /> </label> <a href="search.php"> reset</a> </form> <br /><br /> <table width="700" border="1" cellspacing="0" cellpadding="4"> <tr> <td width="90" bgcolor="#cccccc"><strong>date added</strong></td> <td width="95" bgcolor="#cccccc"><strong>track</strong></td> <td width="159" bgcolor="#cccccc"><strong>artist</strong></td> <td width="191" bgcolor="#cccccc"><strong>disk</strong></td> <td width="113" bgcolor="#cccccc"><strong>duet</strong></td> <td width="113" bgcolor="#cccccc"><strong>comment</strong></td> </tr> <?php // start query $sql = "select count * ".$settings["data_table"]."; if ($_request["string"]!='') { $sql .= " , (track '%".mysql_real_escape_string($_request["string"])."%' or artist '%".mysql_real_escape_string($_request["string"])."%')"; } if ($_request["disk"]!='') { $sql .= " , disk='".mysql_real_escape_string($_request["disk"])."'"; } if ($_request["comment"]!='') { $sql .= " , comment='".mysql_real_escape_string($_request["comment"])."'"; } if ($_request["clock"]!='') { $sql .= " , clock='".mysql_real_escape_string($_request["clock"])."'"; } if ($_request["duet"]!='') { $sql .= " , duet='".mysql_real_escape_string($_request["duet"])."'"; } $sql_result = mysql_query ($sql, $connection ) or die ('request "could not execute sql query" '.$sql); if (mysql_num_rows($sql_result)>0) { while ($row = mysql_fetch_assoc($sql_result)) { $total_records = $row[0]; $total_pages = ceil($total_records / 20); ($i=1; $i<=$total_pages; $i++) { echo "<a href='search.php?page=".$i."'>".$i."</a> "; }; ?> <tr> <td><?php echo $row["clock"]; ?></td> <td><?php echo $row["track"]; ?></td> <td><?php echo $row["artist"]; ?></td> <td><?php echo $row["disk"]; ?></td> <td><?php echo $row["duet"]; ?></td> <td><?php echo $row["comment"]; ?></td> </tr> <?php } } else { ?> <tr><td colspan="6">no results found.</td> <?php } ?> </table> any assistance appreciated thank in advance
Comments
Post a Comment