Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post

    Pagination not working

    Ok so I am trying to make a pagination for my site. For my members and my inbox.

    I got it working good by itself with no WHERE = '$logged[username]'.

    I take
    PHP Code:
    $sql mysql_query("SELECT * FROM privates  LIMIT ".$limits.",$max") or die(mysql_error()); 
    and replace it with:
    PHP Code:
    $sql mysql_query("SELECT * FROM privates WHERE to = '$logged[username]' LIMIT ".$limits.",$max") or die(mysql_error()); 
    and then I get the error:

    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 'to = 'Dan' LIMIT 0,10' at line 1

    But without the WHERE to . it works fine.

    here is source:
    PHP Code:
    <?php
    include ("/home/jsfdan/public_html/config.php");




    $max 10//amount of articles per page. change to what to want

    $p $_GET['p'];

    if(empty(
    $p))

    {

    $p 1;

    }

    $limits = ($p 1) * $max

    //view the news article!

    if(isset($_GET['act']) && $_GET['act'] == "view")

    {

    $id $_GET['id'];

    $sql mysql_query("SELECT * FROM privates'");

    while(
    $r mysql_fetch_array($sql))

    {

    $id $r['pid'];

    $by $r['from'];

    $subject $r['subject'];

    echo 
    "MID: $id --- $subject ---- By: $by<br><br>";

    }



    }else{



    //view all the news articles in rows

    $sql mysql_query("SELECT * FROM privates WHERE to = '$logged[username]' LIMIT ".$limits.",$max") or die(mysql_error());

    //the total rows in the table

    $totalres mysql_result(mysql_query("SELECT COUNT(pid) AS tot FROM privates"),0);    

    //the total number of pages (calculated result), math stuff...

    $totalpages ceil($totalres $max); 

    //the table

    echo "Hello $logged[username]<br><br>";

    while(
    $r mysql_fetch_array($sql))

    {

    $id $r['pid'];

    $by $r['from'];

    $subject $r['subject'];

    echo 
    "MID: $id --- $subject ---- By: $by<br><br>";

    }

    //close up the table

    echo "</tr></table>";

    for(
    $i 1$i <= $totalpages$i++){ 

    //this is the pagination link

    echo "<a href='w.php?p=$i'>$i</a>|";

    }

    }


    ?>
    Last edited by UrbanTwitch; 08-13-2008 at 10:07 PM.
    Formerly MysticScript

    6+ years club

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    PHP Code:
    $sql mysql_query("SELECT * FROM privates WHERE 'to' = '".$logged['username']."' LIMIT ".$limits.",".$max) or die(mysql_error()); 
    quoting problem and probably 'to' is mysql reserved word, check last I'm not sure and if is true change it to something else.

    Edit: I check myself and 'to' is mysql reserved word
    regards
    Last edited by oesxyl; 08-14-2008 at 01:18 AM.

  • #3
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post
    So what do I do?
    Formerly MysticScript

    6+ years club

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by MysticScript View Post
    So what do I do?
    two solution:
    - use 'to' instead of to in query
    - change in table the column name field from 'to' to something else, 'user', 'to_user', whatever you want but not a mysql reserved word and then you can call it without apos, '

    regards

  • #5
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post
    Ok so I tried:

    PHP Code:
    $sql mysql_query("SELECT * FROM privates WHERE 'to' = ".$logged[username]." LIMIT ".$limits.",$max") or die(mysql_error()); 
    And I get: Unknown column 'Dan' in 'where clause'

    I am logged in as Dan. I thought it read the the coloum was 'to'.

    EDIT: I tried:
    PHP Code:
    $sql mysql_query("SELECT * FROM privates WHERE 'to' = '$logged[username]' LIMIT ".$limits.",$max") or die(mysql_error()); 
    and I get:

    Hello Dan

    1|2|3|4|5|6|7|8|9|
    Last edited by UrbanTwitch; 08-14-2008 at 01:58 AM.
    Formerly MysticScript

    6+ years club

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    I said in my replay that you have also incorect quoting. correct form is:
    PHP Code:
    $sql mysql_query("SELECT * FROM privates WHERE 'to' = '".$logged[username]."' LIMIT ".$limits.",".$max) or die(mysql_error()); 
    this:
    Code:
    'to' = '$logged[username]' LIMIT
    must be
    Code:
    'to' = '".$logged[username]."' LIMIT
    and this:
    Code:
    LIMIT ".$limits.",$max"
    must be:
    Code:
    LIMIT ".$limits.",".$max
    regards

  • #7
    Regular Coder
    Join Date
    May 2006
    Location
    Wales
    Posts
    820
    Thanks
    1
    Thanked 82 Times in 79 Posts
    You need to use backticks for the to, not apostrophes:

    PHP Code:
    $sql mysql_query "SELECT * FROM `privates`
                          WHERE `to` = '" 
    $logged['username'] . "'
                          LIMIT " 
    $limits ", " $max ";" )
                          or die ( 
    mysql_error() ); 

  • #8
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post
    Still getting

    Hello Dan

    1|2|3|4|5|6|7|8|9|

    using
    PHP Code:
    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() ); 
    I don't get it. It worked before without using WHERE `to`,etc.
    Formerly MysticScript

    6+ years club

  • #9
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by MysticScript View Post
    Still getting

    Hello Dan

    1|2|3|4|5|6|7|8|9|

    using
    PHP Code:
    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() ); 
    I don't get it. It worked before without using WHERE `to`,etc.
    take it step by step and try to debug using echo/print_r/print at each step the results you get and don't get further until work.
    PHP Code:
    if($sql){
       while(
    $row mysql_fetch_assoc($sql)){
          
    print_r($row);
       }

    and don't jump over checking. If you have mysql_query you must test the result, don't assume that work as is.

    regards

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by MysticScript View Post
    Still getting

    Hello Dan

    1|2|3|4|5|6|7|8|9|

    using
    PHP Code:
    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() ); 
    I don't get it. It worked before without using WHERE `to`,etc.
    you have a error here:
    PHP Code:
    while($r mysql_fetch_array($sql)){ // <-- use mysql_fetch_assoc instead
       
    $id $r['pid']; // <-- because you use this
       
    $by $r['from'];
       
    $subject $r['subject'];
       echo 
    "MID: $id --- $subject ---- By: $by<br><br>";

    regards

  • #11
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post
    Ok so if I use this: It should show up [check for errors?]

    PHP Code:
    <?php
    include ("/home/jsfdan/public_html/config.php");
    $max 10//amount of articles per page. change to what to want
    $p $_GET['p'];
    if(empty(
    $p))
    {
    $p 10;
    }
    $limits = ($p 1) * $max
    if(isset(
    $_GET['act']) && $_GET['act'] == "view")
    {
    $id $_GET['id'];
    $sql mysql_query("SELECT * FROM privates'");
    while(
    $r mysql_fetch_assoc($sql)){
       
    $id $r['pid']; 
       
    $by $r['from'];
       
    $subject $r['subject'];
       echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
    }  

    }else{

    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() );  

    $totalres mysql_result(mysql_query("SELECT COUNT(pid) AS tot FROM privates"),0);    

    $totalpages ceil($totalres $max); 

    echo 
    "Hello $logged[username]<br><br>";
    while(
    $r mysql_fetch_assoc($sql)){
       
    $id $r['pid']; 
       
    $by $r['from'];
       
    $subject $r['subject'];
       echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
    }
    echo 
    "</tr></table>";
    for(
    $i 1$i <= $totalpages$i++){ 
    echo 
    "<a href='w.php?p=$i'>$i</a>|";
    }
    }
    ?>
    Formerly MysticScript

    6+ years club

  • #12
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    PHP Code:
    <?php
    include ("/home/jsfdan/public_html/config.php");
    $max 10//amount of articles per page. change to what to want
    $p $_GET['p'];
    if(empty(
    $p)){
      
    $p 10;
    }
    $limits = ($p 1) * $max
    if(isset(
    $_GET['act']) && $_GET['act'] == "view"){
      
    $id $_GET['id'];
      
    $sql mysql_query("SELECT * FROM privates'");
      if(
    $sql){ // <-- checking
        
    while($r mysql_fetch_assoc($sql)){
           
    $id $r['pid']; 
           
    $by $r['from'];
           
    $subject $r['subject'];
           echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
        }
      }else{
         
    // error $sql
      
    }
    }else{
      
    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() );  
      
    // not sure if that work, expand and write checking
      // $totalres = mysql_result(mysql_query("SELECT COUNT(pid) AS tot FROM privates"),0);
      
    $q "SELECT COUNT(pid) AS tot FROM privates";
      
    $qres mysql_query($q);
      if(
    $qres){
         
    $totalres mysql_result($qres,0,0);
      }else{
         
    // error $qres
      
    }
      
    // you are sure that $max can't be 0, $totalres have any value assigned?
      
    $totalpages ceil($totalres $max); 
      echo 
    "Hello $logged[username]<br><br>";
      
    // missing checking of $sql here, you must add
      
    while($r mysql_fetch_assoc($sql)){
         
    $id $r['pid']; 
         
    $by $r['from'];
         
    $subject $r['subject'];
         echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
      }
      echo 
    "</tr></table>";
      for(
    $i 1$i <= $totalpages$i++){ 
       
    // not a error I guess, but could generate problem, I suggest:
       // echo '<a href="w.php?p='.$i.'">'.$i.'</a>|';
        
    echo "<a href='w.php?p=$i'>$i</a>|";
      }
    }
    ?>
    I think it work, not sure, but read the comments please.

    regards
    Last edited by oesxyl; 08-14-2008 at 02:55 AM.

  • #13
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post
    Nope. :\ What do I do with this:
    PHP Code:
    $totalres mysql_result(mysql_query("SELECT COUNT(pid) AS tot FROM privates"),0); 
    My updated code:

    PHP Code:
    <?php
    include ("/home/jsfdan/public_html/config.php");
    $max 10//amount of articles per page. change to what to want
    $p $_GET['p'];
    if(empty(
    $p)){
      
    $p 10;
    }
    $limits = ($p 1) * $max
    if(isset(
    $_GET['act']) && $_GET['act'] == "view"){
      
    $id $_GET['id'];
      
    $sql mysql_query("SELECT * FROM privates'");
      if(
    $sql){ // <-- checking
        
    while($r mysql_fetch_assoc($sql)){
           
    $id $r['pid']; 
           
    $by $r['from'];
           
    $subject $r['subject'];
           echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
        }
      }else{
         
    // error $sql
      
    }
    }else{
      
    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() );  


      
    $q "SELECT COUNT(pid) AS tot FROM privates";
      
    $qres mysql_query($q);
      if(
    $qres){
         
    $totalres mysql_result($qres,0,0);
      }else{
         
    // error $qres
      
    }
      
    // you are sure that $max can't be 0, $totalres have any value assigned?
      
    $totalpages ceil($totalres $max); 
      echo 
    "Hello $logged[username]<br><br>";
      if(
    $sql){ // <-- checking
      
    while($r mysql_fetch_assoc($sql)){
         
    $id $r['pid']; 
         
    $by $r['from'];
         
    $subject $r['subject'];
         echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
      }
      echo 
    "</tr></table>";
      for(
    $i 1$i <= $totalpages$i++){ 


        echo 
    "<a href=w.php?p=$i>$i</a>|";
      }
    }
    }
    ?>
    Last edited by UrbanTwitch; 08-14-2008 at 03:23 AM.
    Formerly MysticScript

    6+ years club

  • #14
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by MysticScript View Post
    Nope. :\ What do I do with this:
    PHP Code:
    $totalres mysql_result(mysql_query("SELECT COUNT(pid) AS tot FROM privates"),0); 
    remove it, is replaced by 3 lines $q = ...; $qres = ....; ...

    where I put // error XXX, put some code for debug
    for example to // error $qres, put echo '<pre>'.mysql_error().'</pre>';

    My updated code:

    PHP Code:
    <?php
    include ("/home/jsfdan/public_html/config.php");
    $max 10//amount of articles per page. change to what to want
    $p $_GET['p'];
    if(empty(
    $p)){
      
    $p 10;
    }
    $limits = ($p 1) * $max
    if(isset(
    $_GET['act']) && $_GET['act'] == "view"){
      
    $id $_GET['id'];
      
    $sql mysql_query("SELECT * FROM privates'");
      if(
    $sql){ // <-- checking
        
    while($r mysql_fetch_assoc($sql)){
           
    $id $r['pid']; 
           
    $by $r['from'];
           
    $subject $r['subject'];
           echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
        }
      }else{
         
    // error $sql, added
         
    echo '<pre>'.mysql_error().'</pre>';
      }
    }else{
      
    $sql mysql_query "SELECT * FROM `privates` WHERE `to` = '".$logged[username]."' LIMIT ".$limits.",".$max." ")
                          or die ( 
    mysql_error() );  


      
    $q "SELECT COUNT(pid) AS tot FROM privates";
      
    $qres mysql_query($q);
      if(
    $qres){
         
    $totalres mysql_result($qres,0,0);
      }else{
         
    // error $qres
         
    echo '<pre>'.mysql_error().'</pre>';
      }
      
    // you are sure that $max can't be 0, $totalres have any value assigned?
      
    $totalpages ceil($totalres $max); 
      echo 
    "Hello $logged[username]<br><br>";
      if(
    $sql){ // <-- checking
      
    while($r mysql_fetch_assoc($sql)){
         
    $id $r['pid']; 
         
    $by $r['from'];
         
    $subject $r['subject'];
         echo 
    "MID: $id --- $subject ---- By: $by<br><br>";
      }
      echo 
    "</tr></table>";
      for(
    $i 1$i <= $totalpages$i++){ 


        echo 
    "<a href=w.php?p=$i>$i</a>|";
      }
    }
    }
    ?>
    also try to print/echo or print_r from top to bottom variables to see what values have and if are what you want.

    regards

  • #15
    Regular Coder UrbanTwitch's Avatar
    Join Date
    Apr 2006
    Posts
    485
    Thanks
    44
    Thanked 1 Time in 1 Post
    I tried print but no go. Whats print_r do and how do I do it for my page?
    Formerly MysticScript

    6+ years club


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •