Multiple search pagination

Question

mcafeeza 0 Newbie Poster

12 Years Ago

hi,
if anyone could guide me on how to add a pagination script on this code? Thanks

<?php
error_reporting(0);
include("config.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>MySQL table search</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<style>
BODY, TD {
    font-family:Arial, Helvetica, sans-serif;
    font-size:12px;
}
</style>
</head>


<body>

<form id="form1" name="form1" method="post" action="search.php">
<label for="from">From</label>
<input name="from" type="text" id="from" size="10" value="<?php echo $_REQUEST["from"]; ?>" />
<label for="to">to</label>
<input name="to" type="text" id="to" size="10" value="<?php echo $_REQUEST["to"]; ?>"/>
 <label>Name or Email:</label>
<input type="text" name="string" id="string" value="<?php echo stripcslashes($_REQUEST["string"]); ?>" />
<label>City</label>
<select name="city">
<option value="">--</option>
<?php
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY city ORDER BY city";
    $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
    while ($row = mysql_fetch_assoc($sql_result)) {
        echo "<option value='".$row["city"]."'".($row["city"]==$_REQUEST["city"] ? " selected" : "").">".$row["city"]."</option>";
    }
?>
</select>
<input type="submit" name="button" id="button" value="Filter" />
  </label>
  <a href="search.php"> 
  reset</a>
</form>
<br /><br />
<table width="700" border="1" cellspacing="0" cellpadding="4">
  <tr>
    <td width="90" bgcolor="#CCCCCC"><strong>From date</strong></td>
    <td width="95" bgcolor="#CCCCCC"><strong>To date</strong></td>
    <td width="159" bgcolor="#CCCCCC"><strong>Name</strong></td>
    <td width="191" bgcolor="#CCCCCC"><strong>Email</strong></td>
    <td width="113" bgcolor="#CCCCCC"><strong>City</strong></td>
  </tr>
<?php
if ($_REQUEST["string"]<>'') {
    $search_string = " AND (full_name LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR email LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%')"; 
}
if ($_REQUEST["city"]<>'') {
    $search_city = " AND city='".mysql_real_escape_string($_REQUEST["city"])."'";   
}

if ($_REQUEST["from"]<>'' and $_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE from_date >= '".mysql_real_escape_string($_REQUEST["from"])."' AND to_date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_city;
} else if ($_REQUEST["from"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE from_date >= '".mysql_real_escape_string($_REQUEST["from"])."'".$search_string.$search_city;
} else if ($_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE to_date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_city;
} else {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE id>0".$search_string.$search_city;
}


$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) 




{
    while ($row = mysql_fetch_assoc($sql_result)) {
?>
  <tr>
    <td><?php echo $row["from_date"]; ?></td>
    <td><?php echo $row["to_date"]; ?></td>
    <td><?php echo $row["full_name"]; ?></td>
    <td><?php echo $row["email"]; ?></td>
    <td><?php echo $row["city"]; ?></td>
  </tr>
<?php
    }
} else {
?>
<tr><td colspan="5">No results found.</td>
<?php    
}
?>
</table>


<script>
    $(function() {
        var dates = $( "#from, #to" ).datepicker({
            defaultDate: "+1w",
            changeMonth: true,
            numberOfMonths: 2,
            dateFormat: 'yy-mm-dd',
            onSelect: function( selectedDate ) {
                var option = this.id == "from" ? "minDate" : "maxDate",
                    instance = $( this ).data( "datepicker" ),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                        selectedDate, instance.settings );
                dates.not( this ).datepicker( "option", option, date );
            }
        });
    });
    </script>

</body>
</html>

php ui-ux

3 Contributors
6 Replies
1K Views
5 Days Discussion Span
Latest Post 12 Years Ago Latest Post by mcafeeza

diafol

12 Years Ago

Firstly, I'd suggest refactoring your php code to try and separate php and html markup. Some mixing may be inevitable, but the example is very untidy (no offence) and could be difficult/tedious to maintain or edit.

Providing functions (or even a class[es]) as opposed to raw procedural code could help you tidy up the code. I suggest placing almost all of your php code above the !doctype declaration (DTD) or place it in an include file, which would also be included above the DTD.

Edited 12 Years Ago by diafol

adam.adamski.96155 43 Junior Poster

12 Years Ago

Sorry if I am patronising, but are you sure your server supports short tags (line 120)?
<?= "blah blah" ?> won't work on some servers,
<?php echo "blah blah"; ?> works on all.
Can we see the page?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mcafeeza 0 Newbie Poster · Answer 1 · 2012-10-11T12:37:08+00:00

Thanks for replying and sorry for my messy code. This script is for multiple search form with pagination. Here is the updated script. I have insert the pagination code but it cant display the pagination number for the next pages. I have 30 records total for this table. Im confused where to fix it. Anyone?

<form id="form1" name="form1" method="post" >

        <label for="from">Date From</label>
        <input name="from" type="text" id="from" size="10" value="<?php echo $_REQUEST["from"]; ?>" />
        <label for="to">to</label>
        <input name="to" type="text" id="to" size="10" value="<?php echo $_REQUEST["to"]; ?>"/>
        <label>Search By Status</label>
        <select name="Status">
        <option value="">-- Select --</option>
        <?php
            $sql = "SELECT * FROM project GROUP BY Status ORDER BY Status";
            $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
            while ($row = mysql_fetch_assoc($sql_result)) {
                echo "<option value='".$row["Status"]."'".($row["Status"]==$_REQUEST["Status"] ? " selected" : "").">".$row["Status"]."</option>";
            }
        ?>
        </select>

        <input type="submit" type="hidden" name="button" id="button" value="View" />
          </label>
         <input type="reset" value="Reset!" /> 
        <tr><td colspan='3'><input type="button" onclick="window.print()" value="Print" /> </tr></td>

        </form>


        <?php

        if ($_REQUEST["Status"]<>'') {
            $search_Status = " AND Status='".mysql_real_escape_string($_REQUEST["Status"])."'"; 
        }

        if ($_REQUEST["from"]<>'' or $_REQUEST["to"]<>'') {
            $sql = "SELECT * FROM project WHERE Date_Submitted BETWEEN '".mysql_real_escape_string($_REQUEST["from"])."' AND '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_Status;
        } else if ($_REQUEST["from"]<>'') {
            $sql = "SELECT * FROM project WHERE Date_Submitted >= '".mysql_real_escape_string($_REQUEST["from"])."'".$search_string.$search_Status;
        } else if ($_REQUEST["to"]<>'') {
            $sql = "SELECT * FROM project WHERE Date_Submitted <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_Status;
        } else {
            $sql = "SELECT * FROM project WHERE Id_No>0".$search_string.$search_Status;
        }

        $sql_query = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);

        if ($Num_Rows = mysql_num_rows($sql_query)>0) {
            while ($row = mysql_fetch_assoc($sql_query)) {

        $Per_Page = 4;   // Per Page

            $Page = $_POST["Page"];
            if(!$_POST["Page"])
            {
                $Page=1;
            }

            $Prev_Page = $Page-1;
            $Next_Page = $Page+1;

            $Page_Start = (($Per_Page*$Page)-$Per_Page);
            if($Num_Rows<=$Per_Page)
            {
                $Num_Pages =1;
            }
            else if(($Num_Rows % $Per_Page)==0)
            {
                $Num_Pages =($Num_Rows/$Per_Page) ;
            }
            else
            {
                $Num_Pages =($Num_Rows/$Per_Page)+1;
                $Num_Pages = (int)$Num_Pages;
            }
        }
        }


        //$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);

        $sql .=" order by Status ASC LIMIT $Page_Start , $Per_Page";
        $sql_query  = mysql_query($sql);

        ?>

        <center>
        <table width="700" border="0" cellspacing="0" cellpadding="4">

          <tr>
            <td width="90" bgcolor="#CCCCCC"><strong>ID</strong></td>
            <td width="159" bgcolor="#CCCCCC"><strong>Contract Name</strong></td> 
            <td width="113" bgcolor="#CCCCCC"><strong>Date Submitted</strong></td>
           <td width="90" bgcolor="#CCCCCC"><strong>Provider</strong></td> 
            <td width="159" bgcolor="#CCCCCC"><strong>Status</strong></td> 
          </tr>



        <?php

            while ($row = mysql_fetch_assoc($sql_query)) {
        ?>


          <tr>
            <td><?php echo $row["Id_No"]; ?></td> 
            <td><?php echo $row["ContractName"]; ?></td> 
            <td><?php echo $row["Date_Submitted"]; ?></td> 
            <td><?php echo $row["Provider"]; ?></td>
            <td><?php echo $row["Status"]; ?></td>
          </tr>

        <?php
            }


        ?>


        </table>

        Total <?= $Num_Rows;?> Record : <?=$Num_Pages;?> Page :
            <?
            if($Prev_Page)
            {
                echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> ";
            }

            for($i=1; $i<=$Num_Pages; $i++){
                if($i != $Page)
                {
                    echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]";
                }
                else
                {
                    echo "<b> $i </b>";
                }
            }
            if($Page!=$Num_Pages)
            {
                echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next>></a> ";
            }

            mysql_close($connection);

        ?>

        </center>

thanks.

mcafeeza 0 Newbie Poster · Answer 2 · 2012-10-12T00:08:50+00:00

oh that was a mistake, it supposed to be <?php echo "blabla"; ?>. but still the same not showing the next page button and only show the first page for 4 items per page.

Total <?php echo $Num_Rows;?> Record : <?php echo $Num_Pages;?> Page :
        <?
        if($Prev_Page)
        {
            echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> ";
        }

        for($i=1; $i<=$Num_Pages; $i++){
            if($i != $Page)
            {
                echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]";
            }
            else
            {
                echo "<b> $i </b>";
            }
        }
        if($Page!=$Num_Pages)
        {
            echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next>></a> ";
        }

        mysql_close($connection);   
        ?>

thanks. i dont have the website for this to show how the display bcuz i did this in my localhost. if you want me to post the fully code, i will but the code looks a little messy.

mcafeeza 0 Newbie Poster · Answer 3 · 2012-10-12T02:10:38+00:00

here is my latest fixed code. Now all the pagination is display and working well. The problem now is when i put a date in date range form that doesnot have in database it comes out an error like this. It supposed to say that the record not found etc.

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

mcafeeza 0 Newbie Poster · Answer 4 · 2012-10-16T01:09:13+00:00

im already fixed this code and working fine! thanks.