SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard cmuench's Avatar
    Join Date
    Jul 2005
    Location
    At my computer
    Posts
    2,251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    1 misplaced comma screw up the whole thing (was "Syntax help")

    I really need help its telling me my error in SQL syntax is by the submitproduct and by the editproduct query.
    PHP Code:
     <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
     <html>
     <head>
     <title>Cycle Logic Bikes</title>
     </head>
     
     <body>
     <h3><font face="Arial, Helvetica, sans-serif">Cycle Logic Bikes Product Administration</font></h3>
     
     <?php
     
     
    // Display new form if add link was selected
     
    if (ISSET($_GET['addproduct']))
     {
         
    $date date("D, d M Y H:i:s");
         
    ?>
     <form action="<? echo $_SERVER['PHP_SELF']?>" method="post">
     <p>Add news item: </p>
     <p>Title:<input type="text" name="titlefromform" size = "30"/></p>
     <p><? echo $datefromform;?></p>
     <p><textarea name="storyfromform" rows="20" cols="60" wrap></textarea></p>
     <p><input type="text" name="photofromform" size="20" /></p>
     <p><input type="hidden" name="datefromform" value = "<? echo $date; ?>"/></p>
     <p><input type="submit" name="submitproduct" value="SUBMIT" /></p>
     </form>
     <?
     
     }
         elseif (ISSET($_GET['editproduct']))
     {
     
     connecttodatabase();
     selectdatabase();
     $IDTOEDIT = $_GET['editproduct'];
     $sql = "SELECT * FROM items WHERE ID = $IDTOEDIT";
     $result = @mysql_query($sql);
     if (!result)
     {
         die ('<p>Error performing query: ' .
              mysql_error() . '</p>');
     }
     
     $row = mysql_fetch_array($result);
     $description = $row['description'];
     $title = $row['title'];
     $newsid = $row['ID'];
     $date = $row['date'];
     $photo = $row['photo'];
     
         ?>
     <form action="<? echo $_SERVER['PHP_SELF']?>" method="post">
     <p>Edit News item: </p>
     <p>Date:<input type="text" name="datefromform" size = "10" value="<? echo "$date"?>"/></p>
     <p>Title:<input type="text" name="titlefromform" size = "30" value="<? echo "$title"?>"/></p>
     <p><textarea name="storyfromform" rows="10" cols="40" wrap><? echo "$description"?></textarea></p>
     <input type=hidden name="id" size="3" value="<? echo "$newsid"?>"/>
     <p><input type="text" name="photofromform" value="<? echo "$photo"?>" size="20" /></p>
     <p><input type="submit" name="editproduct" value="UPDATE" /></p>
     </form>
     <?
     
     }
         else
     {
     
     connecttodatabase();
     selectdatabase();
     
     if (ISSET($_POST['submitproduct']))
     {
         $description = $_POST['storyfromform'];
         $title = $_POST['titlefromform'];
         $date = $_POST['datefromform'];
         $photo = $_POST['photofromform'];
         $sql = "INSERT INTO items SET
                 description = '$description',
                 title = '$title',
                 date = '$date'
                 photo = '$photo'";
         if (!@mysql_query($sql))
         {
             echo ('<p>Error adding news item: ' . mysql_error() . '</p>');
         }
     }
     
     // If users want to view a news item, display it
     if (ISSET($_GET['viewproduct']))
     {
         $newsid = $_GET['viewproduct'];
         $sql = "SELECT * FROM items WHERE ID = $newsid";
         $result = @mysql_query($sql);
     
         if (!result)
         {
             die ('<p>Error performing query: ' .
              mysql_error() . '</p>');
         }
         
         while ($row = mysql_fetch_array($result))
         {
             $title = $row['title'];
             $date = $row['date'];
             $description = $row['description'];
             $photo = $row['photo'];
             echo ('<p>Date: ' . $date . '</p><p>Title: ' . $title . '</p><p>' . $description . '</p>');
             echo ('<p>Photo file name: ' . $photo . '</p>');
             echo ('<p><a href = "' . $_SERVER['PHP_SELF'] . '">Back to news item listing</a></p>');
         }
     }
     
     // If a news item has been deleted, remove it from database
     if (ISSET($_GET['deleteproduct']))
     {
         $newsid = $_GET['deleteproduct'];
         $sql = "DELETE FROM items where ID = $newsid";
         if (@mysql_query($sql))
         {
             echo ('<p>Product deleted succesfully!</p>');
         }
             else
         {
             echo ('<p>Error deleting product: ' . mysql_error() . '</p>');
         }
     }
     
     // If a news item has been edited, update the database
     if (ISSET($_POST['editproduct']))
     {
         $newsid = $_POST['id'];
         $description = $_POST['storyfromform'];
         $title = $_POST['titlefromform'];
         $date = $_POST['datefromform'];
         $photo = $_POST['photofromform'];
         $sql = "UPDATE items SET
                description = '$description',
                title = '$title'
                date = '$date'
                photo = '$photo'
                WHERE ID = '$newsid'";
         if (@mysql_query($sql))
         {
             echo ('<p>Product updated succesfully!</p>');
         }
             else
         {
             echo ('<p>Error updating Product: ' . mysql_error() . '</p>');
         }
     }
     
     // Retrieve records from the items table
     $result = @mysql_query('SELECT * FROM items ORDER BY ID DESC');
     if (!result)
     {
         die ('<p>Error performing query: ' .
              mysql_error() . '</p>');
     }
     
     // Display the items from previous query
     echo ('<hr />');
     echo ('<table width="600" border="0">');
     echo ('<tr bgcolor = "aaaaaa"><td width = "50"><font face = "Arial, Helvetica, sans-serif"' .
           'color = "ffffff" size="-1">Date</font></td><td width = "400">' .
           '<font face = "Arial, Helvetica, sans-serif" color = "ffffff" size="-1">Products</font>' .
           '</td><td bgcolor = "ffffff"></td></tr>');
     
     while ($row = mysql_fetch_array($result))
     {
         $newsID = $row['ID'];
         echo ('<tr><td width="50" bgcolor="dddddd"><font face="Arial, Helvetica, sans-serif" size="-1">' . $row['date'] . 
               '</font></td><td width="400" bgcolor="dddddd"><font face="Arial, Helvetica, sans-serif" size="-1"><b>' . 
               $row['title'] . '</b></font></td><td width = "150" bgcolor = "ffffff">' .
               '<font face="Arial, Helvetica, sans-serif" size="-1">' . '<a href = "' . $_SERVER['PHP_SELF'] .
               '?viewproduct=' . $newsID . '">View</a> | '. '<a href = "' . $_SERVER['PHP_SELF'] .
               '?editproduct=' . $newsID . '">Edit</a> | '. '<a href = "' . $_SERVER['PHP_SELF'] .
               '?deleteproduct=' . $newsID . '">Delete</a></font></td></tr>');
         
     }
     echo ('</table><hr />');
     
     // Display link to add news item to database
     echo ('<p><a href = "' . $_SERVER['PHP_SELF'] .
           '?addproduct=1">Add a product</a></p>');
     }
     
     // FUNCTIONS
     
     // Connect to the news database
     function connecttodatabase()
     {
     $db = @mysql_connect('localhost', 'username', 'password');
     if (!$db)
         {
             die ('<p>Unable to connect to the ' .
              'news database at this time.</p>');
         }
     }
     
     // Select the news database
     function selectdatabase() {
     if (! @mysql_select_db('database') )
         {
             die ('<p>Unable to locate the news ' .
              'database at this time.</p>');
         }
     }
     
     
     ?>
     </body>
     </html>

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    anywhere you do something like this:
    PHP Code:
    $newsid $_POST['id']; 
    do this instead:
    PHP Code:
    $newsid mysql_real_escape_string($_POST['id']); 
    you're probably getting the errors because your data includes a quote that is not properly escaped.

    if that doesn't fix your problem (or inserts weird slashes in your data), then rewrite the lines where you have mysql_error() to also output the actual query in its entirety. you can then try taht query in phpmyadmin to figure out where its failing.

  3. #3
    SitePoint Wizard cmuench's Avatar
    Join Date
    Jul 2005
    Location
    At my computer
    Posts
    2,251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm still getting it and the problem is:
    Error updating Product: 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 'WHERE ID = '2'' at line 6
    I think it is the SQL syntax I use can you please look at hat longneck? I really appreciate your help as 1 misplaced comma screw up the whole thing.
    here is the code that is giving me the problems.
    PHP Code:
    $sql "UPDATE items SET
               description = '
    $description',
               title = '
    $title',
               date = '
    $date',
               photo = '
    $photo',
               WHERE ID = '
    $newsid'"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dangling comma, exactly where the error message says to look

    would be so much easier to spot if you were using the "leading comma" style of writing SQL --

    Code:
    UPDATE items 
       SET description = '$description'
         , title = '$title'
         , date = '$date'
         , photo = '$photo'
         ,
     WHERE ID = '$newsid'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard cmuench's Avatar
    Join Date
    Jul 2005
    Location
    At my computer
    Posts
    2,251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its still giving me a error message
    Error updating Product: 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 'WHERE ID = '4'' at line 7
    here is my code again
    PHP Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Cycle Logic Bikes</title>
    </head>

    <body>
    <h3><font face="Arial, Helvetica, sans-serif">Cycle Logic Bikes Product Administration</font></h3>

    <?php

    // Display new form if add link was selected
    if (ISSET($_GET['addproduct']))
    {
        
    $date date("D, d M Y H:i:s");
        
    ?>
    <form action="<? echo $_SERVER['PHP_SELF']?>" method="post">
    <p>Add news item: </p>
    <p>Title:<input type="text" name="titlefromform" size = "30"/></p>
    <p><? echo $datefromform;?></p>
    <p><textarea name="storyfromform" rows="20" cols="60" wrap></textarea></p>
    <p><input type="text" name="photofromform" size="20" /></p>
    <p><input type="hidden" name="datefromform" value = "<? echo $date; ?>"/></p>
    <p><input type="submit" name="submitproduct" value="SUBMIT" /></p>
    </form>
    <?

    }
        elseif (ISSET($_GET['editproduct']))
    {

    connecttodatabase();
    selectdatabase();
    $IDTOEDIT = $_GET['editproduct'];
    $sql = "SELECT * FROM items WHERE ID = $IDTOEDIT";
    $result = @mysql_query($sql);
    if (!result)
    {
        die ('<p>Error performing query: ' .
             mysql_error() . '</p>');
    }

    $row = mysql_fetch_array($result);
    $description = $row['description'];
    $title = $row['title'];
    $newsid = $row['ID'];
    $date = $row['date'];
    $photo = $row['photo'];

        ?>
    <form action="<? echo $_SERVER['PHP_SELF']?>" method="post">
    <p>Edit News item: </p>
    <p>Date:<input type="text" name="datefromform" size = "10" value="<? echo "$date"?>"/></p>
    <p>Title:<input type="text" name="titlefromform" size = "30" value="<? echo "$title"?>"/></p>
    <p><textarea name="storyfromform" rows="10" cols="40" wrap><? echo "$description"?></textarea></p>
    <input type=hidden name="id" size="3" value="<? echo "$newsid"?>"/>
    <p><input type="text" name="photofromform" value="<? echo "$photo"?>" size="20" /></p>
    <p><input type="submit" name="editproduct" value="UPDATE" /></p>
    </form>
    <?

    }
        else
    {

    connecttodatabase();
    selectdatabase();

    if (ISSET($_POST['submitproduct']))
    {
        $description = mysql_real_escape_string($_POST['storyfromform']);
        $title = mysql_real_escape_string($_POST['titlefromform']); 
        $date = mysql_real_escape_string($_POST['datefromform']); 
        $photo = mysql_real_escape_string($_POST['photofromform']); 
        $sql = "INSERT INTO items SET
                    description = '$description',
                title = '$title',
                date = '$date'
                photo = '$photo'";
        if (!@mysql_query($sql))
        {
            echo ('<p>Error adding news item: ' . mysql_error() . '</p>');
        }
    }

    // If users want to view a news item, display it
    if (ISSET($_GET['viewproduct']))
    {
        $newsid = $_GET['viewproduct'];
        $sql = "SELECT * FROM items WHERE ID = $newsid";
        $result = @mysql_query($sql);

        if (!result)
        {
            die ('<p>Error performing query: ' .
             mysql_error() . '</p>');
        }
        
        while ($row = mysql_fetch_array($result))
        {
            $title = $row['title'];
            $date = $row['date'];
            $description = $row['description'];
            $photo = $row['photo'];
            echo ('<p>Date: ' . $date . '</p><p>Title: ' . $title . '</p><p>' . $description . '</p>');
            echo ('<p>Photo file name: ' . $photo . '</p>');
            echo ('<p><a href = "' . $_SERVER['PHP_SELF'] . '">Back to news item listing</a></p>');
        }
    }

    // If a news item has been deleted, remove it from database
    if (ISSET($_GET['deleteproduct']))
    {
        $newsid = $_GET['deleteproduct'];
        $sql = "DELETE FROM items where ID = $newsid";
        if (@mysql_query($sql))
        {
            echo ('<p>Product deleted succesfully!</p>');
        }
            else
        {
            echo ('<p>Error deleting product: ' . mysql_error() . '</p>');
        }
    }

    // If a news item has been edited, update the database
    if (ISSET($_POST['editproduct']))
    {
        $newsid = mysql_real_escape_string($_POST['id']); 
        $description = mysql_real_escape_string($_POST['storyfromform']);
        $title = mysql_real_escape_string($_POST['titlefromform']); 
        $date = mysql_real_escape_string($_POST['datefromform']); 
        $photo = mysql_real_escape_string($_POST['photofromform']);
        $sql = "UPDATE items 
       SET description = '$description'
         , title = '$title'
         , date = '$date'
         , photo = '$photo'
         ,
     WHERE ID = '$newsid'";
        if (@mysql_query($sql))
        {
            echo ('<p>Product updated succesfully!</p>');
        }
            else
        {
            echo ('<p>Error updating Product: ' . mysql_error() . '</p>');
        }
    }

    // Retrieve records from the items table
    $result = @mysql_query('SELECT * FROM items ORDER BY ID DESC');
    if (!result)
    {
        die ('<p>Error performing query: ' .
             mysql_error() . '</p>');
    }

    // Display the items from previous query
    echo ('<hr />');
    echo ('<table width="600" border="0">');
    echo ('<tr bgcolor = "aaaaaa"><td width = "50"><font face = "Arial, Helvetica, sans-serif"' .
          'color = "ffffff" size="-1">Date</font></td><td width = "400">' .
          '<font face = "Arial, Helvetica, sans-serif" color = "ffffff" size="-1">Products</font>' .
          '</td><td bgcolor = "ffffff"></td></tr>');

    while ($row = mysql_fetch_array($result))
    {
        $newsID = $row['ID'];
        echo ('<tr><td width="50" bgcolor="dddddd"><font face="Arial, Helvetica, sans-serif" size="-1">' . $row['date'] . 
              '</font></td><td width="400" bgcolor="dddddd"><font face="Arial, Helvetica, sans-serif" size="-1"><b>' . 
              $row['title'] . '</b></font></td><td width = "150" bgcolor = "ffffff">' .
              '<font face="Arial, Helvetica, sans-serif" size="-1">' . '<a href = "' . $_SERVER['PHP_SELF'] .
              '?viewproduct=' . $newsID . '">View</a> | '. '<a href = "' . $_SERVER['PHP_SELF'] .
              '?editproduct=' . $newsID . '">Edit</a> | '. '<a href = "' . $_SERVER['PHP_SELF'] .
              '?deleteproduct=' . $newsID . '">Delete</a></font></td></tr>');
        
    }
    echo ('</table><hr />');

    // Display link to add news item to database
    echo ('<p><a href = "' . $_SERVER['PHP_SELF'] .
          '?addproduct=1">Add a product</a></p>');
    }

    // FUNCTIONS

    // Connect to the news database
    function connecttodatabase()
    {
    $db = @mysql_connect('localhost', 'username', 'passwrod');
    if (!$db)
        {
            die ('<p>Unable to connect to the ' .
             'news database at this time.</p>');
        }
    }

    // Select the news database
    function selectdatabase() {
    if (! @mysql_select_db('database') )
        {
            die ('<p>Unable to locate the news ' .
             'database at this time.</p>');
        }
    }


    ?>
    </body>
    </html>

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i said you would've been able to see the dangling comma if you were using the "leading comma" style of sql, which i see you have now adopted -- good stuff

    however, you haven't removed the dangling comma!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard cmuench's Avatar
    Join Date
    Jul 2005
    Location
    At my computer
    Posts
    2,251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Sorry I'm pretty illiterate to sql syntax anyway what do you mean by dangling comma? where is the creature?

  8. #8
    SitePoint Wizard cmuench's Avatar
    Join Date
    Jul 2005
    Location
    At my computer
    Posts
    2,251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind I figured it out. I just had 1 to many commas. Thanks for all your help. I love sitepoint!!!

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What about this bit?
    Code:
    $sql = "INSERT INTO items SET
                description = '$description',
                title = '$title',
                date = '$date'
                photo = '$photo'";
    Shouldn't there be a comma after '$date' ?


Bookmarks

Posting Permissions

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