from to date search form php mysqli

[ianhaney]

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

[Barand]

Add a WHERE clause to your queries

[ianhaney]

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

[ianhaney]

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?

[Barand]

What format are

 

a ) sales_month?

b ) from and to dates?

[ianhaney]

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?

[Barand]

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?

[ianhaney]

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

[ianhaney]

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

[Barand]

Sorry, Ian. Life's too short for this. Good luck.

[mac_gyver]

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