Simple UPDATE MySQl table from php array

Question

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

retrieve a JSON encoded string from a form
decode the string to a php array
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.

Marc B · Accepted Answer · 2011-10-25 03:22:08Z

5

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

answered Oct 25, 2011 at 3:22

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Comments

Alex · Accepted Answer · 2012-07-01 15:23:50Z

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

Jeff Day · Accepted Answer · 2011-10-25 03:21:28Z

0

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.

answered Oct 25, 2011 at 3:21

Jeff Day

3,7171 gold badge20 silver badges12 bronze badges

Comments

Abenil · Accepted Answer · 2011-10-25 03:24:09Z

0

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

answered Oct 25, 2011 at 3:24

Abenil

1,1583 gold badges12 silver badges28 bronze badges

Comments

bumperbox · Accepted Answer · 2011-10-25 03:25:33Z

0

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

        }
    }

answered Oct 25, 2011 at 3:25

bumperbox

10.2k6 gold badges46 silver badges66 bronze badges

Comments

Metin Erbek · Accepted Answer · 2016-08-31 10:45:28Z

0

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.

edited Aug 31, 2016 at 10:45

answered Aug 30, 2016 at 13:00

Metin Erbek

842 silver badges9 bronze badges

Collectives™ on Stack Overflow

Simple UPDATE MySQl table from php array

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related