How to Update multiple columns in PHP, where the input is not empty. Options

[masonh928]

EditProfile

CODE

<?php
session_start();

require_once("Scripts/PHP/Class/Update.class.php");

$DataUpdate = array_filter($_POST);
$Columns = array_keys($DataUpdate);

$ColumnsToUpdate = implode($Columns, ", ");

//$Update = new Update($ColumnsToUpdate, $DataUpdate, $_SESSION['ID']);

?>

DataUpdate is $_POST removing all the empty fields.

$Columns are the columns which are to be updated.

$ColumnsToUpdate should be something like… About, Phone, Gender, etc.

However I want $ColumnsToUpdate to be something like…

About = Hello I was born in *, Phone = 8125633578, Gender = Male, etc.

So I can put that into an Update Query.

Please help thanks…

This post has been edited by masonh928: Jan 20 2016, 03:22 PM

[masonh928]

Please move this to ServerSide Please…

[CharlesEF]

See if this link helps.

[masonh928]

Thanks looking into it!

[masonh928]

The problem with that is it's Insert not Update and Update has a very different setup or method of executing.

Insert…

CODE

INSERT INTO Table (col1, col2) VALUES (val1, val2)

Update…

CODE

UPDATE Users SET col1=val1, col2=val2 WHERE ID = 5

[CharlesEF]

The problem with that is it's Insert not Update and Update has a very different setup or method of executing.

Insert…

CODE

INSERT INTO Table (col1, col2) VALUES (val1, val2)

Update…

CODE

UPDATE Users SET col1=val1, col2=val2 WHERE ID = 5

I don't use PDO but from what I understand using arrays should work regardless of INSET of UPDATE. Build the column array with the '=?' placeholders and then use bindValue/bindParam using an array of values in the same order as the column names.

[masonh928]

Not in this case, but I will update you later… thanks

[CharlesEF]

There is another option, just build the query itself in a loop. Sample, not actual code:

CODE

$sql = "UPDATE Users SET"
foreach($_POST as $key => $value)
{
  if($value != '')
  {
   $sql .= ' ' . $key . '=' . $value
  }
  $sql .= " WHERE ID = 5"
}

[masonh928]

You know I just saw this and I had the same idea!

I have this
Class

CODE

<?php
Class Update
{
    private $UpdateColumn = [];
    private $UpdatedData = [];
    private $ID;
    protected $Connect;
    protected $Data = [];
    public $UpdatedQuery;

    public function __construct($UpdateColumn, $UpdatedData, $ID)
    {
        try {
            $this->Connect = new pdo(**PRIVATE**);
            $this->Connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }

        catch(PDOException $ex) {
            die(json_encode(array(
                'outcome' => false,
                'message' => 'Unable to connect'
            )));
        }

        $this->UpdateColumn = $UpdateColumn;
        $this->ID = $ID;
        $this->UpdatedData = $UpdatedData;
    }

    public function bindData()
    {

        // $this->UpdatedData =  strip_tags($this->UpdatedData);

        $this->Data = [$this->UpdateColumn, $this->ID, $this->UpdatedData];
        return $this->Data;
    }

    public function checkForErrors()
    {
        foreach($this->Data as $Data) {
            $Error = false;
            if (empty($Data)) {
                $Error = true;
            }

            if (!is_Numeric($Data[1])) {
                $Error = true;
            }
        } //foreach close
        return $Error;
    } //function close
    
public function bindQuery()
    {
        $this->UpdatedQuery = "";
        foreach($this->Data[2] as $Arr => $Key) {
            $Key = "'" . $Key . "'";
            $this->UpdatedQuery.= $Arr . "=" . $Key . ",";
        }

        $this->UpdatedQuery = rtrim($this->UpdatedQuery, ",");
        $this->UpdatedQuery.= " ";
        return $this->UpdatedQuery;
    }

    public function Update()
    {
        $Query = $this->Connect->prepare("UPDATE Users SET" . $this->UpdatedQuery . "WHERE ID = :ID");
        $Query->bindValue(":ID", $this->Data[1]);
        $Query->execute();
    }

    public function Redirect($MessageToUser)
    {
        $Msg = urlencode($MessageToUser);
        return header("Location: /Profile?ID=$this->Data[1]&&Msg=$Msg");
    }
} //class close

?>

EditProfile

CODE

<?php
session_start();

require_once("Scripts/PHP/Class/Update.class.php");

$DataUpdate = array_filter($_POST);
$Columns = array_keys($DataUpdate);


$Update = new Update($Columns, $DataUpdate, $_SESSION['ID']);

$Update->bindData();

if($Update->CheckForErrors()){
error_reporting(E_ALL);
//$Update->Redirect("Error Could NOT Update");
}

echo($Update->bindQuery());

if($Update->Update()){

$Update->Redirect("Successfully Updated Your Profile");
exit();
}
?>

I don't see where the syntax error lies.

I get:

Weight="165 LBS"

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '='165 LBS' WHERE ID = '5'' at line 1' in /home/u481336533/public_html/Scripts/PHP/Class/Update.class.php:87 Stack trace: #0 /home/u481336533/public_html/Scripts/PHP/Class/Update.class.php(87): PDOStatement->execute() #1 /home/u481336533/public_html/EditProfile.php(21): Update->Update() #2 {main} thrown in /home/u481336533/public_html/Scripts/PHP/Class/Update.class.php on line 87

===============
Weight="165 LBS" is just test content, where's the error?

This post has been edited by masonh928: Jan 20 2016, 09:21 PM

[masonh928]

I used backsticks and it looks pretty good.

[masonh928]

Anybody see an error that would cause checkForErrors() return true?

[masonh928]

Ok I removed the errors function and everything works perfectly, I'll redesign the error method later.