TalkPHP
 
 
Account Login
Latest Articles
» The basic usage of PHPTAL, a XML/XHTML template library for PHP
» Vulnerable methods and the areas they are commonly trusted in.
» Simple way to protect a form from bot
» The Basics On: How Session Stealing Works
» How to keep your forms from double posting data
Advertisement
Associates
Associates
techtuts Darkmindz
CSS Tutorials Tutorialsphere.com - Free Online Tutorials
Boston PHP SurfnLearn
Reply
 
LinkBack Thread Tools Display Modes
Old 02-07-2008, 04:06 AM   #1 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default MySQL Error with Pagination

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! :(
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 09:04 AM   #2 (permalink)
The Wanderer
 
dylanfm's Avatar
 
Join Date: Jan 2008
Location: Australia
Posts: 14
Thanks: 1
dylanfm is on a distinguished road
Default

Try it without quotes around news
Send a message via ICQ to dylanfm
dylanfm is offline  
Reply With Quote
Old 02-07-2008, 10:51 AM   #3 (permalink)
The Frequenter
 
ReSpawN's Avatar
 
Join Date: Nov 2007
Location: Netherlands
Posts: 445
Thanks: 49
ReSpawN is on a distinguished road
Default

`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.'"'
__________________
"Life is a bitch, take that bitch on a ride"
Send a message via MSN to ReSpawN
ReSpawN is offline  
Reply With Quote
The Following User Says Thank You to ReSpawN For This Useful Post:
Orc (02-07-2008)
Old 02-07-2008, 11:48 AM   #4 (permalink)
Moderateur
RegEx Guru PHP Guru Top Contributor Advanced Programmer 
 
Salathe's Avatar
 
Join Date: Apr 2007
Posts: 701
Thanks: 2
Salathe is on a distinguished road
Default

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
__________________
Salathe is offline  
Reply With Quote
The Following User Says Thank You to Salathe For This Useful Post:
Orc (02-07-2008)
Old 02-07-2008, 02:42 PM   #5 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default

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. -_-
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 03:05 PM   #6 (permalink)
The Gregarious
Top Contributor 
 
Village Idiot's Avatar
 
Join Date: Sep 2007
Posts: 566
Thanks: 15
Village Idiot is on a distinguished road
Default

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>
__________________
There are two ways to write bug-free code, only the third one works.
Village Idiot is offline  
Reply With Quote
Old 02-07-2008, 03:07 PM   #7 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default

Quote:
Originally Posted by Village Idiot View Post
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
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 03:30 PM   #8 (permalink)
The Gregarious
Top Contributor 
 
Village Idiot's Avatar
 
Join Date: Sep 2007
Posts: 566
Thanks: 15
Village Idiot is on a distinguished road
Default

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()); 
__________________
There are two ways to write bug-free code, only the third one works.
Village Idiot is offline  
Reply With Quote
Old 02-07-2008, 03:39 PM   #9 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default

Quote:
Originally Posted by Village Idiot View Post
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?
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 04:08 PM   #10 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default

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. -_-
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 04:28 PM   #11 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default

I've tried 5 tutorials, none work. Heh, for me, I'm starting to hate this. -_-
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 05:18 PM   #12 (permalink)
The Gregarious
Top Contributor 
 
Village Idiot's Avatar
 
Join Date: Sep 2007
Posts: 566
Thanks: 15
Village Idiot is on a distinguished road
Default

Quote:
Originally Posted by Orc View Post
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;
    }

__________________
There are two ways to write bug-free code, only the third one works.
Village Idiot is offline  
Reply With Quote
The Following User Says Thank You to Village Idiot For This Useful Post:
RobertK (02-07-2008)
Old 02-07-2008, 05:28 PM   #13 (permalink)
The Addict
Top Contributor Good Samaritan 
 
Join Date: Jan 2008
Location: USA
Posts: 218
Thanks: 16
RobertK is on a distinguished road
Default

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]);
    } 
__________________
Programmers are in a race with the Universe to create bigger and better idiot-proof programs, while the Universe is trying to create bigger and better idiots. So far the Universe is winning. - Rich Cook
RobertK is offline  
Reply With Quote
Old 02-07-2008, 07:26 PM   #14 (permalink)
The Gregarious
Top Contributor 
 
Village Idiot's Avatar
 
Join Date: Sep 2007
Posts: 566
Thanks: 15
Village Idiot is on a distinguished road
Default

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.
__________________
There are two ways to write bug-free code, only the third one works.
Village Idiot is offline  
Reply With Quote
Old 02-07-2008, 07:30 PM   #15 (permalink)
Orc
The Prestige
 
Orc's Avatar
 
Join Date: Dec 2007
Location: On your Hard Drive, hiding like a Virus
Posts: 818
Thanks: 163
Orc is on a distinguished road
Default

I lack MySQL skills, so I'll just keep trying till I at least get something right. :P
__________________
Wax on, Wax off
Orc is offline  
Reply With Quote
Old 02-07-2008, 08:04 PM   #16 (permalink)