ianhaney Posted February 9, 2016 Share Posted February 9, 2016 Hi I have got search results that display all records from a db table and they are paginated so shows 5 records per page but I need to add a form that allows me to select a from date and a to date and then retrieves the records that are between the from and to date, is that possible to do in php html and mysqli as unsure how to do it please if anyone can help me, below is the code I have so far for the pagination that is working <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); include_once("mysqli_connection.php"); // This first query is just to get the total count of rows $sql = "SELECT COUNT(id) FROM purchased_software"; $r = mysqli_query($con,$sql) or die("Error: ".mysqli_error($con)); $query = mysqli_query($con, $sql); $row = mysqli_fetch_row($query); // Here we have the total row count $rows = $row[0]; // This is the number of results we want displayed per page $page_rows = 5; // This tells us the page number of our last page $last = ceil($rows/$page_rows); // This makes sure $last cannot be less than 1 if($last < 1){ $last = 1; } // Establish the $pagenum variable $pagenum = 1; // Get pagenum from URL vars if it is present, else it is = 1 if(isset($_GET['pn'])){ $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']); } // This makes sure the page number isn't below 1, or more than our $last page if ($pagenum < 1) { $pagenum = 1; } else if ($pagenum > $last) { $pagenum = $last; } // This sets the range of rows to query for the chosen $pagenum $limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows; // This is your query again, it is for grabbing just one page worth of rows by applying $limit $sql = "SELECT id, customer_pp_email, sales_month, software_title, quantity, total_sale, ebay_fees, paypal_fees, software_cost, profit, notes, status, sold_by FROM purchased_software ORDER BY id DESC $limit"; $query = mysqli_query($con, $sql); // This shows the user what page they are on, and the total number of pages $textline1 = "Sales (<b>$rows</b>)"; $textline2 = "Page <b>$pagenum</b> of <b>$last</b>"; // Establish the $paginationCtrls variable $paginationCtrls = ''; // If there is more than 1 page worth of results if($last != 1){ /* First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page. */ if ($pagenum > 1) { $previous = $pagenum - 1; $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> '; // Render clickable number links that should appear on the left of the target page number for($i = $pagenum-4; $i < $pagenum; $i++){ if($i > 0){ $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> '; } } } // Render the target page number, but without it being a link $paginationCtrls .= ''.$pagenum.' '; // Render clickable number links that should appear on the right of the target page number for($i = $pagenum+1; $i <= $last; $i++){ $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> '; if($i >= $pagenum+4){ break; } } // This does the same as above, only checking if we are on the last page, and then generating the "Next" if ($pagenum != $last) { $next = $pagenum + 1; $paginationCtrls .= ' <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> '; } } $list = ''; while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = $row["id"]; $customer_pp_email = $row["customer_pp_email"]; $sales_month = strftime("%b %d, %Y", strtotime($sales_month)); $software_title = $row["software_title"]; $quantity = $row["quantity"]; $total_sale = $row["total_sale"]; $ebay_fees = $row["ebay_fees"]; $paypal_fees = $row["paypal_fees"]; $software_cost = $row["software_cost"]; $profit = $row["profit"]; $notes = $row["notes"]; $status = $row["status"]; $sold_by = $row["sold_by"]; $list .= '<p>' . $customer_pp_email . $sales_month . $software_title . $quantity . $total_sale . $ebay_fees . $paypal_fees . $software_cost . $profit . $notes . $status . $sold_by.'</p>'; } // Close your database connection mysqli_close($con); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>Search Data Between Two Dates</title> <link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" /> </head> <body> <div id="logo"> <img src="images/logo/it-done-right.jpg" alt="" title=""> </div> <br><br> <?php include("nav-menu.php"); ?> <br><br> <div> <h2><?php echo $textline1; ?> Paged</h2> <p><?php echo $textline2; ?></p> <p><?php echo $list; ?></p> <div id="pagination_controls"><?php echo $paginationCtrls; ?></div> </div> </body> </html> Thank you in advance Ian Quote Link to comment Share on other sites More sharing options...
Barand Posted February 9, 2016 Share Posted February 9, 2016 Add a WHERE clause to your queries Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 9, 2016 Author Share Posted February 9, 2016 I have made these amendments but know I have got it wrong cause of the error messages but just seeing if am close or how to resolve the error messages, once working I will sort out the sql injection I added in the following above the sql query $a=$_POST['from']; $b=$_POST['to']; I added the following to the query WHERE sales_month BETWEEN '$a' AND '$b' I added the form in as well which is below <form action="search-data.php" method="post"> From : <input type="text" name="from" class="tcal" /> To: <input type="text" name="to" class="tcal" /> <input name="" type="submit" value="Search"> </form> With them amendments, I get the followign error messages Notice: Undefined index: from in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 42 Notice: Undefined index: to in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 43 Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 9, 2016 Author Share Posted February 9, 2016 I solved the undefined index errors but amending the two lines to the following $a = (isset($_POST['from']) ? $_POST['from'] : null); $b = (isset($_POST['to']) ? $_POST['to'] : null); but that does not return any results when I search for two different dates using the form? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 9, 2016 Share Posted February 9, 2016 What format are a ) sales_month? b ) from and to dates? Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 10, 2016 Author Share Posted February 10, 2016 Hi Sorry for my late reply, do you mean the format in the db table, if so sales_month in the db table is date type and I don't have a from and to in my db table? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 10, 2016 Share Posted February 10, 2016 I am well aware that the from and to dates are passed as search parameters and are are not in the database but that doesn't mean they do not have a format. If you echo $_POST['from'] and $_POST['to'], what do they look like? Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 10, 2016 Author Share Posted February 10, 2016 Ahh ok sorry, have I done this right? echo $_POST['from']; echo $_POST['to']; on the page I get the errors Notice: Undefined index: from in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 49 Notice: Undefined index: to in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 50 line 49 and 50 is the two echo lines Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 10, 2016 Author Share Posted February 10, 2016 (edited) Not sure if I did this right as thought I did it wrong above so did the following var_dump($_POST); on the page that outputs the following array(0) { } If I do the following print_r($_POST); That outputs the following on the page Array ( ) Is that what you was looking for regarding the format? Edited February 10, 2016 by ianhaney Quote Link to comment Share on other sites More sharing options...
Barand Posted February 10, 2016 Share Posted February 10, 2016 Sorry, Ian. Life's too short for this. Good luck. 1 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 10, 2016 Share Posted February 10, 2016 (edited) i'm wondering why the code in this thread, using mysqli, threw a way the code you posted in your last thread on this forum, using pdo, that apparent had a working date search - http://forums.phpfreaks.com/topic/300742-paginate-search-results/ especially since you have now posted that previous code, less the pagination logic, on at least one other help forum, expecting someone to spoon-feed you with the information you need to paginate the results. programming requires that you learn the meaning of what you are doing so that you can write code that brings together different concepts. all you are doing is trying to smash together pieces that don't even work together. by throwing away code and starting over, sometimes in a single thread, you are also throwing away the help you have gotten, because people are not going to keep reading randomly changing code from you to try and figure out what you are currently doing. if you want help, stick with one set of code and FIX the problems in it (using PDO is your best choice, since you need to use a prepared query to get the external data securely into the query). don't keep starting over. it will take you forever to accomplish anything. Edited February 10, 2016 by mac_gyver 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.