0

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>
3
Contributors
6
Replies
10
Views
4 Years
Discussion Span
Last Post by mcafeeza
0

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 by diafol

0

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.

Edited by mcafeeza: incorrect word

0

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?

0

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.

Edited by mcafeeza: incorrect word

0

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

Edited by mcafeeza: incorrect word

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.