PHP update statement with arrays

Question

Hi so I have an insert statement which works well, but need to create a separate update function which uses array keys and array values, which would be quite like the insert function but updates.

I have this for my insert

$sql = "INSERT INTO $tablename (".implode(",", array_keys($DATA).")" . " DATA ('".implode("','",array_values($DATA))."')";
connect()->query($sql);

This is what I have for my update so far but am stuck with it,

<?php
    function updatethis (array $id, array $values, $tablename)
    {
        $sql = "UPDATE $tablename SET (".implode(",", array_keys($DATA)).")" . " DATA ('".implode("','",array_values($DATA))."')";
        dbconnect()->query($sql); 
    } 
?>

Therefore I would like help on the update feature please .

So I am getting an error with the UPDATE syntax

This is the part i am struggling with, i cna give further explanation, but i must have put in the wrong syntax to update the database after i click edit on the index page it calls the function just the syntax is incorrect.

also its php to mySQL

index page for PHP updatee fucntion

 {
     $values = array();
     $idValues = array($idColumn => $id);
     foreach($_POST as $key => $value)
     {
         if(!empty($value) && ($value != "Submit"))
         {
             $values[$key] = $value;
         }
     } 
     $result = update($idValues, $values, $tableName);
 }

Edit: Error I am getting edit has not been successfull from below

  if(isset($_POST['Submit']))
                {
                    if($result>0)
                    {
                        echo 'Edit has been successful.  Return to index page';
                    }
                    else
                    {
                        echo 'Edit has not been successful.';
                    }
                }

My code

function updateAll(array $id, array $values, $tablename)
{
    $sIDColumn  = key($id);
    $sIDValue   = current($id);
    $arrayValues = $values;
    array_walk($values, function(&$value, $key){
        $value = "{$key} = '{$value}'";
    });
    $sUpdate = implode(", ", array_values($values));
    $sql        = "UPDATE {$tablename} SET {$sUpdate} WHERE {$sIDColumn} = '{$sIDValue}'";


    connect()->query($sql);
}

My aim: takes the input of the unique identifier of the row to be edited as an array of 1 then the value plus the name of the column representing the primary key, an array containing the values indexed by the column names as well as a string representing the table name useing array_keys and array_vaules like th insert but to update

What do you mean by "but am stuck with it"? Do you get an error(if so, show us the error) or do you get no error? What have you tried so far to solve this problem(this is so we can rule out some possibilities)? Edit the question with these question answered so we can help you better. — BRoebie
– BRoebie, Commented Dec 8, 2015 at 9:19

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ · Accepted Answer · 2015-12-08 11:37:26Z

8

You cannot UPDATE in the same way of INSERT. It should be like this :

$valueSets = array();
foreach($values as $key => $value) {
   $valueSets[] = $key . " = '" . $value . "'";
}

$conditionSets = array();
foreach($id as $key => $value) {
   $conditionSets[] = $key . " = '" . $value . "'";
}

$sql = "UPDATE $tablename SET ". join(",",$valueSets) . " WHERE " . join(" AND ", $conditionSets);

See details here http://dev.mysql.com/doc/refman/5.7/en/update.html

edited Dec 8, 2015 at 11:37

answered Dec 8, 2015 at 9:39

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ

4,2261 gold badge27 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

20 Comments

blueboy123 Over a year ago

Okay thanks, I have to use the function updatethis(array $id, array $values, $tablename) i will have a look at that

blueboy123 Over a year ago

that kind of works but updates all of the fields not just 1

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Over a year ago

You want to update only one field? Which one field? How do you tell your function about that field?

blueboy123 Over a year ago

Hi that's what i am struggling with, i have an idex page where i pefrom the data input, but with the above code it updates every row not just the single row

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Over a year ago

You need to add the where conditions in the last line of my code block

|

Christian · Accepted Answer · 2015-12-08 10:12:12Z

1

I believe the pattern you are using is incorrect?

UPDATE table SET (rows) DATA ('values');

I think updates look more like this:

UPDATE table SET row1 = 'value1', row2 = 'value2';

In which case, this may be closer to what you are looking for.

function updatethis(array $id, array $values, $tablename)
{
    $sIDColumn  = key($id);
    $sIDValue   = current($id);
    $arrayValues = $values;
    array_walk($values, function(&$value, $key){
        $value = "{$key} = '{$value}'";
    });
    $sUpdate = implode(", ", array_values($values));
    $sql        = "UPDATE {$tablename} SET {$sUpdate} WHERE {$sIDColumn} = '{$sIDValue}'";
    dbconnect()->query($sql);
}

Using it, I get this query:

$testArray = array(
    "id" => 19,
    "username" => "test"
);
updatethis(array("id" => 9), $testArray, "users");

UPDATE users SET id = '19', username = 'test' WHERE id = '9'

I hope this at least helps but when it comes to databases, I only know MySQL and it is possible you are using another language.

edited Dec 8, 2015 at 10:12

answered Dec 8, 2015 at 9:39

Christian

1,57712 silver badges16 bronze badges

2 Comments

blueboy123 Over a year ago

Thanks I think this may be nearly there i just get an update function failed though i have added my index page to the top of the page for you to look into

Christian Over a year ago

I have updated my the function a little. If this still does not work, you will need to show us the exact error. If you do not know how to get that error then maybe tell us what database class you are using.

Thomas Rollet · Accepted Answer · 2015-12-08 09:38:20Z

I think you can try something like this : $champs : Array of fields to update $valeurs : Array of value to update fields $conditions : Array of conditions

 protected function modify($table,$champs,$valeurs,$conditions){
            $Requete = "UPDATE ".$table." SET ";
            $nbChamps = count($champs);
            $nbValeurs = count($valeurs);
            if($nbChamps == $nbValeurs){
                for($i = 0; $i < $nbChamps ; $i++){
                    if($i < ($nbChamps - 1)){
                        if(is_numeric($valeurs[$i]))
                            $Requete = $Requete.$champs[$i]." = ".$valeurs[$i].",";
                        else
                            $Requete = $Requete.$champs[$i]." = '".$valeurs[$i]."',";
                    }
                    else
                        if(is_numeric($valeurs[$i]))
                            $Requete = $Requete.$champs[$i]." = ".$valeurs[$i]." ";
                        else
                            $Requete = $Requete.$champs[$i]." = '".$valeurs[$i]."' ";
                    }
                    $Requete = $Requete.$this->genereConditions($conditions);
                    $this->db->query($Requete);
                }
                else
                    throw new Exception("Le nombre de champs n'est pas identique au nombre de valeurs", 1); 
            }

    private function genereConditions($conditions){
            $condition = "WHERE ";
            for($i = 0 ; $i < count($conditions); $i++){
                if($i < (count($conditions)) - 1)
                    $condition = $condition.$conditions[$i]." AND ";
                else
                    $condition = $condition.$conditions[$i];
            }
            return $condition;
        }

Collectives™ on Stack Overflow

PHP update statement with arrays

3 Answers 3

20 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

20 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related