Error using LIMIT in MySQL

[csharplearner]

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:

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.

[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?

[cwarn23]

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

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;

[csharplearner]

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]

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.

[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.

[cwarn23]

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:

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:

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:

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:

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