MySQL Error with Pagination

Orc · 02-07-2008, 04:06 AM

When trying to perform a sql query with a normal pagination procedure, I get this error:

PHP Code:


			


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in index.php on line 118

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 '-3' at line 1 





// This is what the query looks like

$query = mysql_query("SELECT * FROM `news` LIMIT $limit ");



// Limit is this:

$limit = ($p - 1) * $max;

// $p = $_GET['p'] and $max = 3;

Please help! :(

dylanfm · 02-07-2008, 09:04 AM

Try it without quotes around news

ReSpawN · 02-07-2008, 10:51 AM

`news` should be fine.

SQL Code:

SELECT * FROM `news` LIMIT -3

I think the -3 is the problem. Try it PHPwise like this;

PHP Code:


			
$query = 'SELECT * FROM `news` LIMIT "'.$limit.'"';

Salathe · 02-07-2008, 11:48 AM

The problem is your understanding of the LIMIT clause. Here's some examples with your query as it is at the moment.

WHERE p = 1, max = 3, limit = (1 - 1) * 3:
SELECT * FROM news LIMIT 0
You're asking MySQL to return the first zero rows.

WHERE p = 2, max = 3, limit = (2 - 1) * 3:
SELECT * FROM news LIMIT 3
You're asking MySQL to return the first three rows.

The syntax for specifying limits is as follows:
SELECT ... LIMIT [offset,] row_count

Putting that in terms of what you want to be using:
SELECT ... LIMIT $limit, $max

Your variable naming doesn't make things very clear ($limit isn't really the limit, $max is). So, I'd suggest perhaps adopting some more suitable variables, like:
$page (the current page, eg. 1: replaces $p)
$perpage (how many items per page, eg. 3: replaces $max)
$offset (the row offset to use in the query, eg. 3 for page 2: replaces $limit)

Then you query can become:
SELECT * FROM news LIMIT $offset, $perpage

Orc · 02-07-2008, 02:42 PM

Heres the whole thing I made:

PHP Code:


			
     $max = 3;
     $p = $_GET['p'];
     $limit = ($p - 1) * $max;
     $rows = @mysql_num_rows($query);
     $numOfPages = ceil($rows/$max);
     if ($p < 1)
     {
         $p = 1;
     } elseif ($p < $numOfPages )
     {
         $p = $numOfPages;
     }

                  if (!isset($prev))
         {
             $prev = $p - 1;
             echo '<a href="/?p='.$prev.'">'.$prev.'</a>';
         }
         for ($i=1;$i<=$numOfPages;$i++)
         {
             echo '<a href="/?p='.$i.'">'.$i.'</a>';
         }
         if (!isset($next))
         {
             $next = $p + 1;
             echo '<a href="/?p='.$next.'">'.$next.'</a>';
         }

This whole script is from a tutorial really.. So, don't blame me about variable naming. -_-

Village Idiot · 02-07-2008, 03:05 PM

Your math wont work on page or 1

PHP Code:


			
     $max = 3;
     $p = $_GET['p'];
     $limit = ($p - 1) * $max;
     $rows = @mysql_num_rows($query);
     $numOfPages = ceil($rows/$max);

If $page = 1, it will be subtracted to zero and $limit will be zero. Or if $page = 0, max will be -3

Salathe explained the limit comman well. But to answer your specific question, you cant limit to a negative number like that. Although your paganation script wont work with the query you have at the first post

</span></span>

Orc · 02-07-2008, 03:07 PM

Quote:

Originally Posted by Village Idiot

Your math wont work on page or 1

PHP Code:


			
     $max = 3;
     $p = $_GET['p'];
     $limit = ($p - 1) * $max;
     $rows = @mysql_num_rows($query);
     $numOfPages = ceil($rows/$max);

If $page = 1, it will be subtracted to zero and $limit will be zero. Or if $page = 0, max will be -3

Salathe explained the limit comman well. But to answer your specific question, you cant limit to a negative number like that. Although your paganation script wont work with the query you have at the first post

</span></span>

I need a pagination. -_-

Could you guys make a tutorial on this?
I'm not good with math when using MySQL. :S

Village Idiot · 02-07-2008, 03:30 PM

The generic code I use, form an image hosting site I built

PHP Code:


			
//get the total count so we know what we are working with
$count_query = mysql_query("SELECT COUNT(id) AS `count` FROM `images` WHERE `uploader` = '$user->id'") or die(mysql_error());
$count_arr = mysql_fetch_array($count_query);
$count = $count_arr["count"];

//assign the pages
$page = $data->get("page");

//if page not set, set it to zero
if($page == "")
{
    $page = 0;
}
//set this to the number of items you want it to display per page
$perpage = 10;

//If there are enough rows to fit on a single page, we dont need any more pages
if(mysql_num_rows($count_query) < $perpage)
{
    $pages = 0;
}
//If there are too many rows, allow for more pages
else
{
    $pages=ceil($count / $perpage);
}
//this is where the page starts in the query
$page_start = $perpage * $page;
//this is where the page ends
$page_end = $page_start + ($perpage);

$sql = mysql_query("SELECT * FROM `images` WHERE `uploader` = '$user->id' ORDER BY `date` DESC LIMIT $page_start,$perpage") or die(mysql_error());

Orc · 02-07-2008, 03:39 PM

Quote:

Originally Posted by Village Idiot

The generic code I use, form an image hosting site I built

PHP Code:


			
//get the total count so we know what we are working with

$count_query = mysql_query("SELECT COUNT(id) AS `count` FROM `images` WHERE `uploader` = '$user->id'") or die(mysql_error());

$count_arr = mysql_fetch_array($count_query);

$count = $count_arr["count"];



//assign the pages

$page = $data->get("page");



//if page not set, set it to zero

if($page == "")

{

    $page = 0;

}

//set this to the number of items you want it to display per page

$perpage = 10;



//If there are enough rows to fit on a single page, we dont need any more pages

if(mysql_num_rows($count_query) < $perpage)

{

    $pages = 0;

}

//If there are too many rows, allow for more pages

else

{

    $pages=ceil($count / $perpage);

}

//this is where the page starts in the query

$page_start = $perpage * $page;

//this is where the page ends

$page_end = $page_start + ($perpage);



$sql = mysql_query("SELECT * FROM `images` WHERE `uploader` = '$user->id' ORDER BY `date` DESC LIMIT $page_start,$perpage") or die(mysql_error());

What is the $data object?

Orc · 02-07-2008, 04:08 PM

Pagination is frustrating. >.<

Update: So far, it's still buggy. :/ If you put in /?page=1 it shows it all, if you put in 2 it shows maximum 3 is how I want it. -_-

Orc · 02-07-2008, 04:28 PM

I've tried 5 tutorials, none work. Heh, for me, I'm starting to hate this. -_-

Village Idiot · 02-07-2008, 05:18 PM

Quote:

Originally Posted by Orc

What is the $data object?

$data is an isntance of this

PHP Code:


			
class input
{
    private function sql_safe($value) 
    {
        // Stripslashes
        if (get_magic_quotes_gpc()) 
        {
            $value = stripslashes($value);
        }

        // Quote if not integer
        if (!is_numeric($value) || $value[0] == '0')
        {
            $value = mysql_real_escape_string($value);
        }
        return $value;
    }

    function get($var)
    {
        $var = $_GET[$var];
        $var = $this->sql_safe($var);
        return $var;
    }

    function post($var)
    {
        $var = $_POST[$var];
        $var = $this->sql_safe($var);
        return $var;
    }

    function cookie($var)
    {
        $var = $_COOKIE[$var];
        $var = $this->sql_safe($var);
        return $var;
    }
}

RobertK · 02-07-2008, 05:28 PM

Hey, I like that class. I have something like that in a bit of a scattered procedural form. Thought about turning it into a class, after seeing your first reply and thinking about my own project.

One question though, if you'll humor me.

Why write this instead of the equivalent shorthand? Is the first faster, and if so why?

PHP Code:


			
    function get($var)

    {

        // This

        $var = $_GET[$var];

        $var = $this->sql_safe($var);

        return $var;

        // versus this

        return $this->sql_safe($_GET[$var]);

    }

Village Idiot · 02-07-2008, 07:26 PM

In case I ever want to change anything in the way it passes the get data on for a specific purpose. Just leaves the door open so I wont have to make any more modifications then I have to.

As for speed, I dont know, but the difference would be negligible at best.

Orc · 02-07-2008, 07:30 PM

I lack MySQL skills, so I'll just keep trying till I at least get something right. :P

RobertK · 02-07-2008, 08:04 PM

I was wondering, since the only reason I could figure was either flexibility or speed. And I doubted the latter was the one. Exactly why I asked. ;)

dylanfm · 02-07-2008, 11:38 PM

I'm used to postgres which has LIMIT and OFFSET clauses.
Did you get it sorted out orc?

Orc · 02-08-2008, 12:14 AM

No, though I got 1 and Next up, and I put I got prev working, though it's stuck on page 1 so it doesnt show prev, but I still cannot get the query right. :[ I have two queries, one of them is count and the other is the regular query which displays all the news. If I try to put a limit with that using $max or $num ( $page - $max * $max ) then I would get an error, I looked at the error using mysql_error(), and nothing is there. so yeah, it's not working :P

Code:

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 '''' at line 1

I tried both methods:

Code:

quotes . foo,bar . quotes

and

Code:

noquotes foo,bar noquotes

Still doesnt show up..

dylanfm · 02-08-2008, 12:41 AM

I'll run through a way I do it, but I'm using postgres so the SQL is slightly different. This probably isn't the best way by any means, but it works for me.

First you want to get your limit and offset sorted:

PHP Code:

$limit = 3; $offset = (is_numeric($_GET['s'])) ? $_GET['s'] : null ;

This assumes that your start number is in the url as 's'

Do your query. You can change your SQL syntax to MySQL if you need (really this is only replacing LIMIT $limit OFFSET $offset with the mysql equivalent).

PHP Code:

$query = "SELECT id FROM blog_post ORDER BY created_at DESC"; $query .= (is_numeric($start)) ? ' OFFSET '.$offset : '' ; $query .= (is_numeric($limit)) ? ' LIMIT '.$limit : '' ; $result = pg_query($query); #Feed the results into an array called $blog_posts

I also want the total number of blog_posts.
So, just do the above query without any OFFSET or LIMIT

PHP Code:

$number_of_posts = .....

This is the code I use for the pagination thing. I'm not bothering with numbers at the moment, just next and previous. Numbers aren't hard to do though.

PHP Code:

<ul class="pagination"> <?php $date = (is_numeric($mm) && is_numeric($yyyy)) ? $yyyy.'/'.$mm.'/' : null ; if ($offset >= $limit): ?> <li><a href="/blog/<?=$date?>?s=<?php echo $offset - $limit; ?>" title="View the previous page of blog posts">Newer posts</a></li> <?php endif ?> <?php if ((($offset + $limit) < $number_of_posts) && ($number_of_posts > count($blog_posts))): ?> <li><a href="/blog/<?=$date?>?s=<?php echo $offset + $limit; ?>" title="View the next page of blog posts">Earlier posts</a></li> <?php endif ?> </ul>

I hope this is of some help. I wouldn't be suprised if I made a mistake above. Sorry about the weird 1,1,1 list!

Orc · 02-08-2008, 01:51 AM

I'm always getting a negative number!! '-3,3'

PHP Code:


			
 $num = $page * $max - $max;

This is the code.