Ad:

PHP Discussion Thread
Unsolved
Views: 1229

May 30th, 2009

Error using LIMIT in MySQL

Expand Post »

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:

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.

Similar Threads

MySQL syntax error 1064 in MySQL
The "LIMIT" parameter in queries in PHP
error in connecting MySql and PHPBB help me..... in MySQL
help on error handling in mysql ,in primary key in MySQL
About LIMIT in mysql in PHP

csharplearner

Reputation Points: 11

Solved Threads: 3

Junior Poster in Training

Offline

99 posts
since Nov 2008

Permalink

May 31st, 2009

Re: Error using LIMIT in MySQL

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?

OmniX

Reputation Points: 31

Solved Threads: 10

Practically a Master Poster

Offline

652 posts
since Dec 2007

Permalink

May 31st, 2009

Re: Error using LIMIT in MySQL

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

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;

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

Permalink

May 31st, 2009

Re: Error using LIMIT in MySQL

Click to Expand / Collapse Quote 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.

csharplearner

Reputation Points: 11

Solved Threads: 3

Junior Poster in Training

Offline

99 posts
since Nov 2008

Permalink

May 31st, 2009

Re: Error using LIMIT in MySQL

Click to Expand / Collapse Quote 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?

csharplearner

Reputation Points: 11

Solved Threads: 3

Junior Poster in Training

Offline

99 posts
since Nov 2008

Permalink

May 31st, 2009

Re: Error using LIMIT in MySQL

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.

Josh Connerty

Reputation Points: 31

Solved Threads: 27

Unverified User

Offline

342 posts
since Apr 2009

Permalink

Jun 1st, 2009

Re: Error using LIMIT in MySQL

Click to Expand / Collapse Quote 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:

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:

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:

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:

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());

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: can we achieve 3-tier architecture with out frameworks in php?

Next Thread in PHP Forum Timeline: Trouble grasping OOP concepts

DaniWeb Message

Cancel Changes

User Name
Password