paginate search results

**ianhaney** · 02-09-2016, 06:11 PM

I took everything out of it apart from the pagination coding, logo and menu and still got the same error, below is the code

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 82

Code:

<?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, cost_price, 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> &nbsp; &nbsp; ';
		// 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> &nbsp; ';
			}
	    }
    }
	// Render the target page number, but without it being a link
	$paginationCtrls .= ''.$pagenum.' &nbsp; ';
	// 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> &nbsp; ';
		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 .= ' &nbsp; &nbsp; <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 = $row["sales_month"];
	$software_title = $row["software_title"];
	$datemade = strftime("%b %d, %Y", strtotime($datemade));
	$list .= '<p>' . $customer_pp_email . $sales_month . $software_title . $datemade.'</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>

**ianhaney** · 02-09-2016, 06:24 PM

Right am slowly winning

I have got the pagination all working and paginating the results, just need to put the search form back in to be able to search for data between the two dates, I will try that now and post a update for that section of code

**ianhaney** · 02-09-2016, 06:30 PM

It def does not like the search form and the dates, is it because that is in PDO and the rest is mysqli

I get the error message again like before

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 100

below is what I have so far

Code:

<?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;

$data = [];
if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    if ($d1 && $d2) {
        $start_date = date('Y-m-d', $d1);
        $end_date = date('Y-m-d', $d2);
		$result = $con->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id");
		
        $result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
        }
    }
}

// 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 WHERE sales_month BETWEEN :a AND :b 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> &nbsp; &nbsp; ';
		// 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> &nbsp; ';
			}
	    }
    }
	// Render the target page number, but without it being a link
	$paginationCtrls .= ''.$pagenum.' &nbsp; ';
	// 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> &nbsp; ';
		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 .= ' &nbsp; &nbsp; <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" />
        <link rel="stylesheet" type="text/css" href="css/tcal.css" />
        <script type="text/javascript" src="js/tcal.js"></script>
    </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>
        
        <form action="search-data.php" method="get">
            From : <input type="text" name="d1" class="tcal" value="" />
            To: <input type="text" name="d2" class="tcal" value="" />
            <input type="submit" value="Search">
        </form>
        
        <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>

**ianhaney** · 02-09-2016, 06:41 PM

Is there a way to use mysqli rather than PDO for the search form dates as looking on Google but can't seem to find anything yet

**ianhaney** · 02-09-2016, 09:41 PM

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

Code:

$a=$_POST['from'];
$b=$_POST['to'];

I added the following to the query

Code:

WHERE sales_month BETWEEN '$a' AND '$b'

I added the form in as well which is below

Code:

<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** · 02-09-2016, 10:16 PM

I solved the undefined index errors but amending the two lines to the following

Code:

$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?

Thread: paginate search results

Thread Tools

Search Thread

Similar Threads

customising a maps with search results

Highlighting of Search Words from Search Engine Results

PHP code to retrieve search results

search criteria appear after search results appear, to the search form for new search

Split Search results into columns

Bookmarks

Bookmarks

Posting Permissions