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
  •