SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Virginia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with updating a database record.

    I keep getting the following error:
    Error performing query: 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 (runtotals.id = '1)' at line 1

    I can't find what the problem is. Here is my code for the php page:

    Code:
    <html> 
    <head> 
    <title>Update Monthly Incident Totals</title> 
    </head> 
    <body> 
    <?php 
    
    if (!$_REQUEST['Submit']) { 
         html_form(); 
    } elseif ($_REQUEST['Submit'] == "ViewTotals") { 
         select_month(); 
    } elseif ($_REQUEST['Submit'] == "Edit") { 
         get_data(); 
    } elseif ($_REQUEST['Submit'] == "Update") { 
         update_total(); 
    }   
    
    function my_conn() { 
    
    /* set's the variables for MySQL connection */ 
    
    $server = "localhost";  
    $username = "myusername"; 
    $password = "mypass"; 
    
    /* Connects to the MySQL server */ 
    
    $link = @mysql_connect ($server, $username, $password) or die (mysql_error()); 
    
    /* Defines the Active Database for the Connection */ 
    
    if (!@mysql_select_db("innovati_firedept", $link)) { 
         echo "<p>There has been an error. This is the error message:</p>"; 
         echo "<p><strong>" . mysql_error() . "</strong></p>"; 
         echo "Please Contact Your Systems Administrator with the details"; 
    } 
    
    return $link; 
    
    } 
    
    function html_form() { 
    
    $conn = my_conn(); 
    $SQL = "SELECT DISTINCT runtotals.month FROM runtotals;"; 
    
    $result = mysql_query($SQL, $conn); 
    if (!$result) { 
        echo("<p>Error performing query: " . mysql_error() . "</p>"); 
    exit(); 
    } 
    ?> 
    
    <p>Please select the month that you would like to update.</p> 
    <form name="runtotals" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>"> 
    Month: <select name="month"> 
    <? 
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 
        echo("<option value=\"" . $row["month"] . "\">" . $row["month"] . "</option>\n"); 
    } 
    ?> 
    </select> 
    <input type="submit" name="Submit" value="ViewTotals" /> 
    </form> 
    
    <? 
    
    /* Closes Connection to the MySQL server */ 
    
    mysql_close ($conn); 
    
    } 
    
    
    function select_month() { 
    ?> 
    <h4>Current Totals</h4> 
    <? 
    
    $conn = my_conn(); 
    
    /* Sets the SQL Query */ 
    
    $sql = "SELECT * FROM runtotals"; 
    $sql .= " WHERE (runtotals.month = '{$_POST['month']}')"; 
    
    /* Passes a Query to the Active Database */ 
    
    $result = mysql_query($sql, $conn);   
    if (!$result) { 
      echo("<p>Error performing query: " . mysql_error() . "</p>"); 
      exit(); 
    } 
    
    /* Starts the table and creates headings */ 
    ?> 
    <table> 
    <tr> 
    <td><strong>Month</strong></td> 
    <td><strong>Total</strong></td> 
    <td></td> 
    </tr> 
    <? 
    
    /* Retrieves the rows from the query result set 
    and puts them into a HTML table row */ 
    
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 
        echo("<tr>\n<td>" . $row["month"] . "</td>"); 
        echo("<td>" . $row["total"] . "</td>"); 
        echo("<td><a href=\"" . $_SERVER['PHP_SELF'] . "?id=" .$row['id'] . "&Submit=Edit\">Edit</a></td></tr>\n\n"); 
    } 
    
    /* Closes the table */ 
    ?> 
    </table> 
    <? 
    
    /* Closes Connection to the MySQL server */ 
    
    mysql_close ($conn); 
    html_form(); 
    } 
    
    function get_data() {     
    
    /* Calls our connection function */ 
    
    $conn = my_conn(); 
    
    /* Defines query */ 
    
    $sql = "SELECT * FROM runtotals WHERE (runtotals.id = " . $_REQUEST['id'] . ")"; 
    
    
    /* Passes query to database */ 
    
    $result = mysql_query($sql, $conn); 
    if (!$result) { 
      echo("<p>Error performing query: " . mysql_error() . "</p>"); 
      exit(); 
    } 
    
    /* creates our row array with an if statement to report errors */ 
    
    if ($row = @mysql_fetch_array($result, MYSQL_ASSOC)) { 
    
    /* prints out the artist and title */ 
    
    print "<h4>$row[month] - $row[total]</h4>"; 
    
    /* prints out our HTML form '\"' */ 
    
    print "<form name=\"CDs\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">"; 
    
    /* Prints out hidden releaseID - we don't put this in the HTML form 
    so that the uer cannot edit the Key value in error */ 
    
    //print "<input type=\"text\" name=\"id\" value=\"$row[id]\">"; 
    
    /* prints out our HTML table and fields 'escaping' any double quotes '\"' */ 
    
    print "<table width=\"600\"> 
    
    <tr> 
    <td width=\"100\"><strong>New Total:</strong></td> 
    <td width=\"150\"><input type=\"text\" name=\"total\" value=\"$row[total]\"></td> 
    <td rowspan=\"5\" valign=\"top\"><input type=\"submit\" name=\"Submit\" value=\"Update\"> 
    </td> 
    </tr> 
    </table> 
    </form>";
    
    
    /* Counts the number of rows (therefore copies) */ 
    
    //$count = mysql_num_rows($result_count); 
    
    //if ($count != 1) { 
    //print "<p>There are $count copies of this CD</p>"; 
    //} else { 
    //print "<p>There is $count copy of this CD</p>"; 
    //} 
    
    //} else { 
       // echo("There has been an error" . mysql_error()); 
    } 
    
    /* closes connection */ 
    
    mysql_close ($conn); 
    
    } 
    
    function update_total() { 
    
    /* Calls our connection function */ 
    
    $conn = my_conn(); 
    
    /* Defines query */ 
    
    $sql_update = "UPDATE runtotals SET "; 
    $sql_update .= "runtotals.month = '" . $_REQUEST['month'] . "', "; 
    $sql_update .= "runtotals.total = '" . $_REQUEST['total'] . "', "; 
    $sql_update .= "WHERE (runtotals.id = '" . $_REQUEST['id'] . ")";
    
    /* Passes query to database */ 
    
    $result = mysql_query($sql_update, $conn); 
    if (!$result) { 
      echo("<p>Error performing query: " . mysql_error() . "</p>"); 
      exit(); 
    } 
    
    /* Prints succes message */ 
    
    print "<p> Successfully Updated</p>"; 
    
    /* closes connection */ 
    
    mysql_close ($conn); 
    
    /* Calls get_data() function */ 
    
    get_data(); 
    
    } 
    
    ?> 
    
    </body> 
    </html>
    Any ideas?? Thanks in advance!

  2. #2

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a missing apostrophe
    PHP Code:
    $sql_update "UPDATE runtotals SET "
    $sql_update .= "runtotals.month = '" $_REQUEST['month'] . "', "
    $sql_update .= "runtotals.total = '" $_REQUEST['total'] . "' "
    $sql_update .= "WHERE (runtotals.id = '" $_REQUEST['id'] . "')"
    Edit:

    and a colon too much

  3. #3
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think you have a , before where and it shouldn't.
    try the folowing
    PHP Code:
     $sql_update "UPDATE runtotals SET ";
    $sql_update .= "runtotals.month = '" $_REQUEST['month'] . "', ";
    $sql_update .= "runtotals.total = '" $_REQUEST['total'] . "' ";
    $sql_update .= "WHERE (runtotals.id = '" $_REQUEST['id'] . "')"

  4. #4

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaswinder_rana
    i think you have a , before where and it shouldn't.
    try the folowing
    Yep, oversaw it

  5. #5
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Virginia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! I tried both and now I get this error:

    Successfully Updated

    Error performing query: 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 ')' at line 1

  6. #6
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try the same query without brackets and see
    PHP Code:
     $sql_update "UPDATE runtotals SET ";
    $sql_update .= "runtotals.month = '" $_REQUEST['month'] . "', ";
    $sql_update .= "runtotals.total = '" $_REQUEST['total'] . "' ";
    $sql_update .= "WHERE runtotals.id = '" $_REQUEST['id'] . "'"
    if you again get error then also try echoing the query with
    echo $sql_update and post the result (just in case this change won't work)

  7. #7

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wouldnt call "Successfully Updated" exactly an error message

    Seriously, I wonder how this can be shown in this order, because first of all "Successfully Updated" is the last message you are outputting. Secondly, if an error occures it should never the printed because the script will exit after outputting the "Error performing query" message.

  8. #8

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaswinder_rana
    try the same query without brackets and see
    The brackets shouldnt matter ....

    but yeah, I know .... the typical "oohh, this will work, it shouldnt matter" statement

  9. #9
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm. din't think about that part. drzoid is right. it shouldn't

  10. #10
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Virginia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Still having problems

    I am still having similar problems with this. I just can't figure out what I am doing wrong. I have re-written the code using the SitePoint book, Build Your Own Database Driven Website Using PHP & MySQL. as my guide. I am building a content management system for a website for the local fire department. I can add and delete records but not update them because of the same error.
    Everything works fine until I click the "update" button then I get:

    Error updating member details: 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='5'' at line 7

    My Code is:
    Code:
    <html>
    <head>
    <title>Edit Member Details</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <?php
    
    $dbcnx = @mysql_connect('localhost', 'myusername, 'mypass');
    if (!$dbcnx) {
      exit('<p>Unable to connect to the ' .
          'database server at this time.</p>');
    }
    
    if (!@mysql_select_db('innovati_firedept')) {
      exit('<p>Unable to locate the member ' .
          'database at this time.</p>');
    }
    
    if (isset($_POST['firstname'])):
      // The member's details have
      // been updated.
      
      $id = $_POST['id'];
      $firstname = $_POST['firstname'];
      $lastname=$_POST['lastname'];
      $position=$_POST['position'];
      $year = $_POST['year'];
      $email=$_POST['email'];
    
      $sql = "UPDATE members SET
              firstname='$firstname',
    		  lastname='$lastname',
    		  position='$position',
    		  year='$year',
    		  email='$email',
              WHERE id='$id'";
      if (mysql_query($sql)) {
        echo '<p>Member details updated.</p>';
      } else {
        exit('<p>Error updating member details: ' .
            mysql_error() . '</p>');
      }
    
        
    ?>
    
    <p></p>
    
    <?php else: // Allow the user to edit the member
    
      $id = $_GET['id'];
    
      $member = @mysql_query(
        "SELECT firstname,lastname, position,year, email FROM members WHERE id='$id'");
      if (!$member) {
        exit('<p>Error fetching member details: ' .
            mysql_error() . '</p>');
      }
    
      $member = mysql_fetch_array($member);
    
      $firstname = $member['firstname'];
      $lastname = $member['lastname'];
      $position=$member['position'];
      $year=$member['year'];
      $email=$member['email'];
    
       $position = htmlspecialchars($position);
    
     
    ?>
    
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    <p>Edit the member:<br /></P>
    <p>First Name:
    <input type="text" name="firstname" value="<?php echo $firstname; ?>"><br />
    Last Name:
    <input type="text" name="lastname" value="<?php echo $lastname;?>"><br />
    Position:
    <input type="text" name="position" value="<?php echo $position;?>"><br />
    Member Since:
    <input type="text" name="year" value="<?php echo $year;?>"><br / >
    Email:
    <input type="text" name="email" value="<?php echo $email;?>"><br />
    </p>
    <input type="hidden" name="id" value="<?php echo $id; ?>" />
    <input type="submit" value="SUBMIT" />
    </form>
    
    <?php endif; ?>
    </body>
    </html>

  11. #11

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In such cases its always advisable to print out your SQL statement. Just do a
    PHP Code:
    echo $sql
    before calling mysql_query(). I guess one of your data fields you set screws the query (unescaped strings? If so take a look at mysql_escape_string().)

  12. #12
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    remove the trailing , on the line email='$email',

    Also, you should really use mysql_real_escape_string on any string data you store in the database

  13. #13
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you are using earlier versions mysql_real_escape_string might not be available so what i do is
    [php]
    function clearStr($str)
    {
    return (function_exists('mysql_real_escape_string'))?mysql_real_escape_string($str):mysql_escape_string($str);
    }
    [/pgp]

  14. #14

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaswinder_rana
    if you are using earlier versions mysql_real_escape_string might not be available so what i do is
    PHP Code:
    function clearStr($str)
    {
        return (
    function_exists('mysql_real_escape_string'))?mysql_real_escape_string($str):mysql_escape_string($str);

    Why would you actually want to use mysql_real_escape_string()?

  15. #15
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP_Manual
    mysql_escape_string() does not escape % and _.

    This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes a connection handler and escapes the string according to the current character set. mysql_escape_string() does not take a connection argument and does not respect the current charset setting.
    is this what you wanted to know

  16. #16

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaswinder_rana
    is this what you wanted to know
    So your concern is, that you want to escape the string according to the character set? I would guess this shouldnt matter as long as you only deal with ANSI characters.

  17. #17
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    drzoid can you be more clear on that please. i think i am misunderstanding the way this function works. can you explain this character-set bit please.

  18. #18

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaswinder_rana
    drzoid can you be more clear on that please. i think i am misunderstanding the way this function works. can you explain this character-set bit please.
    The only difference between these two functions is that mysql_real_escape_string() escapes a string according to the character set defined for the current database.

  19. #19
    SitePoint Member ravikiran's Avatar
    Join Date
    Dec 2004
    Location
    hyderabad
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kristy9966
    I am still having similar problems with this. I just can't figure out what I am doing wrong. I have re-written the code using the SitePoint book, Build Your Own Database Driven Website Using PHP & MySQL. as my guide. I am building a content management system for a website for the local fire department. I can add and delete records but not update them because of the same error.
    Everything works fine until I click the "update" button then I get:

    Error updating member details: 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='5'' at line 7

    My Code is:
    Code:
    <html>
    <head>
    <title>Edit Member Details</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <?php
    
    $dbcnx = @mysql_connect('localhost', 'myusername, 'mypass');
    if (!$dbcnx) {
      exit('<p>Unable to connect to the ' .
          'database server at this time.</p>');
    }
    
    if (!@mysql_select_db('innovati_firedept')) {
      exit('<p>Unable to locate the member ' .
          'database at this time.</p>');
    }
    
    if (isset($_POST['firstname'])):
      // The member's details have
      // been updated.
      
      $id = $_POST['id'];
      $firstname = $_POST['firstname'];
      $lastname=$_POST['lastname'];
      $position=$_POST['position'];
      $year = $_POST['year'];
      $email=$_POST['email'];
    
      $sql = "UPDATE members SET
              firstname='$firstname',
    		  lastname='$lastname',
    		  position='$position',
    		  year='$year',
    		  email='$email',
              WHERE id='$id'";
      if (mysql_query($sql)) {
        echo '<p>Member details updated.</p>';
      } else {
        exit('<p>Error updating member details: ' .
            mysql_error() . '</p>');
      }
    
        
    ?>
    
    <p></p>
    
    <?php else: // Allow the user to edit the member
    
      $id = $_GET['id'];
    
      $member = @mysql_query(
        "SELECT firstname,lastname, position,year, email FROM members WHERE id='$id'");
      if (!$member) {
        exit('<p>Error fetching member details: ' .
            mysql_error() . '</p>');
      }
    
      $member = mysql_fetch_array($member);
    
      $firstname = $member['firstname'];
      $lastname = $member['lastname'];
      $position=$member['position'];
      $year=$member['year'];
      $email=$member['email'];
    
       $position = htmlspecialchars($position);
    
     
    ?>
    
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    <p>Edit the member:<br /></P>
    <p>First Name:
    <input type="text" name="firstname" value="<?php echo $firstname; ?>"><br />
    Last Name:
    <input type="text" name="lastname" value="<?php echo $lastname;?>"><br />
    Position:
    <input type="text" name="position" value="<?php echo $position;?>"><br />
    Member Since:
    <input type="text" name="year" value="<?php echo $year;?>"><br / >
    Email:
    <input type="text" name="email" value="<?php echo $email;?>"><br />
    </p>
    <input type="hidden" name="id" value="<?php echo $id; ?>" />
    <input type="submit" value="SUBMIT" />
    </form>
    
    <?php endif; ?>
    </body>
    </html>
    Hi ! kristy9966,

    have u check the query by removing the comma after "email='$email'". I think this will work definitly.


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
  •