SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP driven form doesn't update data back to database!!!

    Code:
            $name = sanitize($_POST['name'], "text"); 
    		$designation = sanitize($_POST['designation'], "text");
    		$phone = sanitize($_POST['phone'], "int");
    		$date = sanitize($_POST['date'], "text");
            $id = sanitize($_POST['id'], "text"); 
            $new_id = create_guid(); 
    		
    		include_once "connect.php";
    		
    		$update_sql = "UPDATE `projects` SET `Name`={$Name}, `id`='{$new_id}' WHERE `projects`.`id`={$id};"; 
            $update_result = mysql_query($update_sql); 
            mysql_free_result($update_result);
    I have built an html form that is filled out in two steps- first by an administrator and then by the user. The code above shows the user part after the admin has already filled a few fields and hit submit. The php generates a link to another page that contains these 4 fields- name, designation, phone and date. The code above is supposed to add data to the database in the column name but its not working. Also I get a warning and a few notices about undefined variables and indexes

    Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\Program Files\xampp\htdocs\badger.php on line 211

    Can anyone tell me whats the problem in the code? Is it the update statement? My connect.php has no error because the previous few fields filled by the admin are being saved. The phone field shows a 0, but the other 3 are empty. Urgent help will be much appreciated.

  2. #2
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your mysql_query() probably did not return the update result. Update it to include this to see why:

    $update_result = mysql_query($update_sql);
    if (!$update_result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
    }
    mysql_free_result($update_result);

  3. #3
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what it says. Could not run 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 ' `id`='{7466FFB9-20FC-B4BD-E2D5-3DC6C1BAD403}' WHERE `projects`.`id`='{D5DB5A4C-' at line 1

    I can't figure out whats wrong with that though. Its a syntactical error. Whats wrong with the syntax? Can you help me with that? In the line of code I am updating the id to a new ID so that the page that the user sees is different from the page that the admin just filled out.

  4. #4
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try it without the {}
    PHP is FUN

  5. #5
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Update the the query to this and try again:

    $update_sql = "UPDATE projects SET Name='{$Name}', id='{$new_id}' WHERE id='{$id}'";

    Without the {} then you need to do this:

    $update_sql = "UPDATE projects SET Name='".$Name."', id='".$new_id."' WHERE id='".$id."'";

  6. #6
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried both without the {} and tried tom8's update_sql statement. Doesn't work. Could not run 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 '{5F4FA1CB-050D-A534-0383-0F8F8782FCE1}''' at line 1

    Its the value of the $new_id variable. Any other suggestions?

  7. #7
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what if you did the where statement like this:
    WHERE id = '$id'
    would that work?
    PHP is FUN

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by immohito View Post
    Any other suggestions?
    please echo your $update_sql variable so we can see the entire query

    i suspect the string values are missing the single quotes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    None of it works. Its the same error. What do you mean echo my entire variable? I posted the entire chunk of code right in the beginning!

  10. #10
    SitePoint Enthusiast scout1idf's Avatar
    Join Date
    Nov 2009
    Location
    Ohio
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if this will fix it or not, but I removed the ` and used ' and changed the "WHERE `projects`.`id`={$id}" part and removed the {} as maxdream01 suggested.

    Code MySQL:
    $name = sanitize($_POST['name'], "text"); 
    		$designation = sanitize($_POST['designation'], "text");
    		$phone = sanitize($_POST['phone'], "int");
    		$date = sanitize($_POST['date'], "text");
            $id = sanitize($_POST['id'], "text"); 
            $new_id = create_guid(); 
     
    		include_once "connect.php";
     
    		$update_sql = "UPDATE projects SET Name='$Name', id='$new_id' WHERE id='$id';"; 
            $update_result = mysql_query($update_sql); 
            mysql_free_result($update_result);

    Hope it helps....

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by immohito View Post
    What do you mean echo my entire variable? I posted the entire chunk of code right in the beginning!
    you posted the code, but that's not what mysql sees

    mysql sees the sql ~after~ the $Name and $new_id and $id variables have been replaced by actual values

    see http://ca.php.net/manual/en/function.echo.php

    put it here --
    Code:
    $update_sql = "UPDATE ... ";
    
    echo $update_sql 
    
    $update_result = mysql_query($update_sql);
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh ok, I got that. And tried it. It was showing the value of the variable id in double quotes. So basically the function I used to create the unique ID for every instance of the form had a variable with single quotes in it. Anyway, I removed that and there is no more MySQL syntax error. However, my data still doesn't get saved back to the database. I tried trouble shooting, and removed the updating of id to $new_id but it still doesn't work. Please see if you can help here is the code.

    PHP Code:
    $update_sql "UPDATE projects SET Name='$name' WHERE dex=$dex;";
            echo 
    $update_sql;
            
    $update_result mysql_query($update_sql);
            
            
            
    mysql_free_result($update_result); 
    gives this output:

    UPDATE projects SET Name=''kbdsb'' WHERE dex='{43D135BF-BE9E-8358-0213-0F8B5E8DD94E}';

    However, the name column in my database is not being updated. Its text type. Does the length matter?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by immohito View Post
    UPDATE projects SET Name=''kbdsb'' WHERE dex='{43D135BF-BE9E-8358-0213-0F8B5E8DD94E}';
    well, there's the problem

    somehow, you are adding an extra set of single quotes

    after you echo the sql, copy/paste it into mysql, and you will see that it does produce an error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah actually there is an error. I used mysql_error(). Could not run 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 'gkwde'' WHERE dex='{71C50C85-E4F2-987A-833F-2B0F00B63224}'' at line 1

    But I can't seem to figure out where I have added the extra set of single notes.

    Here's the function I use to create my id.
    PHP Code:
    function create_guid() {     
        
    //--------------------------------------------------------------------- 
        // A function to create a unique ID 
        //--------------------------------------------------------------------- 
        
    static $guid ''
        
    $uid uniqid(""true); 
        
    $data $namespace
        
    $data .= $_SERVER['REQUEST_TIME']; 
        
    $data .= $_SERVER['HTTP_USER_AGENT']; 
        
    $data .= $_SERVER['LOCAL_ADDR']; 
        
    $data .= $_SERVER['LOCAL_PORT']; 
        
    $data .= $_SERVER['REMOTE_ADDR']; 
        
    $data .= $_SERVER['REMOTE_PORT']; 
        
    $hash strtoupper(hash('ripemd128'$uid $guid md5($data))); 
        
    $guid '{' .   
            
    substr($hash,  0,  8) . 
            
    '-' 
            
    substr($hash,  8,  4) . 
            
    '-' 
            
    substr($hash12,  4) . 
            
    '-' 
            
    substr($hash16,  4) . 
            
    '-' 
            
    substr($hash2012) . 
            
    '}'
        return 
    $guid


  15. #15
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can anyone help me figure this out? Its really frustrating!

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the single quotes around the guid are needed

    the extra single quotes around $name are what's giving the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Which of the guid needs single quotes. I just removed them from $name and now everything's getting saved. Thank you so much. Everything is working now. Except when I try to update the id. Can't you update a field whose value you are using in the condition for the UPDATE-SET command. Should I write another UPDATE statement to update the id's value to that of $new_id? Here's the statement.

    PHP Code:

    $update_sql 
    "UPDATE projects SET dex={$new_id}, Name=$name, Designation=$designation, Phone=$phone, Date=$date WHERE dex=$dex ";
            echo 
    $update_sql;
            
    $update_result mysql_query($update_sql);
            if (!
    $update_result) {
            echo 
    'Could not run query: ' mysql_error();
            exit; 
    This code gives the following error:

    Could not run query: Unknown column '9A17' in 'field list'

    Here's what echo $update_sql returns:

    UPDATE projects SET dex={B0EE358D-9A17-FE7A-089C-C7DD2E02A27D}, Name='bcksbkd', Designation='blkcdsl', Phone=6302732, Date='blkcsnl' WHERE dex='{B1FD2661-1DB9-0778-8A1E-AEC8E4611B5D}'

    So basically its a part of the $new_id that's causing the error. But I don't know why. Is there a problem in the create_guid function?

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by immohito View Post
    Here's what echo $update_sql returns:

    UPDATE projects SET dex={B0EE358D-9A17-FE7A-089C-C7DD2E02A27D}, Name='bcksbkd', Designation='blkcdsl', Phone=6302732, Date='blkcsnl' WHERE dex='{B1FD2661-1DB9-0778-8A1E-AEC8E4611B5D}'
    that thing in red there, that needs quotes around it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You need to remove {} from $new_id and add '(single quote) around each variables:

    $update_sql = "UPDATE projects SET dex='$new_id', Name='$name', Designation='$designation', Phone='$phone', Date='$date' WHERE dex='$dex' ";

  20. #20
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help tom8, r937, maxdream01 and scout1idf. The form is working perfectly. The id is getting updated in my database, however it is not getting updated in the URL after the user submits the form. I don't know if I should be bothered though lol. Thanks again. I'll bother you guys again if I get stuck somewhere.


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
  •