PHP Pagination & Variables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
miketw
Forum Newbie
Posts: 5
Joined: Sat May 05, 2007 6:44 am

PHP Pagination & Variables

Post by miketw »

I have a html form with two drop downs to filter a database and then return its results. I have below my script which I have tried to implemtn pagination into. MY problem is that the pagination only works properly if "SelectManufacutrer" and "SelectColour" are selected meaning that it brings back all of the records. If I filter it by the colour Red, then only the first page works and the pagination doesnt work then.. it loses its variables and returns : Failed2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Red' LIMIT 25, 25' at line 1

Im a noob, a beginner. I am learning php. Can someone help me tidy up the code and implement a working pagination where it will still paginate when the results are filtered.

As you can see, ive tried passing the variables through the URL using Manufacturer=$Manufactuer&colour...
Can someone check to see if i have made any mistakes.

The prolbem only occours if I select something in the drop downs, then the second page of results doesnt work. It works perfcetley fine when all the records are returned.

Thanks in advance

Code: Select all

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");


function secured($val)
 {
  $val = strip_tags(trim(($val))) ;
  $val = escapeshellcmd($val);
  return stripslashes($val);
  }
   if (get_magic_quotes_gpc()) {
$Manufacturer = $_GET["Manufacturer"]; 
$Color = $_GET["Color"]; 

} else {
$Manufacturer = addslashes($_GET["Manufacturer"]);
$Color = addslashes($_GET["Color"]); 

}
if(isset($_GET['brick']))  {
$where = "";
$sep = " WHERE ";
}
if($Manufacturer != "SelectManufacturer") {
$where .= $sep." Manufacturer = '".$Manufacturer."'";

$sep = " AND ";
}

if($Color != "SelectColor") {
$where .= $sep." colour = '".$Color."'";
$sep = " AND ";
}

if($_GET['page']) // Is page defined?

    {

        $page = $_GET['page']; // Set to the page defined

    }else{

        $page = 1; // Set to default page 1
}

$limit          = 25;               

if(empty($page)){
        $page = 1;
    }
$limitvalue = $page * $limit - ($limit);     
$query = "SELECT * FROM bricks $where LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Failed2: ".mysql_error());
$query1 = "SELECT * FROM bricks $where";
$result1 = mysql_query($query1) or die("Failed1: ".mysql_error());
$totalrows = mysql_num_rows($result1);
       
       if(!$totalrows)
        {
            echo "<table align='center'><tr><td align='center'>Sorry, no bricks matching your selection were found. </td></tr></table>"; //message for when no bricks are returned
         }
         else
             {         
                         if ($bgcolor == "#E0E0E0"){
            $bgcolor = "#FFFFFF";
        }else{
            $bgcolor = "#E0E0E0";
        } 
             echo "<table align='center' border=0 ><tr>";
                        echo "<td align='left' width=250 >There are <b>$totalrows</b> matches to your query</td>";   //message saying how many matches for query            
                        echo "<table align='center' border=1  bgcolor=E0E0E0><tr>";
                        echo "<td align='left' width=125 bgcolor='#11d163' color='#FF0000'><b>Supplier</b></td>";
                        echo "<td align='left' width=125 bgcolor='#11d163'><b>Color</b></td>";
                        echo "<td align='left' width=125 bgcolor='#11d163'><b>Details</b></td>";
                        echo "<td align='center' bgcolor='#11d163'><b>Picture</b></td>";
                        echo "</tr>";
                        
                        while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                        
                        echo "<tr><td align='left'>".$row["Manufacturer"]."</td>";
                        echo "<td align='left'>".$row["Colour"]."</td>";
                        echo "<td align='left'>".$row["Brick Name"]."</td>";
                        echo "<td align='left'>".$row["Image"]."</td>";
                         echo "</tr>";
                  }
              }
                 echo "</table>";

        if($page != 1){ 
        $pageprev = ($page - 1);
        
        echo("<a href=\"$_SERVER[PHP_SELF]?Manufacturer=$Manufacturer&Colour=$Color&page=$pageprev\">PREV".$limit."</a> ");
    }else{
        echo("PREV".$limit." ");
    }

    $numofpages = ceil($totalrows / $limit);
    
    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"$_SERVER[PHP_SELF]?Manufacturer=$Manufacturer&Color=$Color&page=$i\">$i</a> ");
        }
    }


    if(($totalrows - ($limit * $page)) > 0){
        $pagenext = ($page + 1);
         
        echo("<a href=\"$_SERVER[PHP_SELF]?Manufacturer=$Manufacturer&Color=$Color&page=$pagenext\">NEXT".$limit."</a>"); 
           }

    mysql_free_result($result);
     
?>
miketw
Forum Newbie
Posts: 5
Joined: Sat May 05, 2007 6:44 am

Post by miketw »

:?: bump lol
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:4. All users of any level are restricted to bumping (as defined here) any given thread within twenty-four (24) hours of its last post. Non-trivial posts are not considered bumping. A bump post found in violation will be deleted, and you may or may not receive a warning. Persons bumping excessively be considered as spammers and dealt with accordingly.
While you are in violation, it is not by much, which is why I am letting this slide. Try not to let it happen again :wink:
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

What is $_GET['brick'] ?

Can we see your form ?
miketw
Forum Newbie
Posts: 5
Joined: Sat May 05, 2007 6:44 am

Post by miketw »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Sorry for the bump, i saw the thread sliding to the end of second page and im desperate for this 

MY form is just basically two drop downs 

brick was my hidden value in the form

[syntax="html"]
<p align="center">

                <input type="hidden" name="brick" value="brick">
                <input type="hidden" name="page" value="1" />
		</p>
		<p align="center">&nbsp;</p>
		<p align="left">&nbsp;</p>
		<p align="center">&nbsp;</p>
		<table border="0" height="198px" width="300px">
			<tr>
				
	<p align="center">&nbsp;</p>

	<p align="center"><select size="1" name="Manufacturer">

	<option selected value="SelectManufacturer">All Suppliers</option>
        <option value="Wienerberger">Wienerberger</option>
	<option value="Furness">Furness</option>
	<option value="Hemmings">Hemmings</option>
        <option value="Carlton">Carlton</option>

        <option value="Tilcon">Tilcon</option>

        <option value="Caradale">Caradale</option>
        <option value="Swarland">Swarland</option>
        <option value="Carlton">Carlton</option>
        <option value="Baggeridge">Baggeridge</option>
        <option value="Blockleys">Blockleys</option>

        <option value="Hanson">Hanson</option>

        <option value="Ibstock">Ibstock</option>
        <option value="Marshalls">Marshalls</option>
        <option value="Tyrone">Tyrone</option>
        <option value="Carlton">Carlton</option>
        <option value="Cheshire">Cheshire</option>

        <option value="Snowie">Snowie</option>

        <option value="Raeburn">Raeburn</option>
	</select></p>
	<p align="center"><select size="1" name="Color">
	<option selected value="SelectColor">All Colors</option>
	<option value="Red">Red</option>

	<option value="Purple">Purple</option>
	<option value="Brown">Brown</option>

        <option value="Yellow">Yellow</option>
        <option value="Cream">Cream</option>
        <option value="Golden">Golden</option>
        <option value="Black">Black</option>

	</select></p>
	<p align="center"><input type="submit" value="Submit" name="B1"></p>
				<p>&nbsp;</td>
			</tr>
			<tr>
				<td height="21" width="250">&nbsp;</td>
			</tr>
		</table>
	</form>

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: PHP Pagination & Variables

Post by onion2k »

miketw wrote:

Code: Select all

if(isset($_GET['brick']))  {
$where = "";
$sep = " WHERE ";
}
if($Manufacturer != "SelectManufacturer") {
$where .= $sep." Manufacturer = '".$Manufacturer."'";

$sep = " AND ";
}

if($Color != "SelectColor") {
$where .= $sep." colour = '".$Color."'";
$sep = " AND ";
}
Rather than building the WHERE clause in this way, where each section relies $sep being set in the previous section, I'd build it in a way that allows any bit to be set or ignored without affecting the rest:

Code: Select all

$query = "SELECT * FROM `bricks`WHERE 1 ";
$query .= ($Manufacturer != "SelectManufacturer") ? "AND Manufacturer = '".$Manufacturer."' " : "";
$query .= ($Color != "SelectColor") ? "AND colour = '".$Color."' " : "";
$query .= "LIMIT $limitvalue, $limit";
miketw
Forum Newbie
Posts: 5
Joined: Sat May 05, 2007 6:44 am

Post by miketw »

How secure is this code? I dont know enough yet to see if its safe or if im vulnerable to sql injection.

Also, If I wanted to log the searches that were made would i just "INSERT $Manufacturer ... $color" into database so that I can look at my database and see what people have searched for? Or is there an easier/better way to do this?
Post Reply