princeofpersia Posted December 15, 2010 Share Posted December 15, 2010 Hi guys I have this pagination code and i have no idea what im doing wrong. I have two tables users and comments user table includes id username password email comments includes id title msg user_id --------------- I am trying to enable users to see their msg by filtering their msg using user_id and id As soon as i add order by id limit $start,$per_page to the query below i get syantax error, if i remove it, it lists all the comments in my table. So really the pagination works fine but as soon as i add limit it gives me Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource................. in line 39 thanks for your time <?php session_start(); include ("../global.php"); //welcome messaage $username=$_SESSION['username']; $query=mysql_query("SELECT id FROM users WHERE username='$username'"); while($row = mysql_fetch_array($query)) { $id = $row['id']; } $per_page =3; if($_GET) { $page=$_GET['page']; } //get table contents $start = ($page-1)*$per_page; $sql = "select * from comments, users order by id limit $start,$per_page WHERE comments.user_id=users.id"; $rsd = mysql_query($sql); ?> <table width="800px"> <?php //Print the contents while($row = mysql_fetch_array($rsd)) { $title=$row['title']; $msg=$row['msg']; ?> <tr><td style="color:#B2b2b2; padding-left:4px" width="30px"><?php echo $title; ?></td><td><?php echo $msg; ?></td></tr> <?php } //while ?> </table> Quote Link to comment Share on other sites More sharing options...
solon Posted December 15, 2010 Share Posted December 15, 2010 Try it now. I just added a part that checks if the page variable is empty or 0 <?php session_start(); include ("../global.php"); //welcome messaage $username=$_SESSION['username']; $query=mysql_query("SELECT id FROM users WHERE username='$username'"); while($row = mysql_fetch_array($query)) { $id = $row['id']; } $per_page =3; if($_GET) { $page=$_GET['page']; } //get table contents if(($page == '') || ($page == '0')) { $start = $per_page; } else { $start = ($page-1)*$per_page; } $sql = "select * from comments, users order by id limit $start,$per_page WHERE comments.user_id=users.id"; $rsd = mysql_query($sql); ?> Quote Link to comment Share on other sites More sharing options...
princeofpersia Posted December 15, 2010 Author Share Posted December 15, 2010 Hi thanks but i have tried it but still the same Quote Link to comment Share on other sites More sharing options...
solon Posted December 15, 2010 Share Posted December 15, 2010 Change this $sql = "select * from comments, users order by id limit $start,$per_page WHERE comments.user_id=users.id"; to this $sql = "select * from comments, users WHERE comments.user_id=users.id order by id limit $start,$per_page"; Quote Link to comment Share on other sites More sharing options...
princeofpersia Posted December 15, 2010 Author Share Posted December 15, 2010 comes up with the same error, when i donr have the limit in my query it shows the entire enteries even submitted by different user who have different username and user id Quote Link to comment Share on other sites More sharing options...
solon Posted December 15, 2010 Share Posted December 15, 2010 Try this $sql = "select * from `comments` WHERE `user_id`= '$id' limit $start,$per_page"; Quote Link to comment Share on other sites More sharing options...
princeofpersia Posted December 15, 2010 Author Share Posted December 15, 2010 i made it worked, what i did was in the begining of query i removed * and specified field names as SELECT users.id, comments.msg from users comment etc thanks for your help you are a star Quote Link to comment Share on other sites More sharing options...
solon Posted December 15, 2010 Share Posted December 15, 2010 well i believe that you dont need that but if it works thats great Quote Link to comment 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.