suppose we have this
seriailizedArray=array(‘gender’=>Male,‘age’=50,other=‘other’…
which has been serialized and put into database field
//retrieve the serialized array from the database
//suppose it has
$sql = 'SELECT seriailizedArray FROM table_name WHERE id = 1';
$rs = mysql_query($sql); $field = mysql_fetch_assoc($rs);
$serializedArray = $field['seriailizedArray']; //unserialize it
$newArray = unserialize($serializedArray); //print it
print '<pre>';
print_r($newArray);
print '</pre>'; //or implode the array
print implode(', ', $newArray);?>
select query just shown to show table structure
suppose that array contents 10 items with a item name age as second item with value 10
it is stored in mysql array after serialization
Is it possible to update that age inside serialized mysql query directly
with OUT THESE STEPS(one way can be this)
select serialize data from database
unserialize it
change array
then again serialize
then update
can it be shorter than that?
if we just want to unpdate 1 thing there was many waste there(unnessary queries,updates)…espcially if array is large
plus
how can an we do age search with out deserialization?(possible?)
There’s no shortcut. The right way to do it is to fix your database design – every column should be atomic (single valued). Stuffing an array into a column is bad design, and leads to issues like these. You should probably have a table with a one-to-many relationship to these records holding the associated values of this array… can’t say that for sure without knowing more about the data though.
yes,most of the time i also thing column should be atomic
BUT
there are some occasions where things like these can come quite handy.
(otherwise it might not have been invented at first place)
in our case,we are planning to use it to implement a custome filed values
ok we can have custom field values for each value of each custom field but another logic to it
why to increase table rows if it can be managed in single row…
i have confirmed…wordpress use this…seems like drupal does the same…
any way,i have made some shortcut hacks which does the task without deserialize,but dont know it is optimized or not…so testing
any way keep the good thought coming
my eyes and brain both tells me it can be managed in single row and it is not a new thing ,it has been being done from years now
i have already said even wordpress does this…
just i am trying to improve it,if it could be…
any way still trying …
lets see whats on
the word “wordpress” does not mean “excellent example to us all”.
you don’t know why wordpress does it
so, you cannot apply it to your case.
“can be managed” and “been being done from years” cannot prove anything.
One can use microscope as a hammer. And even with success. Until they will need a microscope itself.
Now you faced a fact that serialized array is not the best solution, but refuse to use general way, from the elementary school. With ridiculous reason “why to increase table rows”. Tables were invented to store rows of data. Nothing bad with rows in the database. Contrary, it is bad to store arrays in the cell. So, you’re changing good design for bad design. What for?
It is true that WordPress uses serialized data in some db fields. But AFAIK none of them require part of it to be changed independently of the rest of it. For the way you are using it, it is not an adequate solution for your needs and a different database architecture is justified. Or do the unserialize/change/serialize thing.
It depends on the circumstance but most the time its best to keep fields atomic. However, in some situations such as storing session data in the database it wouldn’t be practical to keep every value within the session array as a single field. Its much simpler to serialize and unserialize so long as you don’t actually need to reference individual values within a query itself.
thanks
ok,i am not saying it is the best model and what drupal,wordpress does is fullproof in the world…even many things they do for security are not fullproof thats why sometime some site gets hacked…
but having said that ,they just dont implement something for fun or to test.They can see definate advantage there, so they are doing it.
For eg,
-small setting valus which are linked in a way or other.(single setting may have 20 values related to it,eg video-width,height…so on)
-The value of custom fields
I also fill these, prime candidates to be implemented with serialize.
customfields(c_id,name…)
customfeilds_values(id,c_id,value…
two models now
that “value” field can hold value for each custom field
or all values for different custom field as serialized array.
There are many cases where the custom field are purely dependent on users,can grow in any numbers so i find it good to be implemented using serialize.
suppose
if one article has 15 custom fields
It should make 16 inserts
It should make 16 row updates
Even in search in 1x16 rows for each article (in some situation)
and table will grow at the rate of 1(article)x16
so when we see serialize and unserialize has some disadvantage ,this model is also not free of hussle.
plus imagine,when we say 16 or 1600 it should not sound bad,but if we think 16 million instead of 1 million ,which one will the programmer take?
any way thanks for thought.
Keep it coming.
I am planning to implement a model so open to everything.
thanks
If they have to update any of that information regularly, they’ll take the 16 million, easy. An UPDATE query on an atomic column will be much more than 16 times faster than SELECT, buffer to memory, transfer to PHP, read from memory, unserialize, modify, serialize, buffer to memory, send to MySQL, UPDATE.
A growth rate of 16*n, in terms of time complexity, is no big deal. Might as well ignore the constant factor. If it grew cubically or quadratically or exponentially, then maybe you need to worry.
WordPress custom fields are almost never modified except by an administrator editing a post, which is a rare event and it doesn’t matter if it’s very performant or scales well. Does the same apply for your use case?
I doubt if it would be any easier or faster, but serialized data are strings. So you could use string functions on them and replace the portion(s) you wanted to and UPDATE the new string. IMHO might as well just unserialize and work with the array.
ok thanks
1x(serailze+update)>16 update?
didn’t knew that.Any way i think it depends on array as well.I will give it a test using some PHP classes.
Yes,i agree ,16*n is not great deal ,my intension was if it could be made smaller then why not to go for it.
Now my decision model has been
if (1x(serailze+update)>16 update)
or
(1x(serailze+search)>16 searches)
if answer is yes ,i should not use it.
My feeling was why to have 16 rows for every article.
Wordpress does implement it for custom fields as well which are changed when post is edited.I use video custom field data of which is kept in serialized form.
My situation ,well i am planning for ,suppose custom field for article,which may be changed never by user or changed every day by user.So occurance of that event is not fix
No. It is 1 update for 16 fields. Major difference.
Any way i think it depends on array as well.I will give it a test using some PHP classes.
keep it coming
Why bother asking on the forums if you are going to ignore the advice from some very good PHP/database programmers? You can test it all you like but storing a serialized array is a terrible idea.
My advice: do as Dan said (make a separate customer table) and stop thinking you know better than everybody else.
i fully respect the “good php/database programmers”
and it is not about ignoring things…if i was to ignore i would not have asked but rather said,“I am doing this…so on”
plus i have been saying i am open to any thing until it is valid
Me as well have decided that may be i need to make separate row for each custom field value.
But it was all about creative discussion and forwarding valid points…to reach to a conclusion…
any way for now it seems that “though serialize in single row seems to be shorter and small,there are different problems associated with its database operation which mitigates its benefits…”
so may be i need to make different rows for each
