rafal Posted October 18, 2014 Share Posted October 18, 2014 Hello everybody, i have page called cat.php $cat_name get the data submited from othe .php page see line 77 this page show records WHERE category='$cat_name' see line 78 i have 2 problems: first problem: if i dont have this "where" condition, the sort function works fine see line 74 second problem: if i dont have this "where" condition, the paging function works fine see line from 108 here is my code thank you very much for your help Rafal <html> <head> <title></title> </head> <body> <?php> include("config.php"); ?> <?php $verbindung1 = mysql_connect("$hoster", "$nameuser", "$password") or die ("connection error"); mysql_select_db("$basedata") or die ("database error"); $abfrage = "SELECT cat_id, cat_name FROM $table3 ORDER BY cat_name ASC"; $ergebnis = mysql_query($abfrage); $num = mysql_num_rows($ergebnis); ?> <table> <tr> <?php while ($row = mysql_fetch_object($ergebnis)) { ?> <td> <font face="Arial" size="2"> <form action="cat.php" method="post" name="cat_form" id="cat_form"> <input type="hidden" name="inp_cat_name" value="<?php echo $row->cat_name; ?>"> <input type="submit" value="<?php echo $row->cat_name; ?>"> </form> </font> </td> <?php } ?> </tr></table> <?php> include("config.php"); ?> <?Php try { $dbo = new PDO('mysql:host=db.abcdef.fr;dbname='.$basedata, $nameuser, $password); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br/>"; die(); } $page_name="cat.php"; // page name @$sort=$_GET['sort']; if(strlen($sort)>0 and !ctype_alnum($sort)) { echo "Data Error"; exit; } @$start=$_GET['start']; if(strlen($start) > 0 and !is_numeric($start)) { echo "Data Error"; exit; } $zero = ($start - 0); $limit = 20; // number of rows per page $this1 = $zero + $limit; $back = $zero - $limit; $next = $zero + $limit; $query2=" SELECT destination, stars, price FROM $table1 WHERE vis='yes'"; $count=$dbo->prepare($query2); $count->execute(); $nume=$count->rowCount(); ?> <font face="Arial" size="2">Sortieren nach:<br> <?php echo "<a href='$page_name?sort=destination'>Zielort</a>"; ?> | <?php echo "<a href='$page_name?sort=stars'>Sterne</a>"; ?> | <?php echo "<a href='$page_name?sort=price'>Preis</a>"; ?><br><br> </font> <?php $cat_name = $_POST["inp_cat_name"]; $query=" SELECT * FROM $table1 WHERE category='$cat_name'"; if(isset($sort) and strlen($sort)>0){ $query = $query . " order by $sort"; } $query = $query. " limit $zero, $limit "; foreach ($dbo->query($query) as $row) { ?> <table cellspacing="0" cellpadding="0" border="1" width="600px" class="table"> <tr> <td rowspan="2" valign="top" class="td"> <img src="<?php if ($row[photo1] != '') { ?><?php echo $row[photo1] ?><?php } ?><?php if ($row[photo1] == '') { ?><?php echo 'cloud/500x282.gif' ?><?php } ?>" width="200" height="113"> </td> <th width="400px" class="th"><font face="Arial" size="2"><b><?php echo $row[product] ?></b> <?php if ($row[stars] == "1") { ?><?php echo "<img src='cloud/star.png'>"; } ?> <?php echo $row[destination] ?> (<?php echo $row[category] ?>)</font></th> </tr> <tr> <td valign="top" class="td" style="padding:7px;"> <font face="Arial" size="2"> <?php echo $row[shortdesc] ?><br> from <font size="4"><b><?php echo $row[price] ?> €</b></font> <font face="Arial" size="1">Nr. <?php echo $row[inventar] ?></font><br> <br> </font> </td></tr> </table> <br> <?php } if($back >=0) { echo "<a href='$page_name?start=$back&sort=$sort'><font face='Verdana' size='2'>back</font></a>"; } echo " "; $i=0; $l=1; for($i=0;$i < $nume;$i=$i+$limit){ if($i <> $zero){ echo " <a href='$page_name?start=$i&sort=$sort'><font face='Verdana' size='2'>$l</font></a> "; } else { echo "<font face='arial' size='2' color='black'>$l</font>";} $l=$l+1; } echo " "; if($this1 < $nume) { echo "<a href='$page_name?start=$next&sort=$sort'><font face='Verdana' size='2'>next</font></a>";} ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/291913-sort-and-paging-with-where-condition/ Share on other sites More sharing options...
mac_gyver Posted October 19, 2014 Share Posted October 19, 2014 because you are using a post method form for your category, the submitted $cat_name value is only present on the one page request that the form submitted to. on all other page requests, there is no $cat_name value and the where clause becomes WHERE category='', which is likely a false value and doesn't match any rows in your database table. so, two things - 1) you should ALWAYS validate inputs and take an appropriate action if they are not an expected value. if there is no $cat_name value, your code should do something predictable. either output a message that no category was selected and don't even attempt to run the database query or form the database query without the category in the WHERE clause and match all categories. 2) you must propagate the $cat_name value (which should actually be the category id, not the name) in your pagination/sort links. the easiest way of doing this is to make your category form a get method form (or just use links for the categories) and build your pagination links using http_build_query(). btw - your code needs some help. some of these things are causing the code to not do what you expect. also, fixing these things will organize and reduce the amount of code, making it easier for you and for us to see what it is your code is trying to do. 1) you are using both mysql and pdo database statements. you should use only one type and since mysql is obsolete, use pdo throughout all your code. 2) you are including config.php twice. once is enough. 3) you are running code setting variables that don't ever get used, such as the $num variable. unused code should be removed. 4) you should enable exceptions for your PDO database connection so that any errors that occur with the query statements will throw an exception. you should also disable emulated prepared queries. 5) you should NOT use any @ error suppressors in your code. for variables that might not exist, use isset() to test for them and take an appropriate action if they are not set. 6) your pagination is actually 'rowination' you are specifying the starting row in the links. you should be specifying a logical page number. 7) to get a total row count, you should use a COUNT(*) query. selecting all the rows from your table just to get a count of the rows is not efficient. you are using a prepared() query when there's nothing in the query that requires it to be prepared, then you are running a non-prepared query when you do have external data being put directly into the query. these should be the other way around. 9a) the query that gets a total row count and the query that gets the actual data to display on the page MUST have the same WHERE clause. the WHERE clause should be built in a php variable and used in both of the sql query statements. 9b) the vis='yes' condition must also be in the WHERE clause for the two queries (or you could create a database 'view' that contains this condition.) if you do item #11 in this list, of reorganizing your code, having all the main php code together, without it being mixed in with and cluttered up by the html markup will make it easier to see problems like this. 10) the $sort value that's coming from the visitor's browser MUST be validated to insure it is ONLY an expected value in order to prevent sql injection. the ORDER BY $sort term in the query cannot be protected by escaping the value or using a prepared query placeholder. if you make an array of the permitted sort values, you can loop over this array to build the sort links and use the array to validate the submitted sort value. 11) lastly, your code needs a general reorganization that will help clean it up. the php logic that determines what to do on the page, based on inputs the page receives (controller logic), and any database retrieval code (model logic) should all come first at the start of the file. this is referred to as the 'business logic'. the 'presentation logic' that contains all the html/css/javascript markup should come at the end. the business logic should set variables as it's output that contains the data that the presentation logic needs. the presentation logic should contain no database specific instructions, it should only receive variables from the business logic that contain data. edit: 12) you should use the cat_id to relate your tables, not the category name. Quote Link to comment https://forums.phpfreaks.com/topic/291913-sort-and-paging-with-where-condition/#findComment-1494151 Share on other sites More sharing options...
mac_gyver Posted October 20, 2014 Share Posted October 20, 2014 another thing to add to your list of to-do's. you should style the elements on the page using css. the <font> tag is obsolete along with most of the inline styling in your markup. Quote Link to comment https://forums.phpfreaks.com/topic/291913-sort-and-paging-with-where-condition/#findComment-1494270 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.