Only Update If Entry In Cell?

PHP
1

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.

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 

<?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());
    }
}
?>
3

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:

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

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.

5

[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:


if (($val != '') && ($key != 'form_id')) {

whereas I tend do this:


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

$id, i’m assuming (hoping) is a session-stored userid field.

I’d do it slightly differently;


$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}";
7

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?

 <?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

They mean this:



        $result = mysql_query($sql) or die("An error occurred ".mysql_error());

=>



        $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.