SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Only Update If Entry In Cell?

    Lets say you have a form which allows someone to change there personal data in a database. The user only wants to change their phone number and leave everything else the same does anyone know how to change the row with the phone number in?

    My current code updates all the rows in the form which are left blank as blank inputs.

    Current Database:

    First Name: Joe
    Surname: Bloggs
    Phone Number: 123456789

    Update Form:

    First Name:
    Surname:
    Phone Number: 987654321

    Incorrect Updated Database:

    First Name:
    Surname:
    Phone Number: 987654321


    Correct Updated Database:

    First Name: Joe
    Surname: Bloggs
    Phone Number: 987654321



    I need something that says: If blank do not update.





    Code:
    function mysql_real_escape_array($t)
    {
        return array_map("mysql_real_escape_string",$t);
    }
    
    function trim_array($ar)
    {
        return array_map("trim",$ar);
    }
    
    if(isset($_POST['form_id']))
    {
        $_POST = mysql_real_escape_array($_POST);
        $_POST = trim_array($_POST);
        $error = "";
    
     
        if($error == "")
        {
            $sql = "
            UPDATE
                users
            SET
                category = '".$_POST['category']."',
    		linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
                firstname = '".$_POST['firstname']."',
                surname = '".$_POST['surname']."',
                email = '".$_POST['email']."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."'
    			         WHERE 
    			id=$id";
    
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
    
        }

  2. #2
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    if(isset($_POST['form_id']))
    {
        
    $values '';
        foreach (
    $_POST as $key => $val
        {
            if ((
    $val != '') && ($key != 'form_id')) {
                
    $values .= "{$key} = '" mysql_real_escape_string(trim($val)) . "', ";
            }
        }
        
    $values rtrim($values', ');
        
        
    $error "";
        
        if(
    $error == "")
        {
            
    $sql "UPDATE users SET {$values} WHERE id={$id}";
            
    $result mysql_query($sql) or die("An error occurred ".mysql_error());
        }
    }
    ?>
    Keith
    Freelance web developer
    http://www.duvalltech.com/

  3. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Many thanks,

    I tried that code. Its very different to what I have. It creates the following error which I haven't seen before:

    "An error occurred 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"

    Does the code pick up all the rows in the database which could be completed:

    Code:
    firstname = '".$_POST['firstname']."',
                surname = '".$_POST['surname']."',
                email = '".$_POST['email']."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."'

  4. #4
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It doesn't pick up rows from the DB. It loops through the fields in the POST array and adds them to the update SQL only if they have been filled in. Doing it that way will prevent it from clearing any fields that the user didn't change.

    Post the value of $sql so we can see where the syntax error is.
    Keith
    Freelance web developer
    http://www.duvalltech.com/

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    [Offtopic]

    @kduv ;

    You use this to test if the form is sent but want to discount it from your sql-string generator, well so it seems:
    PHP Code:
    if (($val != '') && ($key != 'form_id')) { 
    whereas I tend do this:
    PHP Code:
    if( isset($_POST['form_id']))}
    unset(
    $_POST['form_id']); 
    I find that more explicit, whaddya (or anyone else) think?

    [/Offtopic]

    Back on topic ...

    hmmmm.... form_id wouldn't be the value of $id in the non-performing sql string would it?

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    $id, i'm assuming (hoping) is a session-stored userid field.

    I'd do it slightly differently;
    PHP Code:
    $interested_keys = array('firstname' => '','surname' => '' .......); //Establish what fields you're interested in.
    $postvars array_intersect_keys($_POST,$interested_keys); //Remove unneeded values.
    $postvars array_filter($postvars,function ($element) { return !empty($element) }); //Remove empty values.
    foreach($postvars AS $key => $value) {
     
    $stuff[] = $key." = '".$value."'";
    }
    $sql "UPDATE users SET ".implode(",",$stuff)." WHERE id={$id}"
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    730
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I tried this code and it has the same error.

    Im not sure what you mean by "Post the value of $sql so we can see where the syntax error is" Where do I get this information from?


    Code:
     <?php
    if(isset($_POST['form_id']))
    {
        $values = '';
        foreach ($_POST as $key => $val) 
        {
           
    if( isset($_POST['form_id']))
    unset($_POST['form_id']); 
    	   
    	   {
                $values .= "{$key} = '" . mysql_real_escape_string(trim($val)) . "', ";
            }
        }
        $values = rtrim($values, ', ');
        
        $error = "";
        
        if($error == "")
        {
            $sql = "UPDATE users SET {$values} WHERE id={$id}";
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
        }
    }
    ?>

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    They mean this:
    Code:
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
    =>
    Code:
            $result = mysql_query($sql) or die("An error occurred ".$sql." ".mysql_error());
    so you can see what the query was when it was executed. At that point you can copy that text.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


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
  •