Deaddog Posted February 17, 2013 Share Posted February 17, 2013 Hi,I've modified script 10.5 by adding a search form at the top. The search correctly returns a paginated table of results with page numbers and a next link. However, there are some problems that is beyond my knowledge of php. The first is that I don't know how to prevent the undefined index error caused by the blank form fields when the next link or a page number link is clicked. In other words, trying to view the paged results creates an undefined index error. The second thing is that clicking the sort link returns all the database records, not just the search results. The code is posted. Any help would be appreciated. <?php $page_title = 'Search'; include ('includes/header.html'); require("mysqli_connect_skn.php"); ?> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="GET"> <h1>Search the Database</h1> <h3>Return (hopefully) Paginated search results with sortable columns</h3> <p>Form Field One: <input name="one" type="text" size="6" maxlength="6" value="<?php if(isset($_POST['one'])) echo $_POST['one']; ?>" /> </p> <p>Form Field Two: <input name="five" type="text" size="5" maxlength="5" value="<?php if(isset($_POST['five'])) echo $_POST['five']; ?>" /> </p> <p>Form Field Three: <input name="freetext" type="text" size="40" maxlength="40" value="<?php if(isset($_POST['freetext'])) echo $_POST['freetext']; ?>" /> </p> <p> <input type="submit" value="Search"> </p> </form> <?php #Sript 10.5 - view_users.php //Add number of records to show per page as a variable. $one = mysqli_real_escape_string($dbc, trim($_GET['one'])); $five = mysqli_real_escape_string($dbc, trim($_GET['five'])); $freetext = mysqli_real_escape_string($dbc, trim($_GET['freetext'])); $display = 10; //Determine the total number pages. if (isset($_GET['p']) && is_numeric($_GET['p'])) { // if the number of records is already determined. $pages = $_GET['p']; } else { // Count the number of records $q = "SELECT COUNT(acr_id) FROM acr WHERE one LIKE \"%$one%\" AND five LIKE \"$five%\" AND (six LIKE \"%$freetext%\" OR two LIKE \"%$freetext%\" OR four LIKE \"%$freetext%\" OR seven LIKE \"%$freetext%\" OR nine LIKE \"%$freetext%\" OR eight LIKE \"%$freetext%\")"; $r = mysqli_query($dbc, $q); $row = mysqli_fetch_array($r, MYSQLI_NUM); $records = $row[0]; //Calculate the number of pages if ($records > $display){ $pages = ceil($records/$display); //ceil — Round fractions up } else { $pages = 1; } } //End of p IF. // Determine where to start returning results. if (isset($_GET['s']) && is_numeric($_GET['s'])) { $start = $_GET['s']; } else{ $start = 0; } //Determine the default sort. $sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'three'; //Determine the sorting order. $one_sort = 'one'; $seq_sort = 'two'; $date_sort = 'three'; $four_sort = 'four'; $five_sort = 'five'; $comp_sort = 'six'; $seven_sort = 'seven'; $eight_sort = 'eight'; switch ($sort) { case 'one': $order_by = 'one ASC'; $one_sort = 'rone'; break; case 'rone': $order_by = 'one DESC'; break; case 'two': $order_by = 'two ASC'; $seq_sort = 'rtwo'; break; case 'rtwo': $order_by = 'two DESC'; break; case 'three': $order_by = 'three ASC'; $date_sort = 'rthree'; break; case 'rthree': $order_by = 'three DESC'; break; case 'four': $order_by = 'four ASC'; $four_sort = 'rfour'; break; case 'rfour': $order_by = 'four DESC'; break; case 'five': $order_by = 'five ASC'; $five_sort = 'rfive'; break; case 'rfive': $order_by = 'five DESC'; break; case 'six': $order_by = 'six ASC'; $comp_sort = 'rsix'; break; case 'rsix': $order_by = 'six DESC'; break; case 'seven': $order_by = 'seven ASC'; $seven_sort = 'rseven'; break; case 'rseven': $order_by = 'seven DESC'; break; case 'eight': $order_by = 'eight ASC'; $eight_sort = 'reight'; break; case 'reight': $order_by = 'eight DESC'; break; } //Define the query: $q = "SELECT * FROM acr WHERE one LIKE \"%$one%\" AND five LIKE \"$five%\" AND (six LIKE \"%$freetext%\" OR two LIKE \"%$freetext%\" OR four LIKE \"%$freetext%\" OR seven LIKE \"%$freetext%\" OR nine LIKE \"%$freetext%\" OR eight LIKE \"%$freetext%\") ORDER BY $order_by LIMIT $start, $display"; //Run the query $r = @mysqli_query($dbc, $q) or die("Error: ".mysqli_error($dbc)); //Start the table header echo '<table align="center" cellspacing="15" cellpadding="2" width="100%"> <tr>"There were ' . $records . ' hits for your search '.$q . '. " <tr> <td align="left"><b>Details</b></td> <td align="left"><b><a href="data-search.php?sort='. $one_sort. '">One</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $two_sort .'">Two</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $three_sort.'">Three</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $four_sort .'">Four</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $five_sort .'">Five</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $six_sort .'">Six</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $seven_sort .'">Seven</a></b></td> <td align="left"><b><a href="data-search.php?sort='. $eight_sort .'">Eight</a></b></td> </tr> '; //Fetch and print all the records. $bg = '#eeeeee'; while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){ $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); echo ' <tr bgcolor="' .$bg . '"> <td valign ="top" align="left"><a href="edit.php?id=' . $row['acr_id'] . '">Details</a></td> <td valign ="top" align="left">'.$row['one'] .'</td> <td valign ="top" align="left">'.$row['two'] .'</td> <td valign ="top" align="left">'.$row['three'] .'</td> <td valign ="top" align="left">'.$row['four'] .'</td> <td valign ="top" align="left">'.$row['five'] .'</td> <td valign ="top" align="left">'.$row['six'] .'</td> <td valign ="top" align="left">'.$row['seven'] .'</td> <td valign ="top" align="left">'.$row['eight'] .'</td> </tr> '; } //End of WHILE loop. echo '</table>'; mysqli_free_result ($r); mysqli_close($dbc); //Make the links to other pages, if necessary. if ($pages > 1) { echo '<br /><p>'; $current_page = ($start/$display) + 1; // If it's not the first page, make a Previous buttion: if ($current_page != 1) { echo '<a href="'.$_SERVER['PHP_SELF'].'?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '">Previous</a>'; } // Make all the numbered pages. for ($i = 1; $i <= $pages; $i++) { if ($i != $current_page) { echo '<a href="'.$_SERVER['PHP_SELF'].'?s=' . (($display * ($i - 1))) .'&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a>'; } else { echo $i . ' '; } } // End of FOR loop. //If it's not the last page, make a Next buttion: if ($current_page != $pages) { echo '<a href="'.$_SERVER['PHP_SELF'].'?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">Next</a>'; } echo '</p>'; // Close the paragraph } // End the page links section include ('includes/footer.html'); ?> Link to comment Share on other sites More sharing options...
Larry Posted February 18, 2013 Share Posted February 18, 2013 You need to pass all the search terms in the links so that subsequent pages of results have access to them. Link to comment Share on other sites More sharing options...
Deaddog Posted February 18, 2013 Author Share Posted February 18, 2013 Hi Larry, Thanks for the reply. Are there any examples about how to do this in the fourth edition? I think I understand what needs to be done, but lacking the php skills to pull it off. Link to comment Share on other sites More sharing options...
Larry Posted February 18, 2013 Share Posted February 18, 2013 I don't think I did put an example of this, but it's just a matter of appending the search terms to your links the same way you have with the s, p, and sort values. Link to comment Share on other sites More sharing options...
Deaddog Posted February 26, 2013 Author Share Posted February 26, 2013 Thanks for the kick in the right direction. That worked! Here is the code, in case it may help someone else: //Define the query: NOTE: acr is the table name. One, five, and freetext are form input names. $q = "SELECT * FROM acr WHERE one LIKE \"%$one%\" AND five LIKE \"$five%\" AND (six LIKE \"%$freetext%\" OR two LIKE \"%$freetext%\" OR four LIKE \"%$freetext%\" OR seven LIKE \"%$freetext%\" OR nine LIKE \"%$freetext%\" OR eight LIKE \"%$freetext%\") ORDER BY $order_by LIMIT $start, $display"; //Run the query $r = @mysqli_query($dbc, $q) or die("Error: ".mysqli_error($dbc)); //Start the table header echo '<table align="center" cellspacing="15" cellpadding="2" width="100%"> <tr>"There were ' . $records . ' hits for your search '.$q . '. " <tr> <td align="left"><b>Details</b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $one_sort. '">One</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $two_sort .'">Two</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $three_sort.'">Three</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $four_sort .'">Four</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $five_sort .'">Five</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $six_sort .'">Six</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $seven_sort .'">Seven</a></b></td> <td align="left"><b><a href="data-search.php?one='.$one.' &five='.$five.' &freetext='.$freetext.'&sort='. $eight_sort .'">Eight</a></b></td> </tr>'; //Fetch and print all the records. $bg = '#eeeeee'; while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){ $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); echo ' <tr bgcolor="' .$bg . '"> <td valign ="top" align="left"><a href="edit.php?id=' . $row['acr_id'] . '">Details</a></td> <td valign ="top" align="left">'.$row['one'] .'</td> <td valign ="top" align="left">'.$row['two'] .'</td> <td valign ="top" align="left">'.$row['three'] .'</td> <td valign ="top" align="left">'.$row['four'] .'</td> <td valign ="top" align="left">'.$row['five'] .'</td> <td valign ="top" align="left">'.$row['six'] .'</td> <td valign ="top" align="left">'.$row['seven'] .'</td> <td valign ="top" align="left">'.$row['eight'] .'</td> </tr> '; } //End of WHILE loop. echo '</table>'; Link to comment Share on other sites More sharing options...
Larry Posted February 27, 2013 Share Posted February 27, 2013 Excellent. Glad it's working and thanks for sharing your solution. Link to comment Share on other sites More sharing options...
Recommended Posts