paginate this data

**ianhaney** · 02-14-2016, 03:47 PM

I got the pagination working if I just click submit to return all the rows but can't seem to get the date from and to working still, it returns no data and got a blank white page with no errors

Below is what I have currently

Code:

<?php 

$dbConn = mysqli_connect("localhost" , "", "", "") or die("Check connection parameters!");

?>

<!doctype html>
<html lang="en">
 <head>
  <meta charset="UTF-8">
  <meta name="Author" content="">
  <meta name="Keywords" content="">
  <meta name="Description" content="">
  <title>Paginate data</title>

<script type="text/javascript"> 
function stopRKey(evt) { 
  var evt = (evt) ? evt : ((event) ? event : null); 
  var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null); 
  if ((evt.keyCode == 13) && (node.type=="text"))  {return false;} 
}
document.onkeypress = stopRKey; 
</script>
  
  <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>

<?php

$data = [];

if (isset($_GET["rows"])) {        // If variable 'rows' is in the URI, then use it.
    $per_page = $_GET["rows"];
} else {
    $per_page = 10;
}

if (isset($_GET["d1"])):        // If variable 'pfx' is in the URI, then use it.
    $d1 = $_GET["d1"];        // Otherwise go to bottom of page and just display
                                // 'New Search'.
								
if (isset($_GET["d2"])):        // If variable 'pfx' is in the URI, then use it.
    $d2 = $_GET["d2"];        // Otherwise go to bottom of page and just display
                                // 'New Search'.

    echo '<h2>Paginate records from database</h2>';

if (isset($_GET["page"])) {        // If variable 'page' is in the URI, then use it,
    $page = $_GET["page"];        // as this is a recursive call.
} else {
    $page = 1;                    // Otherwise set to page zero.
}

if (isset($_GET["size"])) {        // if variable 'size' is in the URI, then use it.
    $size = $_GET["size"];
} else {
    $query = "SELECT id FROM purchased_software WHERE sales_month LIKE '%".$d1."%' OR sales_month LIKE '%".$d2."%'";
	
    $result = mysqli_query($dbConn, $query);
    if ($result) {
        $size = ceil((mysqli_num_rows($result))/$per_page);
    }
}

$start_from = ($page - 1) * $per_page;

$result = mysqli_query($dbConn, "SELECT * FROM purchased_software WHERE sales_month LIKE '%".$d1."%' OR '%".$d2."%'
 LIMIT $start_from, $per_page");
if ($result) {
    $data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}

?>

        <table class="view-repairs">
            <thead>
                <tr>
                    <th>Software ID</th>
                    <th>Customer PayPal Email</th>
                    <th>Ebay Username</th>
                    <th>Sales Date</th>
                    <th>Software Title</th>
                    <th>Quantity</th>
                    <th>Total Sale</th>
                    <th>Ebay Fees</th>
                    <th>PayPal Fees</th>
                    <th>Cost Price</th>
                    <th>Profit</th>
                    <th>Notes</th>
                    <th>Status</th>
                    <th>Sold By</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php
                echo '<p><strong>Page '. $page .' of '.$size.'.</strong></p>';
                foreach ($data as $row): ?>
                <tr>
                   <td><a href="view-specific-software-sale.php?id=<?= $row['id']; ?>"><?php echo $row['id']; ?></a></td>
                   <td><?php echo $row['customer_pp_email']; ?></td>
                   <td><?php echo $row['ebay_username']; ?></td>
                   <td><?php echo date("d/m/Y", strtotime($row['sales_month'])); ?></td>
                   <td><?php echo $row['software_title']; ?></td>
                   <td><?php echo $row['quantity']; ?></td>
                   <td><?php echo $row['total_sale']; ?></td>
                   <td><?php echo $row['ebay_fees']; ?></td>
                   <td><?php echo $row['paypal_fees']; ?></td>
                   <td><?php echo '£' . $row['software_cost']; ?></td>
                   <td><?php echo '£' . $row['profit']; ?></td>
                   <td><?php echo substr($row['notes'], 0, 25); ?></td>
                   <td><?php echo $row['status']; ?></td>
                   <td><?php echo $row['sold_by']; ?></td>
                   <td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>

        <?php
            $urld1 = urlencode($d1);
			$urld2 = urlencode($d2);
            if($size > 1) {
                echo '<p><br /><br />';
                for($i=1; $i<=$size; $i++) {
echo '<a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'&d2='.$urld2.'">' . ' - '
 .$i.'</a>';
                }
            }
            echo '<h4>New Search</h4>';

        else:        // End of 'if (isset($_GET["pfx"])):' from line 43.

            echo '<h2>New Search</h2>';
        endif;        // End of 'if (isset($_GET["pfx"])): else:' on line 43. 
    ?>
        
        <form action="search-info.php" method="get">
            <p>From: <input type="text" name="d1" value="" class="tcal" /></p>
            <p>To: <input type="text" name="d2" class="tcal" value="" /></p>
            <p>Rows per page: <input type="text" name="rows" value="10" maxlength="4" /></p>
            <p><input type="submit" /></p>
        </form>

</body>
</html>

**ianhaney** · 02-14-2016, 04:49 PM

I have the error log off my server now and think the reason for the blank white page is the error below that is displayed in the error log

PHP Parse error: syntax error, unexpected end of file in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 155: /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php

On line 155 is </html>

I am going to google the issue and see if I can fix it

**ianhaney** · 02-14-2016, 07:58 PM

I have a update, I got the page displaying now but just getting no results displayed

I tried to use the same d1 for both from and to date so wonder if that is the reason why no data is being returned so took the to field out and just left the from field but just having the from field left does not return any data when I search, I don't get what else to try, hope someone can help please

Code:

<?php 

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

$dbConn = mysqli_connect("localhost" , "", "", "") or die("Check connection parameters!");

?>

<!doctype html>
<html lang="en">
 <head>
  <meta charset="UTF-8">
  <meta name="Author" content="">
  <meta name="Keywords" content="">
  <meta name="Description" content="">
  <title>Paginate data</title>

<script type="text/javascript"> 
function stopRKey(evt) { 
  var evt = (evt) ? evt : ((event) ? event : null); 
  var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null); 
  if ((evt.keyCode == 13) && (node.type=="text"))  {return false;} 
}
document.onkeypress = stopRKey; 
</script>
  
  <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>

<?php

$data = [];

if (isset($_GET["rows"])) {        // If variable 'rows' is in the URI, then use it.
    $per_page = $_GET["rows"];
} else {
    $per_page = 10;
}

if (isset($_GET["d1"])):    // If variable 'pfx' is in the URI, then use it.
    $d1 = $_GET["d1"];        // Otherwise go to bottom of page and just display
							// 'New Search'.
							
    echo '<h2>Paginate records from database</h2>';

if (isset($_GET["page"])) {        // If variable 'page' is in the URI, then use it,
    $page = $_GET["page"];        // as this is a recursive call.
} else {
    $page = 1;                    // Otherwise set to page zero.
}

if (isset($_GET["size"])) {        // if variable 'size' is in the URI, then use it.
    $size = $_GET["size"];
} else {
    $query = "SELECT id FROM purchased_software WHERE sales_month LIKE '%".$d1."%'";
	
    $result = mysqli_query($dbConn, $query);
    if ($result) {
        $size = ceil((mysqli_num_rows($result))/$per_page);
    }
}

$start_from = ($page - 1) * $per_page;

$result = mysqli_query($dbConn, "SELECT * FROM purchased_software WHERE sales_month LIKE '%".$d1."%' 
LIMIT $start_from, $per_page");

if ($result) {
    $data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}

?>

        <table class="view-repairs">
            <thead>
                <tr>
                    <th>Software ID</th>
                    <th>Customer PayPal Email</th>
                    <th>Ebay Username</th>
                    <th>Sales Date</th>
                    <th>Software Title</th>
                    <th>Quantity</th>
                    <th>Total Sale</th>
                    <th>Ebay Fees</th>
                    <th>PayPal Fees</th>
                    <th>Cost Price</th>
                    <th>Profit</th>
                    <th>Notes</th>
                    <th>Status</th>
                    <th>Sold By</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php
                echo '<p><strong>Page '. $page .' of '.$size.'.</strong></p>';
                foreach ($data as $row): ?>
                <tr>
                   <td><a href="view-specific-software-sale.php?id=<?= $row['id']; ?>"><?php echo $row['id']; ?></a></td>
                   <td><?php echo $row['customer_pp_email']; ?></td>
                   <td><?php echo $row['ebay_username']; ?></td>
                   <td><?php echo date("d/m/Y", strtotime($row['sales_month'])); ?></td>
                   <td><?php echo $row['software_title']; ?></td>
                   <td><?php echo $row['quantity']; ?></td>
                   <td><?php echo $row['total_sale']; ?></td>
                   <td><?php echo $row['ebay_fees']; ?></td>
                   <td><?php echo $row['paypal_fees']; ?></td>
                   <td><?php echo '£' . $row['software_cost']; ?></td>
                   <td><?php echo '£' . $row['profit']; ?></td>
                   <td><?php echo substr($row['notes'], 0, 25); ?></td>
                   <td><?php echo $row['status']; ?></td>
                   <td><?php echo $row['sold_by']; ?></td>
                   <td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>

        <?php
            $urld1 = urlencode($d1);
            if($size > 1) {
                echo '<p><br /><br />';
                for($i=1; $i<=$size; $i++) {
echo '<a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'">' . ' - '
 .$i.'</a>';
                }
            }
            echo '<h4>New Search</h4>';

        else:        // End of 'if (isset($_GET["pfx"])):' from line 43.

            echo '<h2>New Search</h2>';
        endif;        // End of 'if (isset($_GET["pfx"])): else:' on line 43. 
    ?>
        
        <form action="search-info.php" method="get">
            <p>From: <input type="text" name="d1" class="tcal" value="" /></p>
            <p>To: <input type="text" name="d1" class="tcal" value="" /></p>
            <p>Rows per page: <input type="text" name="rows" value="10" maxlength="4" /></p>
            <p><input type="submit" /></p>
        </form>

</body>
</html>

I am not getting any errors now in the error log

I get all the data returned if I don't enter any date

**styxlawyer** · 02-14-2016, 10:18 PM

I think your problem might be something to do with this:

Originally Posted by ianhaney

If it helps, below is the purchased_software db table structure

the column called sales_month stores the date in the format of YYYY/MM/DD
.
.
.

In PHP your best friend for debugging is the "echo" statement which will send something to the browser that you can see. Just report all the values the page is using and you'll find the answer very quickly.

Firstly look at the URI in your browser after the first pass and see what is returned for "&size=". If you're seeing no data, my bet is you'll see "&size=0" which means that line 66 in your source is evaluating to zero. Add a few echo statements through the code to report what PHP is finding at various steps and you'll soon have it sorted. For example change this:

PHP Code:


if (isset($_GET["d1"])):    // If variable 'pfx' is in the URI, then use it.

    $d1 = $_GET["d1"];        // Otherwise go to bottom of page and just display

                            // 'New Search'.

                            

    echo '<h2>Paginate records from database</h2>';

to this:

PHP Code:


if (isset($_GET["d1"])):    // If variable 'pfx' is in the URI, then use it.

    $d1 = $_GET["d1"];        // Otherwise go to bottom of page and just display

                            // 'New Search'.

                            

    echo '<h2>Paginate records from database</h2>';

                            

    echo '<p style="color:red">Using month: '.$d1.'</p>';

and change this:

PHP Code:


        $size = ceil((mysqli_num_rows($result))/$per_page);

to this:

PHP Code:


        $xyzzy = mysqli_num_rows($result);

        echo '<p style="color:red">'.$xyzzy.' records found.</p>';

        $size = ceil($xyzzy/$per_page);

**ianhaney** · 02-14-2016, 10:34 PM

OK

I have altered the lines of script you mentioned and I choose in the from field 1-2-2016 and the to field 29-2-2016

and it returns the following

Paginate records from database

Using month: 29-02-2016

0 records found.

the url looks like the following

http://www.it-doneright.co.uk/admin/...2-2016&rows=10

**styxlawyer** · 02-15-2016, 10:35 AM

... and do you see what's wrong with that URI?

Code:

http://www.it-doneright.co.uk/admin/software-sales/search-info.php?d1=01-02-2016&d1=29-02-2016&rows=10

At a minimum you also need to have 'page' in there so that you know where you are.

**ianhaney** · 02-15-2016, 10:40 AM

Originally Posted by styxlawyer

... and do you see what's wrong with that URI?

Code:

http://www.it-doneright.co.uk/admin/software-sales/search-info.php?d1=01-02-2016&d1=29-02-2016&rows=10

Is it because in the url is d1=01-02-2016&d1=29-02-2016

I am guessing it can't be the same, so it should be like the following

d1=01-02-2016&d2=29-02-2016

That right?

**ianhaney** · 02-15-2016, 10:48 AM

I know I have done it wrong as got a blank white page again

I added the following lines in

Code:

if (isset($_GET["d1"])):    
    $d1 = $_GET["d1"];
	
//I ADDED THIS LINE IN
	if (isset($_GET["d2"])):    
    $d2 = $_GET["d2"];

I amended this line below to include the $d2

Code:

echo '<p style="color:red">Using month: '.$d1.$d2.'</p>';

I amended the line below to include $d2

Code:

$query = "SELECT id FROM purchased_software WHERE sales_month LIKE '%".$d1."%' OR '%".$d2."%'";

I amended the line below to include the $d2

Code:

$result = mysqli_query($dbConn, "SELECT * FROM purchased_software WHERE sales_month LIKE '%".$d1."%' OR '%".$d2."%' LIMIT $start_from, $per_page");

I added the second line in to include the $d2

Code:

$urld1 = urlencode($d1);
$urld2 = urlencode($d2); //I ADDED THIS LINE IN

I amended the line below to include $urld2

Code:

echo '<a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'&d2='.$urld2.'">' . ' - '
 .$i.'</a>';

My form now looks like the following

Code:

<form action="search-info.php" method="get">
            <p>From: <input type="text" name="d1" class="tcal" value="" /></p>
            <p>To: <input type="text" name="d2" class="tcal" value="" /></p>
            <p>Rows per page: <input type="text" name="rows" value="10" maxlength="4" /></p>
            <p><input type="submit" /></p>
        </form>

**ianhaney** · 02-15-2016, 11:03 AM

I found the issue, it was where I had the following

Code:

if (isset($_GET["d1"])):    
    $d1 = $_GET["d1"];
	
	if (isset($_GET["d2"])):    
    $d2 = $_GET["d2"];

I changed it to the following

Code:

if (isset($_GET['d1'], $_GET['d2'])):

I now have data returned and is being paginated but now I got the following issues where I took out the $d1 and $d2 variables

Notice: Undefined variable: d1 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 51 Notice: Undefined variable: d2 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 51

Using month:
Notice: Undefined variable: d1 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 62 Notice: Undefined variable: d2 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 62

102 records found.
Notice: Undefined variable: d1 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 74 Notice: Undefined variable: d2 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 74

I am going to investigate and see if I can fix it myself

**ianhaney** · 02-15-2016, 11:42 AM

Sorry am stuck

I tried the following

Code:

if (isset($_GET['d1'], $_GET['d2'])):
  $d1 = $_GET['d1'];
  $d2 = $_GET['d2'];

instead of below

Code:

if (isset($_GET['d1'], $_GET['d2'])):

all I got with that is the following

Using month: 01-02-2016-29-02-2016

0 records found.

Page 1 of 0.

The URL looks like the following

http://it-doneright.co.uk/admin/soft...2-2016&rows=10