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