Simple UPDATE MySQl table from php array

Question

I am trying to put together a function that does the following:

1. retrieve a JSON encoded string from a form
2. decode the string to a php array
3. loop through the generated php array to get the values for each part of the array so that I can update a MySql table

Here is my function code so far:

public function saveTestimonials() {


    $existing_testimonials_update = $this->post('data');

    $update_array = json_decode($existing_testimonials_update);

    foreach ($update_array as $key => $testimonials) {
         foreach($testimonials as $key => $value) {
            //echo "$key = $value\n";

        }
    }

    $db = Loader::db();
    $sql = "UPDATE testimonials SET name=var, content=var WHERE id=var";
    $db->query($sql);

    $this->redirect('/dashboard/testimonials/');

}

Here is the array stored in the $update_array variable:

Array
(
[0] => stdClass Object
    (
        [id] => 1
        [name] => Mr. John Doe, Manager, ABC Ltd
        [content] => my content 1.
    )

[1] => stdClass Object
    (
        [id] => 2
        [name] => Mr. Joe Smith, Manager, ABC Industries
        [content] => my content 2.
    )

[2] => stdClass Object
    (
        [id] => 3
        [name] => Mr. Mike Smith, Manager, ABC Industries
        [content] => my content 3.
    )

[3] => stdClass Object
    (
        [id] => 4
        [name] => Ms. Jane Doe, Manager, ABCD Ltd
        [content] => my content 4.
    )

)

I have got steps 1 and 2 working fine, however I am stuck on step 3.

I still learning PHP and struggle with syntax at times. I have tried to work this one out on my own and have spent several hours on it, but just can't seem to figure this one out.

Any assistance is very much appreciated.

Answer 1

foreach ($update_array as $key => $testimonials) {
    $name = mysql_real_escape_string($testimonials->name);
    $content = mysql_real_escape_string($testimonials->content);
    $id = intval($testimonials->id);

    $sql = "UPDATE testimonials SET name='$name', content='$content' WHERE id=$id";
    $result = mysql_query($sql);
    if ($result === FALSE) {
        die(mysql_error());
    }
}

Answer 2

I'm using something like this. Might be a help to you!

function updateDbRecord($db, $table, $carry, $carryUrl) {   
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table . " WHERE Field NOT IN ('id')");
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $array = array_intersect_key( $_POST, array_flip($fieldnames) );
        }
      }
      foreach ($array as $key => $value) {
        $value = mysql_real_escape_string($value);
        $value = "'$value'";
        $updates[] = "$key = $value";
      }
      $implodeArray = implode(', ', $updates);
      $sql = sprintf("UPDATE %s SET %s WHERE id='%s'", $table, $implodeArray, $_POST['id']);
      mysql_query($sql);
      if ($carry == 'yes') {
        redirect($carryUrl.'?id='.$_REQUEST['id'].'&'.$table);
      } else { echo "Done!"; }
}

Answer 3

function update($table_name, $myarray, $my_wheres) {
    $sql = "Update`".$table_name.
    "` SET ";
    $i = 0;
    foreach($myarray as $key => $value) {
        $sql.= $key." = '".$value."'";
        if ($i < count($myarray) - 1) {
            $sql.= " , ";
        }
        $i++;
    }
    if (count($my_wheres) > 0) {
        $sql.= " WHERE ";
        $i = 0;
        foreach($my_wheres as $key => $value) {
            $sql.= $key.
            " = ".$value;
            if ($i < count($my_wheres) - 1) {
                $sql.= " AND ";
            }
            $i++;
        }
    }

    return mysqli_query($sql);
}

Hope, this code can help you. Not try one by one update i think. Think performance.

Answer 4

These are objects you're dealing with inside of the update_array, so you should be able to access them like this:

$update_array = json_decode($existing_testimonials_update);

foreach ($update_array as $key => $testimonials) {
     $testimonials = (array) $testimonials;
     foreach($testimonials as $key => $value) {
        //echo "$key = $value\n";

    }
}

Or, more simply, you can use the arrow operator ($testimonials->name) to access the variables.

Answer 5

You are getting an object from json_decode(). If you pass true as second argument to json_decode() you get an associative array.

http://php.net/manual/de/function.json-decode.php

Answer 6

try something like this

 $db = Loader::db();

foreach ($update_array as $key => $testimonials) {
         foreach($testimonials as $testimonial) {

             // escape data to avoid sql injection
             $id = mysql_escape_string($testimonial->id);
             $name = mysql_escape_string($testimonial->name);
             $content = mysql_escape_string($testimonial->content);

             $sql = "UPDATE testimonials SET name='$name', content='$content' WHERE id='$id'";
             $db->query($sql);

             // TODO check for database error here

        }
    }