DaniWeb Forum Index > Web Development > PHP

Error using LIMIT in MySQL

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 99

Reputation: csharplearner is an unknown quantity at this point

Solved Threads: 3

csharplearner csharplearner is offline

Offline

Junior Poster in Training

Error using LIMIT in MySQL

May 30th, 2009

Hi
I trying to implement pagination using LIMIT in MySQL. My problem is it works good for the first 3 results only and for later results it shows error saying check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 3, 3' at line 1

I couldnt get where i am going wrong here. Please look at my code below:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
if($_GET["c"])
{ $k = $_GET["k"] ; $cnt = $k; $k = $k + 3;  }
else
{$cnt = 0; $k = 3;}
 
$table = '<table  >';
 
mysql_connect(" ") ;
mysql_select_db(" "); 
$result = mysql_query("SELECT var1 FROM $tab1 LIMIT $cnt, 3") or die(mysql_error());  
$i = 0;
while($row = mysql_fetch_array( $result ))
{
       $var1 = $row['name'];
if ( $i == 3 ) {
            $table .= '</tr><tr>';
            $i = 0;
        }
 
mysql_connect(" ") ;
mysql_select_db(" ");
$result1 = mysql_query("SELECT * FROM tab2 WHERE var2= '$var1'");
$row = mysql_fetch_array( $result1 );
$var2 = $row['var2'];
        $table .= "<td > blah blah blah";
 
$i++; 
}
 
$table .= "<tr><td  ><a href=link.php?c=next&k=".$k.">next</a></td></tr>";
echo $table;
if($_GET["c"])
{ $k = $_GET["k"] ; $cnt = $k; $k = $k + 3;  }
else
{$cnt = 0; $k = 3;}
 
$table = '<table  >';
	
mysql_connect(" ") ;
mysql_select_db(" "); 
$result = mysql_query("SELECT var1 FROM $tab1 LIMIT $cnt, 3") or die(mysql_error());  
$i = 0;
while($row = mysql_fetch_array( $result ))
{
       $var1 = $row['name'];
if ( $i == 3 ) {
            $table .= '</tr><tr>';
            $i = 0;
        }

mysql_connect(" ") ;
mysql_select_db(" ");
$result1 = mysql_query("SELECT * FROM tab2 WHERE var2= '$var1'");
$row = mysql_fetch_array( $result1 );
$var2 = $row['var2'];
        $table .= "<td > blah blah blah";

$i++; 
}

$table .= "<tr><td  ><a href=link.php?c=next&k=".$k.">next</a></td></tr>";
echo $table;

All the variables are retreived properly the only problem seems to be somewhere with LIMIT statement. Please let me know where am i going wrong.

Thank you in advance.

Last edited by csharplearner; May 30th, 2009 at 11:49 pm.

•

Join Date: Dec 2007

Posts: 608

Reputation: OmniX is an unknown quantity at this point

Solved Threads: 8

OmniX

Offline

Practically a Master Poster

Re: Error using LIMIT in MySQL

May 31st, 2009

You stated your sql statement is LIMIT 3, 3.
That line of code will only gather 3 results try changing it to LIMIT 3, 4 and see if that works?

"You never stop learning." - OmniX

•

Join Date: Sep 2007

Posts: 1,449

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 135

cwarn23

Offline

Nearly a Posting Virtuoso

Re: Error using LIMIT in MySQL

May 31st, 2009

There are a few bugs and security holes in that script. So try the following:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
mysql_connect(" ") ; //add variables in function
mysql_select_db(" "); //add variables in function
 
if(!empty($_GET["c"]) && !preg_match('/[^0-9]/',$_GET['k']))
{ $k = $_GET["k"] ; $cnt = $k; $k += 3;  }
else
{$cnt = 0; $k = 3;}
 
$table = '<table  >';
 
 
$result = mysql_query('SELECT var1 FROM '.mysql_real_escape_string($tab1).' LIMIT '.mysql_real_escape_string($cnt).', 3') or die(mysql_error());  
 
//for loops are faster than while loops when used properly.
for ($i=0;$row = mysql_fetch_assoc( $result );$i++)
{
       $var1 = $row['name'];
if ( $i == 3 ) {
            $table .= '</tr><tr>';
            $i = 0;
        }
 
 
$result1 = mysql_query('SELECT * FROM tab2 WHERE var2= "'.mysql_real_escape_string($var1).'"');
$row = mysql_fetch_assoc( $result1 );
$var2 = $row['var2'];
        $table .= "<td > blah blah blah";
 
}
 
$table .= "<tr><td  ><a href=link.php?c=next&k=".$k.">next</a></td></tr>";
echo $table;
mysql_connect(" ") ; //add variables in function
mysql_select_db(" "); //add variables in function

if(!empty($_GET["c"]) && !preg_match('/[^0-9]/',$_GET['k']))
{ $k = $_GET["k"] ; $cnt = $k; $k += 3;  }
else
{$cnt = 0; $k = 3;}
 
$table = '<table  >';
 

$result = mysql_query('SELECT var1 FROM '.mysql_real_escape_string($tab1).' LIMIT '.mysql_real_escape_string($cnt).', 3') or die(mysql_error());  

//for loops are faster than while loops when used properly.
for ($i=0;$row = mysql_fetch_assoc( $result );$i++)
{
       $var1 = $row['name'];
if ( $i == 3 ) {
            $table .= '</tr><tr>';
            $i = 0;
        }
 

$result1 = mysql_query('SELECT * FROM tab2 WHERE var2= "'.mysql_real_escape_string($var1).'"');
$row = mysql_fetch_assoc( $result1 );
$var2 = $row['var2'];
        $table .= "<td > blah blah blah";
 
}
 
$table .= "<tr><td  ><a href=link.php?c=next&k=".$k.">next</a></td></tr>";
echo $table;

Try not to bump 10 year old threads as it can be really annoying.
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u

•

Join Date: Nov 2008

Posts: 99

Reputation: csharplearner is an unknown quantity at this point

Solved Threads: 3

csharplearner csharplearner is offline

Offline

Junior Poster in Training

Re: Error using LIMIT in MySQL

May 31st, 2009

•

Originally Posted by OmniX

You stated your sql statement is LIMIT 3, 3.
That line of code will only gather 3 results try changing it to LIMIT 3, 4 and see if that works?

Thank you for your time and reply.
I couldnt understand why should i change it to '4' because after LIMIT
the first variable is offset and next one is number of records we want to display right?! Then cant i put whatever number of records i want changing the offset value.
Please explain if i am not following.
Thank you.

•

Join Date: Nov 2008

Posts: 99

Reputation: csharplearner is an unknown quantity at this point

Solved Threads: 3

csharplearner csharplearner is offline

Offline

Junior Poster in Training

Re: Error using LIMIT in MySQL

May 31st, 2009

•

Originally Posted by OmniX

You stated your sql statement is LIMIT 3, 3.
That line of code will only gather 3 results try changing it to LIMIT 3, 4 and see if that works?

•

Join Date: Apr 2009

Posts: 340

Reputation: Josh Connerty is an unknown quantity at this point

Solved Threads: 26

Josh Connerty Josh Connerty is offline

Offline

Posting Whiz

Re: Error using LIMIT in MySQL

May 31st, 2009

I always thought the first was the start recoed and the second was the last record.

I think 3,3 is wrong run an if statement to make sure the count variable is not equal to or greater to 3 this would confuse MySql's brain.

Posts should be like mini-skirts, long enough to cover enough, but not too long that you cover too much.

My Liveperson: http://liveperson.com/josh-connerty/

•

Join Date: Sep 2007

Posts: 1,449

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 135

cwarn23

Offline

Nearly a Posting Virtuoso

Re: Error using LIMIT in MySQL

Jun 1st, 2009

•

Originally Posted by Josh Connerty

I always thought the first was the start recoed and the second was the last record.

I think 3,3 is wrong run an if statement to make sure the count variable is not equal to or greater to 3 this would confuse MySql's brain.

Below is a direct quote from the select syntax of the official mysql manual:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
LIMIT {[offset,] row_count | row_count OFFSET offset}
LIMIT {[offset,] row_count | row_count OFFSET offset}

As you can see, it makes an if statement. It can be broken down to two parts. The first is:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

LIMIT {row_count}

The above statement make just the row count and note that when something is in brackets it is an optional parameter optional. However, if you were to fill in the optional parameter, since there are now two parameters, the if statement would change to the following where the offset is at the end:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

LIMIT {row_count OFFSET offset}

And now for the part that is confusing most people in this thread. An offset is never relative to the row_count. So say you wanted to limit from rows 6 to row 8 you would use LIMIT 6, 2 as you are telling mysql how many rows to count on after six. That's what offset means in any language weather it's mysql, graphics, english etc.

So to explain what LIMIT 3, 3 does. It will limit from result 3 to result 6 as it has counted three rows from result 3. I would have to say that if anything was the error it would be the table name.
As a test, try replacing line 12 of my previously posted script with the following and see what query it reports back:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$result = mysql_query('SELECT var1 FROM `'.mysql_real_escape_string($tab1).'` LIMIT '.mysql_real_escape_string($cnt).', 3')
or die('SELECT var1 FROM `'.mysql_real_escape_string($tab1).'` LIMIT '.mysql_real_escape_string($cnt).', 3<hr>'.mysql_error());
$result = mysql_query('SELECT var1 FROM `'.mysql_real_escape_string($tab1).'` LIMIT '.mysql_real_escape_string($cnt).', 3')
or die('SELECT var1 FROM `'.mysql_real_escape_string($tab1).'` LIMIT '.mysql_real_escape_string($cnt).', 3<hr>'.mysql_error());