Jump to content

from to date search form php mysqli


ianhaney

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 by ianhaney
Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.