php update to mysql with array not updating

Question

I have a function that should be updating a row, no errors what so ever in the logs. From what I see it should be working. I took the function from a update user function and tried to mimic it for the new feature.

Here is the data array posting to it.

$data = array('id' =>  $vid, 'name' => $vname, 'logo' => $vlogo, 'info' => $vinfo, 'site' => $vsite, 'est' => $vest);

The post works, I am doing a dump on the updatecompany page. So they do get set. I think it may be with the function. Any insight would be wonderful!

public static function updateCompany($toUpdate = array(), $company = null){
  self::construct();
   if( is_array($toUpdate) && !isset($toUpdate['id']) ){
    if($company == null){
    echo "No company ID set!";
  }
  $columns = "";
  foreach($toUpdate as $k => $v){
    $columns .= "`$k` = :$k, ";
  }

  $sql = self::$dbh->prepare("UPDATE companys SET {$columns} WHERE `id` = :id");
  $sql->bindValue(":id", $company);
  foreach($toUpdate as $key => $value){
    $value = htmlspecialchars($value);
    $sql->bindValue(":$key", $value);
  }
  $sql->execute();

  }else{
  return false;
 }
}



$vid = $_POST["idnum"];
$vname = $_POST["name"];
$vlogo = $_POST["logo"];
$vinfo = $_POST["info"];
$vsite = $_POST["site"];
$vest = $_POST["est"];

maybe this line $columns .= "`$k` = :$k, "; should be $columns .= "`$k` = :$v, "; — happymacarts
– happymacarts, Commented Jan 7, 2017 at 3:44
are all of the values in that array defined? There doesn't seem to be enough info here to help you figure it out — happymacarts
– happymacarts, Commented Jan 7, 2017 at 4:24
yes i just added that to show you, the var dump on the update page also shows them fine. — ajankuv
– ajankuv, Commented Jan 7, 2017 at 4:35

Rasclatt · Accepted Answer · 2017-01-07 05:27:57Z

I would maybe try using the bind array into your execute() method since you are just binding the values (I assume this is PDO). Another feature is to use an array to assemble the column portions and implode at the time of use.

public static function updateCompany($toUpdate = array(), $company = null)
    {
        # Also this may supposed to be: self::__construct(); ?? Notice the "__" before "construct"
        self::construct();

        if(is_array($toUpdate) && !isset($toUpdate['id'])) {
            if(empty($company)){
                # Throw exception, catch it in a parent wrapper
                throw new \Exception("No company ID set!");
                # Stop, no sense in continuing if an important part is missing
                return;
            }

            foreach($toUpdate as $k => $v){
                $bKey        = ":{$k}";
                # I would create a bind array here
                $bind[$bKey] = $value;
                # I generally save to an array here to implode later
                $columns[]   = "`{$k}` = {$bKey}";
            }
            # Add the id here
            $bind[":id"] = $company;
            # I would use a try here for troubleshooting PDO errors
            try {
                # Create sql with imploded columns
                $sql = self::$dbh->prepare("UPDATE companys SET ".implode(",",$columns)." WHERE `id` = :id");
                # Throw the bind array into the execute here
                $sql->execute($bind);
            }
            catch(\PDOException $e) {
                # I would only die here to see if there are any errors for troubleshooting purposes
                die($e->getMessage());
            }
        } else {
            return false;
        }
    }

Codemole · Accepted Answer · 2017-01-07 04:55:44Z

1

Your update SQL could not work because you have comma in update value set.

See, you attached comma without any consideration:

  $columns = "";
  foreach($toUpdate as $k => $v){
    $columns .= "`$k` = :$k, ";
  }

Then the final SQL will look something like this:

UPDATE companys SET `name`=:name, `logo`=:logo, WHERE `id`=:id

Do you notice the comma just before WHERE? it should not be there!

So you should update the code like following:

$columns = "";
foreach($toUpdate as $k => $v){
  if ($columns != "") $columns .= ",";
  $columns .= "`$k` = :$k ";
}

This should be working.

answered Jan 7, 2017 at 4:55

Codemole

3,2095 gold badges28 silver badges42 bronze badges

1 Comment

ajankuv Over a year ago

Both your's and @Rasclatt worked. I had another issue with the update \Fr\LS::updateCompany($data, $vid); i never declared the $vid at the end i expected the array to take care of it

Igor Mizak · Accepted Answer · 2017-04-07 11:08:04Z

0

or to no need to be aware of last comma try this:

$columns = array();
foreach($toUpdate as $k => $v){
    $columns[] = "`$k` = :$k";
}

$sql = self::$dbh->prepare("UPDATE `companys` SET ".implode(',', $columns)." WHERE `id` = :id");

answered Apr 7, 2017 at 11:08

Igor Mizak

1,1081 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

php update to mysql with array not updating

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related