Query failed: You have an error in your SQL syntax;

[LucienFB]

This is what shows up in the browser:

 

Connected to localhost

Connected to test_db

Query failed: 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 'LIMIT 48, 4' at line 1 

FIRST | PREVIOUS ( Page 13 of 13 ) NEXT | LAST Total number of user_result: 52

 

 

I can't see what's wrong with the code.

I got this code at

http://www.tutorialized.com/view/tutorial/Basic-PHP-MySQL-Pagination/36629

 

 

 

Here is the entire code:

 

<?php

/*1) MySQL Database Connection

 

If you are reading this tutorial, I assume you already know how to make a MySQL database connection from PHP. In case you do not know how, here is the code…

 

*/

 

 

//////////////////////////////////////////

//// MySQL Database Connection ///////////

//////////////////////////////////////////

$host = "localhost";

$user = "LucienFB";

$db_name= "test_db";

$pass= "XXXXXX";

 

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());

echo ("Connected to " . $host);

mysql_select_db($db_name, $conn) or die(mysql_error());

echo ("<br/>Connected to " . $db_name . "<br/>");

 

/* 2) Get page number, if any

 

First, you need to obtain the page number you are in. If the user hasn’t clicked in any of the page links then it

will bring them to page one. Notice that the variable $pageno will only contain numeric characters assigned to it.

In case someone is playing around with the URL or trying an injection attack, the code will exit and the code won’t be executed.

*/

 

/////////////////////////////////////////

////// Prevent Injection Attack /////////

////// Set $pageno variable /////////////

/////////////////////////////////////////

if(isset($_GET['pageno']))

{

    if(!is_numeric($_GET['pageno']))

    {

        echo 'Error.';

        exit();

    }

    $pageno = $_GET['pageno'];

}

else

{

    $pageno=1;

}

 

/* 3) Calculate how many rows or records in total the query will output

 

$numrows variable contains the total number of rows. If the query returns no results then you can output a customized notice.

 

If the query does return rows you now have the total number of rows at the $numrows variable. In case you want to display

“There are 150 products in total”, you can do it by echoing the $numrows variable. We will do this later in the exercise.

*/

 

 

$queryCount = 'SELECT count(*) FROM user_result';

$resultCount = mysql_query($queryCount);

$fetch_row = mysql_fetch_row($resultCount);

$numrows = $fetch_row[0];

 

 

 

 

 

// if there is no results

if($numrows == 0)

{

    echo 'Sorry, we have no user_result yet.';

    exit();

}

 

/* 4) Calculate number of pages

 

Now let’s get the last page number. How many rows do you want per page? Just for the exercise purposes, I chose 4.

Of course, you probably have dozens or hundreds of rows in your table, so you might want to chose 10, 15 or 20. Take your pick.

*/

 

$perPage = 4;

$lastpage = ceil($numrows/$perPage);

$pageno = (int)$pageno;

if($pageno<1)

{

    $pageno=1;

}

elseif($pageno>$lastpage)

{

    $pageno=$lastpage;

}

 

/* 5) Generate page links

 

We are going to work on the links.

 

There will be 4:

 

FIRST

 

PREVIOUS

 

NEXT

 

LAST

 

 

The links that do not apply will be grayed out. For example if you are in page one, the FIRST link will be grayed out.

Or if you are in the last page, then the LAST link will be grayed out. Also, this script will let us know what page we are in.

 

*/

 

 

// ----- PAGE LINKS -----

if($pageno==1)

{

    $pages .= 'FIRST | PREVIOUS ';

}

else

{

    $pages .= "<a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> | ";

    $prevpage=$pageno-1;

    $pages .= " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREVIOUS</a> ";

}

$pages .= ' ( Page '.$pageno.' of '.$lastpage.' ) ';

if($pageno==$lastpage)

{

    $pages .= ' NEXT | LAST ';

}

else

{

    $nextpage = $pageno+1;

    $pages .= " <a href='".$_SERVER['PHP_SELF']."?pageno=$nextpage'>NEXT</a> | ";

    $pages .= " <a href='".$_SERVER['PHP_SELF']."?pageno=$lastpage'>LAST</a>";

}

 

/* 6) Calculate LIMIT clause for the MySQL query

 

The range of query results are loaded into variable $limit so we add it to the query and populate its results.

Then we display the page links and the total number of products.

 

*/

 

$limit=' LIMIT '.($pageno-1)*$perPage.', '.$perPage;

 

 

//7) Run the query, echo the results and echo the page links

 

$query = $query.$limit;

$result = mysql_query($query);

if(!$result)

{

    echo 'Query failed: '.mysql_error();

}

while($row = mysql_fetch_array($result))

{

    echo $row['user_id'].' '.$row['user_name'].'<br />';

}

 

echo '<div style="width:100%;color:#00FF00; text-align: center; font-size: smaller;">'.$pages.'</div>';

echo 'Total number of user_result: '.$numrows;

error_reporting(E_ALL);

 

?>

[revraz]

Where are you populating the $query variable before this part?

 

 

//7) Run the query, echo the results and echo the page links

 

$query = $query.$limit;

$result = mysql_query($query);

[LucienFB]

I don't know what this means:

 

"Where are you populating the $query variable before this part?"

 

BTW MySQl = Version 6.05

 

Please explain further.

 

Thanks,

Lucien

 

[radi8]

What he means is, on the line defined at:

<?php
//7) Run the query, echo the results and echo the page links
$query = $query.$limit;
$result = mysql_query($query);
if(!$result)
{
   echo 'Query failed: '.mysql_error();
}
while($row = mysql_fetch_array($result))
{
   echo $row['user_id'].' '.$row['user_name'].'<br />';
}
?>

 

The $query variable is not being initialized. This is important because the query being p[assed to the server is then just ' LlIMIT 48,4', which is invalid.

 

Somewhere in the script, the last query is not being defined, was accidentally deleted, or was missed by the developer (unlikely). This is one the problems with cut/paste scripts, you are at the mercy of the developer.

 

You probably need a query like:

<?php
//7) Run the query, echo the results and echo the page links
$query="SELECT * FROM user_result".$limit;
$result = mysql_query($query);
if(!$result)
{
   echo 'Query failed: '.mysql_error();
}
while($row = mysql_fetch_array($result))
{
   echo $row['user_id'].' '.$row['user_name'].'<br />';
}?>

[LucienFB]

Thanks,

 

Works Like a charm!

 

Lucien