php pagination and search

Question

i have a while loop with pagination i have i idea how to make a search just don't know how to insert it here's my code the pagination and output commands are working just don't know how to put the code for search and it's messing my head.

//Count the total number of row in your table*/
$count_query   = mysql_query("SELECT COUNT(personid) AS numrows FROM persons");
$row     = mysql_fetch_array($count_query);
$numrows = $row['numrows'];
$total_pages = ceil($numrows/$per_page);
$reload = 'index.php';
//main query to fetch the data 
$query = mysql_query("SELECT * FROM persons ORDER by RAND() LIMIT $offset,$per_page");
//loop through fetched data
while($result = mysql_fetch_array($query)){
$id = $result['PersonID'];
echo "<div class= content > ";
echo"<img height=100 width=100 src='upload/". $result['Image'] ."'/>";
echo "<font color='black'>". $result['FirstName']. "</font></br>";
echo "</div>";

so as i do trial and error i think the part i got error is the row here's my whole code

> <?php include_once('includes/dbConnect.php');
> 
> 
> ?>
> 
> <?php
> 
> // now you can display the results returned. But first we will display
> the search form on the top of the page
> 
> $searchText = $_POST["q"];
> 
> 
> 
> 
> 
> $action = (isset($_REQUEST['action'])&& $_REQUEST['action']
> !=NULL)?$_REQUEST['action']:'';
> 
> if($action == 'ajax'){
> 
>   include 'pagination.php'; //include pagination file
> 
> 
>   //pagination variables  $page = (isset($_REQUEST['page']) &&
> !empty($_REQUEST['page']))?$_REQUEST['page']:1;   $per_page = 5; //how
> much records you want to show     $adjacents  = 5; //gap between pages
> after number of adjacents     $offset = ($page - 1) * $per_page;
> 
>   //Count the total number of row in your table*/     $count_query   =
> mysql_query("SELECT COUNT(personid) AS numrows FROM persons");    $row  
> = mysql_fetch_array($count_query);    $numrows = $row['numrows'];     $total_pages = ceil($numrows/$per_page);    $reload = 'index.php';
> 
>                   //search
>         // basic SQL-injection protection
> 
>         $searchText = $_REQUEST["q"];     //main query to fetch the data
>         // query with simple search criteria $query = mysql_query("SELECT * FROM persons WHERE FirstName LIKE '%"
>            . $searchText . "%' ORDER by RAND() LIMIT $offset,$per_page");
> 
>   //loop through fetched data
> 
> 
>         while($result = mysql_fetch_array($query)){
>         $id = $result['PersonID'];
> 
> 
>                                       echo "<div class= content > ";
> 
>                                       echo"<img height=100 width=100 src='upload/". $result['Image'] ."'/>";
>                                       echo "<font color='black'>". $result['FirstName']. "</font></br>";
> 
> 
> 
>                                       echo "</div>";
> 
> 
> } echo paginate($reload, $page, $total_pages, $adjacents); } else{ ?>
> 
> 
> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Simple Ajax
> Pagination With PHP And MySql</title> <script type="text/javascript"
> src="jquery-1.5.2.min.js"></script> <link media="screen"
> href="style.css" type="text/css" rel="stylesheet"> <script
> type="text/javascript">   $(document).ready(function(){       load(1);    });
> 
>   function load(page){        $("#loader").fadeIn('slow');        $.ajax({
>           url:'index.php?action=ajax&page='+page,             success:function(data){
>               $(".outer_div").html(data).fadeIn('slow');
>               $("#loader").fadeOut('slow');           }       })
> 
>         }
> 
> </script>
> 
> </head> <body>
>  
> 
> 
> <div id="loader"><img src="loader.gif"></div>
> 
> 
> 
> <div class="outer_div"></div>
> 
>     <div class="content"><form action='' method='POST'> <input type='text' name='q' /> <INPUT TYPE="button" onClick="history.go(0)"
> VALUE="Refresh"/> </p> </form></div> </body> </html> <?php
> 
> }?>

this is the output i want
1: https://i.stack.imgur.com/l8MMA.png
2: https://i.stack.imgur.com/p47UI.png

personID @MichaelPerrenoud i need to show the same pagination type i just don't know where to put the query . list of members or report the pagination gives automatic list that is = to 5 records i want to change it when a user give a search command that relates on first name and last name :D — Ermel Justin Pablo Lopez, Jul 25 '13 at 15:35

Mike Perrenoud · Answer 1 · 2013-07-26T10:22:30.770

1

So one approach might be to explode the string into tokens and then place those into the query as like commands:

$sql = "SELECT * FROM persons WHERE ";

$tokens = explode(" ", $searchText);
if (count($tokens) == 0) {
    $sql += "1 = 1"
}
else {
    $i = 0;
    foreach ($tokens as $val) {
        if ($i > 0) {
            $query += " OR ";
        }
        $i++;
        $query += "(firstname LIKE '%$val%' OR lastname LIKE '%$val%')";
    }
}

$sql += " ORDER by RAND() LIMIT $offset, $per_page";

$query = mysql_query($sql);

NOTE: I left your query open to SQL injection. Primarily because I don't want to rewrite it to use mysqli. That's something you need to do. You'd need a counter for the number of tokens that exist and you'd name your parameters something like $token1, $token2, etc.

edited Jul 26 '13 at 10:22

answered Jul 25 '13 at 15:44

Mike Perrenoud

66,820
29
157
232

thanks but i got a question sir how to set the search text i got these form

inside the same page . how do i call it? – Ermel Justin Pablo Lopez Jul 25 '13 at 17:26
@ErmelJustinPabloLopez, okay, so you'd get the `$searchText` like this: `$searchText = $_QUERY["q"];`. That would grab the value typed into the text box. – Mike Perrenoud Jul 25 '13 at 17:35
Notice: Undefined variable: _QUERY in C:\xampp\htdocs\paging\index.php on line 37 i put the form after the php closing tag :(. `?>

` then i set `$searchText = $_GET["q"];` before the query – Ermel Justin Pablo Lopez Jul 25 '13 at 17:48
@ErmelJustinPabloLopez, and what happened when you set the `$searchText` from the `$_GET` var? – Mike Perrenoud Jul 25 '13 at 20:17
hi sir it won't give the value i want instead it gives a random value only i tried this :( – Ermel Justin Pablo Lopez Jul 26 '13 at 10:15
@Ermel, see my edit. I forgot to increment the counter. And echo what the $sql variable is at the end while working through this because you'll be able to see the raw value. – Mike Perrenoud Jul 26 '13 at 10:23
sir i have an error `Parse error: syntax error, unexpected '}' in C:\xampp\htdocs\paging\index.php on line 44` i used this before the while loop i used your edited one sir – Ermel Justin Pablo Lopez Jul 26 '13 at 10:34
new update there's a missing sem colon :D here's the new error Notice: Undefined variable: searchText in C:\xampp\htdocs\paging\index.php on line 41 ` Notice: Undefined variable: query in C:\xampp\htdocs\paging\index.php on line 52 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\paging\index.php on line 63 ‹ Prev123Next ›` – Ermel Justin Pablo Lopez Jul 26 '13 at 10:35
this is my form command `

` – Ermel Justin Pablo Lopez Jul 26 '13 at 10:37
and i added this in the php `$searchText = $_POST['searchText'] ;` on the beginning of php it says `Notice: Undefined index: searchText in C:\xampp\htdocs\paging\index.php on line 10 ` – Ermel Justin Pablo Lopez Jul 26 '13 at 10:39

zavg · Accepted Answer · 2013-07-26T15:36:03.523

1

In your case you can simply add WHERE clause with pattern condition and receive desired effect quickly.

// basic SQL-injection protection
$searchText = htmlspecialchars ($_POST['searchText']);

// query with simple search criteria
$query = mysql_query("SELECT * FROM persons WHERE FirstName LIKE '%" 
           . $searchText . "%' ORDER by RAND() LIMIT $offset,$per_page");

But this approache have several disadvatages:

Your request is very slow (you will see it with more data in your DB) because you use ORDER BY RAND() construction which sorts randomly all entries in DB table and then returns small amount specified in your LIMIT clause;
It is neseccary to reload page every time you want to search something. If you want to implement dynamic update of search results list you should use AJAX queries from Javascript.

P.S.: Try not to use deprecated mysql_ functions, use PDO or mysqli indstead (they provide built-in SQL-injection protection trough the prepared statments).

UPDATE:

Ok, you are already using AJAX.

So you don't need form at all. Use 2 elements: text input and button.

HTML:

<input id="q" type='text' name='q' />
<input type="button" onClick="load(1)" value="Refresh"/>

Javascript:

function load(page){
    $("#loader").fadeIn('slow');
    var searchText = $('#q').val();
    $.ajax({
           url:     'index.php?action=ajax&page='+page+'&q='+searchText, 
           success:  function(data){
               $(".outer_div").html(data).fadeIn('slow');
               $("#loader").fadeOut('slow');           
           }
    });
}

edited Jul 26 '13 at 15:36

answered Jul 25 '13 at 15:46

zavg

10,351
4
44
67

sad to tell i don't know to where to get @zavg '
onblur="clearText(this)" /> > ' here's my search form where to get? so that when i press the search button page reloads and shows right thanks by the way :D how to add the code so that button catches text and search :(
– Ermel Justin Pablo Lopez Jul 25 '13 at 16:06
@ErmelJustinPabloLopez use `$searchText = htmlspecialchars ($_GET['q']);` in your case. – zavg Jul 25 '13 at 16:11
sir it's giving me Notice: Undefined index: q in C:\xampp\htdocs\paging\index.php on line 29 should i put the form inside the php? because it's in the html body tag and that is already in ajax :D it shows loading when you change page – Ermel Justin Pablo Lopez Jul 25 '13 at 16:29
@ErmelJustinPabloLopez after pressing the submit button? – zavg Jul 25 '13 at 16:36
wrong question :D so added a very basic form for me haha the tags messes the code . '

' – Ermel Justin Pablo Lopez Jul 25 '13 at 16:41
new error :( Notice: Undefined index: q in C:\xampp\htdocs\paging\index.php on line 29 almost got it :( – Ermel Justin Pablo Lopez Jul 25 '13 at 16:43
sir @zavg the query is working when i put `$searchText = 'ermel';` it outputs the way i want the only problem is i can't get this `

` i can't get the value it says `Notice: Undefined index: q in C:\xampp\htdocs\paging\index.php on line 7 ` – Ermel Justin Pablo Lopez Jul 26 '13 at 10:48
@ErmelJustinPabloLopez Your script doesn't receive your request paramater. If you are using `POST` method and `INPUT` element with `name` attribute equals `q`, then after pressing `SUBMIT` button your search string will be in PHP's `$_POST['q']` superglobal variable. – zavg Jul 26 '13 at 12:17
sir @zavg no luck :( it's like i can almost taste the bacon but there's a tape on my tounge . ` $searchText =$_POST["q"];` and the form is `
it's working on other page but on the php i can't call it from the url link here's the link `http://localhost/paging/index.php?q=df` no luck :(
` ironic i tried displaying on other page and it's working. :( and the only one i'm gonna get problem is the isset part – Ermel Justin Pablo Lopez Jul 26 '13 at 14:27
@ErmelJustinPabloLopez If you use `POST` form method the url should not be `localhost/paging/index.php?q=df`, it is should be just `localhost/paging/index.php`. Are you sure that you reload your page via `SUBMIT` button click? – zavg Jul 26 '13 at 14:59
sir @zavg i updated my code tre link i post is wrong i thought that you have to set a value for q so my mind got messy and didn't know the difference between post and get i updated my whole code sir hope you can help me :D thanks by the way – Ermel Justin Pablo Lopez Jul 26 '13 at 15:24
sir @zavg thanks but the ` $searchText = $_POST["q"];` after the – Ermel Justin Pablo Lopez Jul 26 '13 at 15:42
oh god it work on a moment then when i refresh it didn't work ohhh my god sir @zavg it workedddddd then failed haha. i remove the post q and it work nevermind my quest for that notice the new error is it won't show the output i want :( almost gott itt – Ermel Justin Pablo Lopez Jul 26 '13 at 15:53
sir @zavg oh my goodd i now know the wrong because when i press enter it only reloads my page and gives all of the random array but when i press the reload button it succesfuly do what i want oh my god let me hug you haha that took me 2 days to figure out -_- now i can go on with my thesis the only question i have is how can i change it so that when i press enter in the text box the button will catch it sir – Ermel Justin Pablo Lopez Jul 26 '13 at 15:57
@ErmelJustinPabloLopez Congratulations! Here is the answer on the last question http://stackoverflow.com/questions/12955222/how-to-trigger-html-button-when-you-press-enter-in-textbox – zavg Jul 26 '13 at 16:41
haha thanks to you. sir the code you link me works this isthe code i used `$(document).ready(function(){ $('#q').keypress(function(e){ if(e.keyCode==13) $('#enter').click(); }); });` but when i press enter it shows the ajax then refreshes the page :( – Ermel Justin Pablo Lopez Jul 26 '13 at 17:12
yes sir it's correct the problem is when i press enter it shows loading then after that it reloads the whole page and giving random results again :D `
` here's the form sir :D you're really good at php you're my hero thanks again :D @zavg
– Ermel Justin Pablo Lopez Jul 26 '13 at 17:29
@ErmelJustinPabloLopez Remove `
` and `
` tags completely, they are not necessary in your case. – zavg Jul 26 '13 at 18:03
OHHHHHHH myyyyyyyyyyy goooddd its working hahahaha gotta sleep thank you very very much @zavg should i make a new topic about the pagination where the pagination will change based on query? :D not sure if i'm gonna put it in an querry or in a different if else statement? :D thanks again you're a genius – Ermel Justin Pablo Lopez Jul 26 '13 at 18:17

php pagination and search

2 Answers2