Thread: Php/sql error

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    16

    Php/sql error


    i've been trying to make an edit page so i can edit member info but its currently erroring

    this is the error
    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 'WHERE Member_ID = 1 LIMIT 1' at line 3<br /> SQL Statement: UPDATE `memberlist`
    SET Member_Name =
    WHERE Member_ID = 1 LIMIT 1
    this is the code

    PHP Code:
    <?php 
        ob_start
    ();
        
    session_start(); 
        
    $db_name 'desbrina_tcgcards'// Database name
        
    $db_user 'desbrina_tcg'// Username
        
    $db_pwrd '****ennou'// Password
        
        
    $con mysql_connect('localhost'$db_user$db_pwrd) or die('Could not connect to database!');
        
    mysql_select_db($db_name$con);
       
    $edituser $_POST['edituser'];
    $sql "SELECT * FROM `memberlist` WHERE Member_Name = '$edituser'";
       
    $result mysql_db_query($db_name$sql$con);
    if (!
    $result) { echo( mysql_error()); }

    while (
    $row mysql_fetch_array($result)) {
        
    $ID $row["Member_ID"];
       
    $Member_Name $row["Member_Name"];
       }  
       
       if (isset (
    $_POST['Edit'])) {
        
    $con2 mysql_connect('localhost'$db_user$db_pwrd
                or die(
    'Could not connect to database!');
                
        
    mysql_select_db($db_name$con)
        or die (
    'Could not select database'.mysql_error ());
        
        
    $message "The User {$_POST['Membername']} with the Database ID of {$_POST['Member_ID']} has sucessfully been Edited," ;
    $ID =  mysql_real_escape_string($_POST['ID']); 
        
    $query2  "UPDATE `memberlist`
                    SET    Member_Name = 
    $Member_Name
                    WHERE Member_ID = 
    $ID LIMIT 1";
        
    $result2 mysql_query($query2$con2);
        
        if (!
    $result2)
            print 
    'Error: ' mysql_error() . '<br />' ' SQL Statement: ' $query2;
        else
            print 
    $message;
    }
       
    ?>
    and the form
    Code:
    <form id="form1" name="form1" method="post" action="<?php print "$_SERVER[PHP_SELF]"?>">
        <label></label>
        <br />
        <p>Editing User: <?php echo "$Membername"; ?></p>
        <table width="253" border="0">
          <tr>
            <td width="92">Member ID:</td>
            <td width="151"><input type="hidden" name="ID" id="ID" value="<?php print $ID; ?>" />
          <?php echo $ID ?></td>
          </tr>
          <tr>
            <td>Member Name: </td>
            <td><input name="Member_Name" type="text" id="Member_Name" value="<?php echo $Member_Name ?>" />		</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td><div align="right">
                <input name="Edit" type="submit" id="Edit" value="Edit" />
            </div></td>
            <td><input name="Reset" type="reset" id="Reset" value="Reset" /></td>
          </tr>
        </table>
        <p>&nbsp;</p>
      </form>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2004
    Posts
    778
    Rep Power
    241
    As far as I understand Member_Name is a string ... Try to add single quotes around the value in the query:
    PHP Code:
       $query2  "UPDATE `memberlist`
                    SET    Member_Name = '
    $Member_Name'
                    WHERE Member_ID = 
    $ID LIMIT 1"
  4. #3
  5. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    1) SQL injection at
    PHP Code:
    $edituser $_POST['edituser']; 
    $sql "SELECT * FROM `memberlist` WHERE Member_Name = '$edituser'"
    2) mysql_db_query is deprecated. Use mysql_select_db and mysql_query instead.

    3) If $_POST[ID] is a number then you should use a number function (like intval() ) not a string function (like mysql_real_escape_string() )

    4a) There is no $_POST[edituser] on your form. Do you mean $_POST[Member_name] instead?

    4b) At
    PHP Code:
    $result mysql_db_query($db_name$sql$con); 
    if (!
    $result) { echo( mysql_error()); } 

    while (
    $row mysql_fetch_array($result)) { 
    you need to test if the query actually has any results (!$result will only check if there was an error). Otherwise $Member_Name will not be set and you'll get that error.


    (I feel like a compiler )
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    16
    $_POST[edituser] is carried over from the page before

    i put '' round then and it stopped erroring, but its not doing anything now, but i'll check it at home
  8. #5
  9. Web Developer/Musician
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Nov 2004
    Location
    Tennessee Mountains
    Posts
    2,424
    Rep Power
    1034
    I don't believe that the LIMIT keyword is applicable in update or insert statements. You're not really going to run this script without escaping input are you?
    Coder Central Tutorials, news and information for the programming community at large.
  10. #6
  11. Dev Shed Spammer, Quite Plain.
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Location
    looking for my other left foot
    Posts
    283
    Rep Power
    109
    Originally Posted by Hammer65
    I don't believe that the LIMIT keyword is applicable in update or insert statements. You're not really going to run this script without escaping input are you?
    You can use LIMIT in all sorts of places.
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    16
    the original script wasn't written by me, it was written by someone on here for my first website, i'm just trying to edit it for my other website
  14. #8
  15. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2006
    Location
    India
    Posts
    850
    Rep Power
    551
    Originally Posted by Hammer65
    I don't believe that the LIMIT keyword is applicable in update or insert statements. You're not really going to run this script without escaping input are you?
    "LIMIT n" Statement is to restrict the action to n number of records only. It's good to use, if you are clear with what you are going to do
    Akash Dwivedi
    "Whatever the mind can conceive and believe, the mind can achieve."
    Feel good..


  16. #9
  17. Web Developer/Musician
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Nov 2004
    Location
    Tennessee Mountains
    Posts
    2,424
    Rep Power
    1034
    I see that the MySQL manual mentions it as part of the update syntax, but not for insert, however I did find some info on using with insert. I just had never seen it use that way.
    Coder Central Tutorials, news and information for the programming community at large.
  18. #10
  19. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    16
    it was recommend that i use it as i only want to edit 1 user, is it a good idea for insert as well or not?


    also, the SQL injection part, how do i solve it. its only going to be me accessing it as its passworded and only i have a password for it
  20. #11
  21. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2006
    Location
    India
    Posts
    850
    Rep Power
    551
    Here is an example
    Code:
    INSERT INTO table_2
    SELECT * FROM table_1
    LIMIT 10
    Akash Dwivedi
    "Whatever the mind can conceive and believe, the mind can achieve."
    Feel good..


  22. #12
  23. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Originally Posted by Desbrina
    also, the SQL injection part, how do i solve it. its only going to be me accessing it as its passworded and only i have a password for it
    PHP Code:
    $edituser mysql_real_escape_string($_POST['edituser']); 
    Never trust the user, even if you know it can only be one person and that person is your mother. No offense meant, but it's just too big a risk to take.
  24. #13
  25. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    16
    thanks, i added the rest to the update but im now getting this
    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 '21/09/06',
    Date_Left = '',
    Prejoiner = 'yes',
    WHERE Memb' at line 7<br /> SQL Statement: UPDATE `memberlist`
    SET Member_Name = 'Desbrina',
    Level = '6',
    Collecting = 'kunzite',
    Email = 'desbrina1@yahoo.co.uk',
    Website = 'http://desbrina.clavis-sama.com',
    Birthday = '30 November,
    Date_Joined = '21/09/06',
    Date_Left = '',
    Prejoiner = 'yes',
    WHERE Member_ID = 1 LIMIT 1
    this is the update code

    PHP Code:
        $query2  "UPDATE `memberlist`
                    SET    Member_Name = '
    $Member_Name',
                    Level = '
    $Level',
                    Collecting = '
    $Collecting',
                    Email = '
    $Email',
                    Website = '
    $Website',
                    Birthday = '
    $Birthday,
                    Date_Joined = '
    $Date_Joined',
                    Date_Left = '
    $Date_Left',
                    Prejoiner = '
    $Prejoiner',
                    WHERE Member_ID = 
    $ID LIMIT 1";
        
    $result2 mysql_query($query2$con2);
        
        if (!
    $result2)
            print 
    'Error: ' mysql_error() . '<br />' ' SQL Statement: ' $query2;
        else
            
    $message "The User {$_POST['Member_Name']} with the Database ID of {$_POST['ID']} has sucessfully been Edited," ;
            } 
  26. #14
  27. Dev Shed Spammer, Quite Plain.
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Location
    looking for my other left foot
    Posts
    283
    Rep Power
    109
    Birthday = '30 November,
    you need to close your single quote.

    Comments on this post

    • helloakash agrees
  28. #15
  29. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    618
    Rep Power
    16
    i can not believe i did that and not notice it, i must have deleted it when was editing it

    Comments on this post

    • dbconnect_zim agrees : damned pesky: 's. :D
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo