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
LVL 9
pmagonyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that won't work. you need to do:

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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this looks like $query_MEM is not a sql string, but the return value of mysql_query()

please double check.
0
 
pmagonyAuthor Commented:
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);
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
pmagonyAuthor Commented:
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

0
 
Roger BaklundCommented:
Try replacing lines 27-30 in the snippet with this:
$query_OTA = mysql_query("SELECT * FROM news WHERE cat = 0 ORDER BY date DESC");
$tmp_query = "SELECT * FROM news WHERE cat = 1 ORDER BY date DESC";
$query_limit_Articles = sprintf("%s LIMIT %d, %d", $tmp_query, $startRow_Articles, $maxRows_Articles);
$query_MEM = mysql_query($tmp_query);
$query_ALL = mysql_query("SELECT * FROM news");

Open in new window

0
 
pmagonyAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.