Updating Table Field With An Array In PHP

Question

I want to uapdate a table field with an array.That means i have a table with name test.In that table i inserted some rows first.

After some process i want to update a filed in this table with ana array value.When i inserting rows into this table at that time this filed is set to 0.

After some process some values returned to a variable $var like:

Array
(
    [id] => "http://www.google.com/calendar/"
    [etag] => "GEwNTgxHfip7JGA6WhJV"

    [link] => "http://www.google.com/calendar/"

)

This is the code iam using for updating field:

$idg= json_encode($var);
mysql_query("UPDATE `test` SET `tk` = '$idg' WHERE `TID` =10");

The above code is the edited code .The $idg now have:

{"id":"http:\/\/www.google.com\/calendar\/feeds\/testid%40gmail.com\/events\/64h9vc0qqqtlqeqhudhhdqbsds","etag":"\"GEwCQwxGeSp7JGA6WhJV\"\r","link":"http:\/\/www.google.com\/calendar\/feeds\/testid%40gmail.com\/private\/full\/64h9vc0qqqtlqeqhudhhdqbsds\r"}

After excuting this code the field will updated with null value.This is the problem facing now.

your $var is an array. you need to access one of its element. like $var['id'] — hjpotter92
– hjpotter92, Commented Mar 23, 2012 at 10:12
you mean i cant to update the $var array completely into the table? — Kichu
– Kichu, Commented Mar 23, 2012 at 10:13

VolkerK · Accepted Answer · 2012-03-23 11:30:36Z

1

Since your table column can only contain a scalar value you have to convert the array into some string representation, e.g. via json_encode().
When you fetch the value from the table again you have to convert it back, in this case via json_decode().

edit: or use a storage mechanism/database that can handle this type of data, e.g. a document based database like mongodb.

edit2: add parameter encoding, error handling and debug code

<?php
echo "start\n";

$tid = 10;
$idg = json_encode($var);
$idg_sql = mysql_real_escape_string($idg);

$query = "
    UPDATE
        `test`
    SET
        `tk` = '$idg_sql'
    WHERE
        `TID` = $tid
";

$result = mysql_query($query);
if ( !$result ) {
    die(mysql_error());
}

if ( 1 < mysql_affected_rows() ) {
    // debugging
    echo "no affected rows\n";
    $result = mysql_query("SELECT Count(*) as c FROM `test` WHERE `TID`=$tid") or die(mysql_error());
    while( false!==($row=mysql_fetch_assoc($result)) ) {
        var_dump($row);
    }
}

echo "done.\n";

edited Mar 23, 2012 at 11:30

answered Mar 23, 2012 at 10:14

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

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

4 Comments

Kichu Over a year ago

This is the query iam using with json data . UPDATE test SET gk = '{"id":"http:\/\/www.google.com\/calendar\/feeds\/test%40gmail.com\/events\/f8uhsjkqvneqif0b0qucdetfog","etag":"\"GEwCRQZIfSp7JGA6WhJV\"\r","link":"http:\/\/www.google.com\/calendar\/feeds\/test%40gmail.com\/private\/full\/f8uhsjkqvneqif0b0qucdetfog\r"}' WHERE TID =91.But it is not updated when excuted this query.But it is updated when i directly giving in phpmyadmin's sql.What is the reason for this?

VolkerK Over a year ago

we need all the relevant php code to answer this question... btw: if you store structured data in a single column your relational database won't be of much help if you want to query records based on the data in that column. E.g. a query like "How entered the link having the etag GEwCRQZIfSp7JGA6WhJV" will result in a full table scan + your php script doing the actual comparison.

Kichu Over a year ago

@ VolkerK : Now i can update this value , But i cant to retrieve / display this value , What is the reason?

Kichu Over a year ago

This is the code iam used to select value:

$sql_sel_now = "SELECT * FROM `test` WHERE `TID` = 10"; $res_sel_now = mysql_query($sql_sel_now); $result_sel_now = mysql_fetch_row($res_sel_now); $tk = $result_sel_now[20]; $jtk = stripslashes($tk); $rtk = json_decode($jtk); print_r($rtk);

.When iam print the value nothing will be displayed .What is the reason?

OM The Eternity · Accepted Answer · 2012-03-23 10:21:36Z

0

Convert you array into a string representation using json_encode() then the obtained string can be easily stored in the DB table field

answered Mar 23, 2012 at 10:21

OM The Eternity

16.2k44 gold badges127 silver badges187 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 19:53:52Z

0

To store whole array in database convert it to string by serialize() and while reteiving it unserialize() it.

edited Nov 17, 2023 at 19:53

CommunityBot

11 silver badge

answered Mar 23, 2012 at 10:23

heyanshukla

6698 silver badges17 bronze badges

Comments

Balaji Kandasamy · Accepted Answer · 2012-03-23 10:24:16Z

0

Convert array into string value then insert into the query. For example:

<?php

    $var = Array('id' => "http://www.google.com/calendar/",'etag' => "GEwNTgxHfip7JGA6WhJV",'link' => "http://www.google.com/calendar/");
    foreach($var as $var1){
    $string .= $var1.",";
    }
    $var = substr($string,0,-1);
    echo "UPDATE `test` SET `gk` = '$var' WHERE `TID` =77"; //printing query to check the values

?>

answered Mar 23, 2012 at 10:24

Balaji Kandasamy

4,50410 gold badges42 silver badges58 bronze badges

Comments

BenMorel · Accepted Answer · 2013-12-30 19:10:14Z

0

You need to store the data in a string. You could either use php's serialize() function OR use json_encode() and json_decode() when needed.

edited Dec 30, 2013 at 19:10

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Mar 23, 2012 at 10:28

nickw444

9662 gold badges9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Updating Table Field With An Array In PHP

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related