[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Syntax error Mysql for LIMIT

Posted on 2009-02-20
6
Medium Priority
?
286 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:pmagony
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

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

please double check.
0
 
LVL 9

Author Comment

by:pmagony
ID: 23697022
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 23697074
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Author Comment

by:pmagony
ID: 23697589
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
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 1000 total points
ID: 23697689
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
 
LVL 9

Author Comment

by:pmagony
ID: 23697737
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month20 days, 8 hours left to enroll

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question