Need Help with Code

ghop2003

Hi. I'm really having trouble getting some code working. I'm trying to output all the items in a table and sort them by a column. I'm also trying to get paging to work with this. Here is the code that I think is relevant (I'll post the whole code at the end):

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

// Performing SQL query
$cat = $_GET['cat'];
$val = $_GET['val'];
$sort = $_GET['sort'];
if ($sort==NULL)
  $sort = 'title';

$query  = 'SELECT title, year, platform, genre, owner, review_url FROM games LIMIT $offset, $rowsPerPage';

if ($cat==NULL or $val==NULL)
  $query .= ' ORDER BY ';
else
  $query .= ' WHERE '.$cat.' LIKE \''.$val.'\' ORDER BY ';

$query .= $sort;

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

Here is the error I'm getting:

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 '$offset, $rowsPerPage ORDER BY title' at line 1

I'm somewhat of a newbie with php and mysql. I'm okay at manipulating code but I can't really write my own. I didn't actually write any of this code. Here's the whole code in case anyone needs to see it:

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

// Performing SQL query
$cat = $_GET['cat'];
$val = $_GET['val'];
$sort = $_GET['sort'];
if ($sort==NULL)
  $sort = 'title';

$query  = 'SELECT title, year, platform, genre, owner, review_url FROM games LIMIT $offset, $rowsPerPage';

if ($cat==NULL or $val==NULL)
  $query .= ' ORDER BY ';
else
  $query .= ' WHERE '.$cat.' LIKE \''.$val.'\' ORDER BY ';

$query .= $sort;

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

//Printing results in HTML
echo "Sort by: <a href=\"/games/index.php?cat=$cat&val=$val&sort=title\">Title</a> | <a 

href=\"/games/index.php?cat=$cat&val=$val&sort=year\">Year</a> | <a 

href=\"/games/index.php?cat=$cat&val=$val&sort=platform\">Platform</a> | <a 

href=\"/games/index.php?cat=$cat&val=$val&sort=genre\">Genre</a> | <a 

href=\"/games/index.php?cat=$cat&val=$val&sort=owner\">Owner</a><br>\n";
echo "<table width=100% border=0 cellspacing=3 cellpadding=0>\n";

// print the resulting query
while ($line = mysql_fetch_array($result, MYSQL_BOTH))
{
    echo "\t<tr>\n";
    echo "\t\t<td><a href='$line[review_url]'>$line[title]</a></td>\n";
    echo "\t\t<td>$line[year]</td>\n";
    echo "\t\t<td>$line[platform]</td>\n";
    echo "\t\t<td>$line[genre]</td>\n";
    echo "\t\t<td>$line[owner]</td>\n";
    echo "\t</tr>\n";
}

echo "</table>\n";
echo "<hr align=left width=60% size=1 color=#303030>\n";
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(title) AS numrows FROM games";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages do we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
    $page = $pageNum - 1;
    $first = " <a href=\"$self?page=1\">Page 1</a> ";
    $prev = " <a href=\"$self?page=$page\">Back</a> ";

} 
else
{
    $prev  = ' '; // we're on page one, don't enable 'previous' link
    $first = ' '; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
    $page = $pageNum + 1;
    $next = " <a href=\"$self?page=$page\">Next</a> ";

$last = " <a href=\"$self?page=$maxPage\">Page $maxPage</a> ";
} 
else
{
    $next = ' '; // we're on the last page, don't enable 'next' link
    $last = ' '; // nor 'last page' link
}

// print the page navigation link
echo $first . $prev . " Page <strong>$pageNum</strong> of <strong>$maxPage</strong> " . $next . $last;

ghop2003

I figured it out. I thought I'd post in case anyone else has a similar problem. This section of the code:

$query  = 'SELECT title, year, platform, genre, owner, review_url FROM games LIMIT $offset, $rowsPerPage';

if ($cat==NULL or $val==NULL)
  $query .= ' ORDER BY ';
else
  $query .= ' WHERE '.$cat.' LIKE \''.$val.'\' ORDER BY ';

$query .= $sort;

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

needs to be changed to this:

$query  = "SELECT title, year, platform, genre, owner, review_url FROM games ";

if ($cat==NULL or $val==NULL)
  $query .= " ORDER BY ";
else
  $query .= " WHERE '.$cat.' LIKE \''.$val.'\' ORDER BY ";

$query .= $sort;

$pagingQuery = " LIMIT $offset, $rowsPerPage ";

$result = mysql_query($query . $pagingQuery) or die('Query failed: ' . mysql_error());

Like I said before, I don't really know PHP all that well, but I'm guessing the problem had something to do with the ordering of the query (as well as the syntax). I saw this in an example for different php code and gave it a try. It worked.

halojoy

MySQL manual
http://dev.mysql.com/doc/refman/4.1/en/index.html

might have something for sql 'ORDER BY' syntax
I guess you use mysql 4

ghop2003

Thanks for the link halojoy but I already figured out the problem.