Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Unanswered: URGENT! Help required with SQL syntax error

    Hi all,

    Really strange thing. I'm using Dreamweaver CS5.5 to build a site for uni. I am creating a testimonial page with an upload funtion. The upload function is working fine however I'm having trouble displaying the data. I've created a recordset and put each item in the area of the page I want it to display. I then created a repeat region which repeats the rows of the database as many times as you require.

    When I test the recordset SQL it runs fine however once I create the repeat region I get this error in the browser:

    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 0, 15' at line 1

    Now I have not got a clue what this means or how to check it. Can anyone help me? Need this as soon as possible if anyone can help. This is my code:

    <?php require_once('Connections/DohertyDecoratingLtd.php'); ?>
    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
    {
    if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
    }

    $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

    switch ($theType) {
    case "text":
    $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    break;
    case "long":
    case "int":
    $theValue = ($theValue != "") ? intval($theValue) : "NULL";
    break;
    case "double":
    $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
    break;
    case "date":
    $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    break;
    case "defined":
    $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
    break;
    }
    return $theValue;
    }
    }

    $maxRows_rstGallery = 15;
    $pageNum_rstGallery = 0;
    if (isset($_GET['pageNum_rstGallery'])) {
    $pageNum_rstGallery = $_GET['pageNum_rstGallery'];
    }
    $startRow_rstGallery = $pageNum_rstGallery * $maxRows_rstGallery;

    mysql_select_db($database_DohertyDecoratingLtd, $DohertyDecoratingLtd);
    $query_rstGallery = "SELECT tblgallery.imageid, tblgallery.imagetitle, tblgallery.imagefilepath, tbljobcat.CategoryName FROM tbljobcat INNER JOIN tblgallery ON tbljobcat.catid=tblgallery.jobcat; ";
    $query_limit_rstGallery = sprintf("%s LIMIT %d, %d", $query_rstGallery, $startRow_rstGallery, $maxRows_rstGallery);
    $rstGallery = mysql_query($query_limit_rstGallery, $DohertyDecoratingLtd) or die(mysql_error());
    $row_rstGallery = mysql_fetch_assoc($rstGallery);

    if (isset($_GET['totalRows_rstGallery'])) {
    $totalRows_rstGallery = $_GET['totalRows_rstGallery'];
    } else {
    $all_rstGallery = mysql_query($query_rstGallery);
    $totalRows_rstGallery = mysql_num_rows($all_rstGallery);
    }
    $totalPages_rstGallery = ceil($totalRows_rstGallery/$maxRows_rstGallery)-1;
    ?>

    Head, body etc.

    <?php do { ?>
    <a href="<?php echo $row_rstGallery['imagefilepath']; ?>" rel="shadowbox" title="<?php echo $row_rstGallery['imagetitle']; ?>"><img src="<?php echo $row_rstGallery['imagefilepath']; ?>" width="100" height="70" /></a>
    <?php } while ($row_rstGallery = mysql_fetch_assoc($rstGallery)); ?>

  2. #2
    Join Date
    May 2008
    Posts
    17
    echo $query_limit_rstGallery;

    and see what query you are actually trying to run.

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    Hi Tony,

    I don't even know how to do that. Dreamweaver normally generates the code for these queries. I'm lost with PHP/SQL so you'll have to bear with me. On the other hand, instead of setting the limit to show just the first 15 records, I changed it to show all records and it worked! I then changed it back to show 15 and the syntax error disappeared.

    I would still like to know what your procedure is Tony if you wouldn't mind going through it?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The problem is in your code here:

    $query_rstGallery = "SELECT tblgallery.imageid, tblgallery.imagetitle, tblgallery.imagefilepath, tbljobcat.CategoryName FROM tbljobcat INNER JOIN tblgallery ON tbljobcat.catid=tblgallery.jobcat; ";
    $query_limit_rstGallery = sprintf("%s LIMIT %d, %d", $query_rstGallery, $startRow_rstGallery, $maxRows_rstGallery);

    The first line is terminating the SQL with a semi colon. Then you are using appending a LIMIT after the ;

    Here is an example showing SQL

    SELECT info FROM table;
    This is correct syntax for the query

    SELECT info FROM table LIMIT 1,5;
    This is correct syntax

    Your code is going to produce something like this:
    SELECT info FROM table; LIMIT 1,5

    This is not good. You need to remove the ; from the end of your SQL query as follows:

    $query_rstGallery = "SELECT tblgallery.imageid, tblgallery.imagetitle, tblgallery.imagefilepath, tbljobcat.CategoryName FROM tbljobcat INNER JOIN tblgallery ON tbljobcat.catid=tblgallery.jobcat ";
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •