Pagination not working PHP, mysql

Hello,

I need to add a pagination to the results of a search engine. When I load the page without start  a search, it shows up like this.

paginationBut when I make the search, the results are not paginated at all.

Please give me a hand to solve this problem. Thanks in advance

That´s the extract of the code including the query and pagination.

$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE (".$name_clause.") OR (".$description_clause.") OR (".$table_clause.")";
		
		
		
            break;

        default:
            trigger_error('SCRIPT UNDER ATTACK, RUN LIKE HELL', E_USER_ERROR);
    }
  // DEBUGGING INFORMATION TO SHOW THE REQUEST DATA AND THE RESULTING QUERY
  
 
 $res = $mysqli->query($sql);


// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;



	echo "<TABLE cellspacing=\"3\" border=\"1\" cellpadding=\"8\">\n";
	
	echo "</TR>\n";



while ($row = $res->fetch_array()) {
   //get the publisher name
 
     $organizerSql = $mysqli->query("SELECT nazwa FROM baza_obiektow_inne WHERE id=" . $row['organizer_id'] . " LIMIT 1");
   $organizer = mysqli_fetch_assoc($organizerSql);
   $pretty_date = date('F Y', strtotime($row['publication_date']));
   echo "<tr>\n";
   $simbolos_reemplazar = array (" ","%","/");
   printf("<td><a href='http://embs-group.com/%s,%s'>%s</a></td>", $row['id'], str_replace( $simbolos_reemplazar, "_", $row['name']), $row['name']);
   printf("<td>%s</td>", $organizer['nazwa']);
   printf("<td>%s</td>", $row['no_pages']);
 printf("<td>%s</td>", $pretty_date);
   printf("<td>%s</td>", $row['price']);
   printf("<td>%s</td>", $row['currency']);
   
	
  
   echo "</tr>\n";

}
// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
//echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
//echo PHP_EOL;
//while ($row = $res->fetch_array())
//{
    // ROW BY ROW PROCESSING IS DONE HERE
  
   
//    echo PHP_EOL;
//}
echo PHP_EOL;

  

}

// START OF PAGINATION

 // WE NEED TO KNOW HOW MUCH RECORDS THERE ARE
    $query = "select count(*) as recordcount from $res";
    $result = $mysqli ->query($query);
    $row_pg = mysqli_fetch_array($result);
    
    // NUMBER OF ALL ROWS
    $numrows2 = $row_pg['recordcount'];
    
    // HERE WE SET THE NUMER OF ITEMS PER ONE PAGE
    $ipp = 50;
    
    // NUMBER OF ALL PAGES
    // WE USE CEIL SO IF THE RECORD COUNT
    // IS - FOR EXAMPLE - 11 WE GET 3 PAGES
    $pagecount = ceil($numrows2 / $ipp);
    echo"We have ".$pagecount." pages<br />";
    
    
    // THE CURRENT PAGE NUMBER FROM GET VARIABLE
    // IF NOT SET - IT'S FIRST 
    $page = 0;
	if(empty($_GET[page]) || !isset($_GET[page])) $page = 1;
	else $page = $_GET[page];	
	
	// WHERE ARE WE ?
    echo"<br /><br />current page: $page<br /><br />";	
	
	// STARTRECORD IS USED FOR THE MYSQL QUERY
	// SO WE KNOW WHERE WE CAN START FROM
	// LIMIT WITH IPP (ITEMS PER PAGE)
	if(!empty($page)) $startrecord = ($page-1)*$ipp;
	else $startrecord = 0; 		
	
	$query = "select * from $res limit $startrecord,$ipp";
	$result = $mysqli->query($query);
	while($row_pg = mysql_fetch_array($result)){
        
	}
	
	// THIS IS THE VALUE FOR THE NUMBER OF
	// NEXT AND PREVIOUS PAGES DISPLAYED IN
	// THE DYNAMIC PANEL
 	$margin = 7;
	
	
	// THE DYNAMIC PANEL
    echo"<br /><div>";
    echo"Page ";
            
			if($page == 1);
			else
			{
				$prev = $page - 1;
				echo" <a href=\"index.php?page=1\">first</a> ";
				echo" <a href=\"index.php?page=$prev\">prev</a> ";
			}

			if($page-$margin <= 0) $start = 1; else $start = $page - $margin;
			if($page+$margin > $pagecount) $last = $pagecount; else $last = $page + $margin;

			for($i=$start;$i<=$last;$i++){
                // SHOW THE CURRENT PAGE WITH DIFFERENT FORMATTING
                if($i == $page)
                    echo" <a href=\"index.php?page=$i\" style=\"font-weight: bold;\">$i</a> ";
                else
                    echo" <a href=\"index.php?page=$i\">$i</a> ";
			}


			if($page == $pagecount);
			else
			{
				$next = $page + 1;
				echo" <a href=\"index.php?page=$next\">next</a> ";
				echo" <a href=\"index.php?page=$pagecount\">last</a> ";
			}
    

Open in new window

Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
The canonical article on PHP pagination is from SitePoint and even though it's rather old, it teaches an object-oriented solution that still works perfectly today.
http://www.sitepoint.com/perfect-php-pagination/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Klaus AndersenAuthor Commented:
Thanks @Ray. Despite small things that I´m currently fixing, the search engine is working well!. Now the next step is to add the pagination and then give it a style.

As I see in the screenshot of the pagination you suggest, it creates a link to every group of results (page 1, page 2), but I wonder what will happen when I will have queries with 4,000 results.

Do you know if I can modify in the way :   <<     < 1 2 3 4 5 6 7 8 9 10 >  >>?

Thanks!
0
Ray PaseurCommented:
I'm sure you can get there, but it will take some design thought.  Here's what I am wondering about... What if someone is at #105 and they want to go to #3500?  How do they get there?  You can't list 3,500 result links on the bottom of the page.  Maybe one way would be to have a form input that lets them put in the page number.  You could have this in addition to the forward and backward pointers.

Of course, nobody is going to read all 4,000 pages of results!
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulfreelancerCommented:
How many times have you used a search engine and visited the 3678th result?

Typically if a very large volume of results are returned, either the first few are used*, or the search is refined to locate a smaller set.

*sorting options may assist here.

Attempting to make 'to page' navigation too complex could have both negative impacts on usability and performance.

:( I can recall one site I saw that tried to link to each page, that navigation was just ridiculously cumbersome and was horrible to look at.
0
Ray PaseurCommented:
+1 for @PortletPaul.  It's not a reasonable design.
0
Klaus AndersenAuthor Commented:
Thanks guys,


I totally agree with what you said, but seems you misunderstood me.

A system that will give 4000 page results to browse is something definitely not good.

When I spoke about 4,000 results, I meant about  each row in the results table

It means, if I set up the pagination to show 100 rows per page, I will have a pagination of 40 pages,  and this will be a rare case, because a normal filtered search should provide between 1 - 200 rows, 1 or 2 pages of results
0
PortletPaulfreelancerCommented:
have you used searches here at E-E?
It will allow you to check out 100 lines or 200 lines per page
in both a 'list view' or 'detail view'

To simulate the fonts sizes you might use you can try Ctrl +   or Ctrl -  in your browser to zoom out/in.

Then consider finding the 3678th item (not page)
EE Adv. Search
0
Klaus AndersenAuthor Commented:
@Portletpaul The search engine and pagination of EE works great, but I'm actually in a stage of the process where my search is also working fine and I just need to add a simple pagination. I also forgot to mention you that I'm new in PHP, mysql,  so if I start to create such system will take me much longer.

Thanks for the info! For the future will be a great reference to take.

Cheers,

PJ
0
PortletPaulfreelancerCommented:
nb: I was not suggesting you emulate the EE search facility

I was just trying to give you a simple sample of what 100 lines per page was like to use.

If you believe that is workable for you that is naturally your decision to make.

best of luck with the project - we are here to guide/help.

Cheers, Paul
0
Klaus AndersenAuthor Commented:
@PortletPaul, Thanks for the support. Is really great to see how talented are the experts from EE. I'm glad to see how you have the disposal to give a hand and be a good guide for us, the people is starting.

I keep working on my project!

If you have the opportinity, please check my question regarding a catchable error.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28250384.html
I'm trying with a new pagination system and I have this problem

Cheers,

PJ
0
PortletPaulfreelancerCommented:
My contribution here is mostly UI/usability related - others more talented than I in the art of catchable errors will no doubt attend to that question.

Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.