Link to home
Start Free TrialLog in
Avatar of pmagony
pmagony

asked on

Syntax error Mysql for LIMIT

Hi, I'm getting a syntax error for the following line:

$query_limit_Articles = sprintf("%s LIMIT %d, %d", $query_MEM, $startRow_Articles, $maxRows_Articles);

Error:

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 'Resource id #8 LIMIT 0, 5' at line 1

My MySQL version is 5.0.67

I'm not sure what to do different.

Any help is appreciated, thanks.

I borrowed
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

this looks like $query_MEM is not a sql string, but the return value of mysql_query()

please double check.
Avatar of pmagony
pmagony

ASKER

yes, correct:

$query_MEM = mysql_query("SELECT * FROM news WHERE cat = 1 ORDER BY date DESC");
$query_limit_Articles = sprintf("%s LIMIT %d, %d", $query_MEM, $startRow_Articles, $maxRows_Articles);
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pmagony

ASKER

Well, let's do this because when I changed it to your suggestion, it produced another error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in line 177

I'm trying to pull two different queries onto the page and I'm trying to incorporate recordset paging.
<? 
/* ************************************** *  
 * Set pagination settings, and default page
 * ************************************** */
$maxRows_Articles = 5;
$pageNum_Articles = 0;
 
/* *************************************************** *  
 * If page number has been set in a GET parameter (e.g. in the url),
 * use that instead.
 * *************************************************** */
 
if (isset($_GET['pageNum_Articles'])) {
  $pageNum_Articles = $_GET['pageNum_Articles'];
}
 
/* ******************************************** *  
 * Calculate the first record (product of page number and records per page)
 * ********************************************* */
 
$startRow_Articles = $pageNum_Articles * $maxRows_Articles;
 
/* ******************************************** *  
 * Create the query for all articles (result) and
 * the query with limit clause (results)
 * ********************************************* */
$query_OTA = mysql_query("SELECT * FROM news WHERE cat = 0 ORDER BY date DESC");
$query_MEM = "SELECT * FROM news WHERE cat = 1 ORDER BY date DESC";
$query_limit_Articles = sprintf("%s LIMIT %d, %d", $query_MEM, $startRow_Articles, $maxRows_Articles);
$query_ALL = mysql_query("SELECT * FROM news");
 
/* ******************************************** *  
 * Execute the query with pagination
 * ********************************************* */
$Articles = mysql_query($query_limit_Articles, $link) or die(mysql_error());
$row_Articles = mysql_fetch_assoc($Articles);
 
while($row = mysql_fetch_array($query_OTA))
				  {
  					echo "<li><a target=\"_blank\" href=\"http://www.dummy.com/path/".$row['url']."\">". $row['title'] . "</a> - <Span style=\"font-size:11px;\">".$row['date']."</span></li>";
  				}
		?>
        </ul>
 
        <h1>Member News</h1>
        <ul>
		<?
			while($row = mysql_fetch_array($query_MEM))
				  {
  					echo "<li><a target=\"_blank\" href=\"http://www.dummy.com/path/".$row['url']."\">". $row['title'] . "</a> - <Span style=\"font-size:11px;\">".$row['date']."</span></li>";
  				}
?>
</ul>
<?		
/* ******************************************** *  
 * get total number of rows across all pages
 * ********************************************* */
if (isset($_GET['totalRows_Articles'])) {
  $totalRows_Articles = $_GET['totalRows_Articles'];
} else {
  $all_Articles = mysql_query($query_ALL);
  $totalRows_Articles = @mysql_num_rows($all_Articles);
}
$totalPages_Articles = ceil($totalRows_Articles/$maxRows_Articles)-1;
 
?>
 
        <!--  ************************************
              PAGINATION SECTION (NAVIGATOR)
       ************************************     -->
    
    <table border="0">
      <tr>
 <!--  ***************************************
              SHOW PAGE COUNT AND MAX PAGE 
      ***************************************     -->
 
        <td><p>Records <?php echo ($startRow_Articles + 1) ?> to <?php echo min($startRow_Articles + $maxRows_Articles, $totalRows_Articles) ?> of <?php echo $totalRows_Articles ?></p></td>
 
<!--  ***************************************
              SHOW FIRST BUTTON
      ***************************************     -->
 
        <td><?php if ($pageNum_Articles > 0) { // Show if not first page ?>
              <a href="<?php printf("%s?pageNum_Articles=%d%s", $currentPage, 0, $queryString_Articles); ?>"><<</a>
              <?php } // Show if not first page ?>
        </td>
 
 
<!--  ***************************************
              SHOW PREV BUTTON
      ***************************************     -->
 
 
        <td><?php if ($pageNum_Articles > 0) { // Show if not first page ?>
              <a href="<?php printf("%s?pageNum_Articles=%d%s", $currentPage, max(0, $pageNum_Articles - 1), $queryString_Articles); ?>"><</a>
              <?php } // Show if not first page ?>
        </td>
 
<!--  ***************************************
              SHOW NEXT BUTTON
      ***************************************     -->
 
        <td><?php if ($pageNum_Articles < $totalPages_Articles) { // Show if not last page ?>
              <a href="<?php printf("%s?pageNum_Articles=%d%s", $currentPage, min($totalPages_Articles, $pageNum_Articles + 1), $queryString_Articles); ?>">></a>
              <?php } // Show if not last page ?>
        </td>
 
<!--  ***************************************
              SHOW LAST BUTTON
      ***************************************     -->
 
        <td><?php if ($pageNum_Articles < $totalPages_Articles) { // Show if not last page ?>
              <a href="<?php printf("%s?pageNum_Articles=%d%s", $currentPage, $totalPages_Articles, $queryString_Articles); ?>">>></a>
              <?php } // Show if not last page ?>
        </td>
      </tr>
    </table>
 
<!--  ************************************
              END PAGINATION SECTION 
      ************************************     -->

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pmagony

ASKER

Right on, that fixed the error issue.  But I'm still having trouble showing recordset.  Honestly, I'm not even looking to have a full blown paging menu.  I just want to be able to put a link that says "view all" and have it pull out all records.