Page 1 of 1

PHP Pagination & Variables

Posted: Sat May 05, 2007 6:49 am
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);
     
?>

Posted: Sun May 06, 2007 5:42 am
by miketw
:?: bump lol

Posted: Sun May 06, 2007 5:19 pm
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:

Posted: Mon May 07, 2007 3:25 am
by CoderGoblin
What is $_GET['brick'] ?

Can we see your form ?

Posted: Mon May 07, 2007 6:15 am
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]

Re: PHP Pagination & Variables

Posted: Mon May 07, 2007 6:34 am
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";

Posted: Mon May 07, 2007 12:36 pm
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?