1

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

eg serialize data somhing like this

a:23:{i:10000011;a:5:{s:15:"social_users_id";s:8:"10000022";s:10:"friends_id";s:8:"10000011";s:8:"relation";s:1:"1";s:6:"status";s:1:"0";s:4:"html";s:41:"Pradeep Kumar has update profile picture.";}i:10000015;a:5:{s:15:"social_users_id";s:8:"10000022";s:10:"friends_id";s:8:"10000015";s:8:"relation";s:1:"6";s:6:"status";s:1:"0";s:4:"html";s:41:"Pradeep Kumar has update profile picture.";}i:10000016;a:5:{s:15:"social_users_id";s:8:"10000022";s:10:"friends_id";s:8:"10000016";s:8:"relation";s:1:"3";s:6:"status";s:1:"0";s:4:"html";s:41:"Pradeep Kumar has update profile picture.";}i:10000018;a:5:

OR Array

[10000015] => Array
    (
        [social_users_id] => 10000022
        [friends_id] => 10000015
        [relation] => 6
        [status] => 0
        [html] => Pradeep Kumar has update profile picture.
    )

[10000016] => Array
    (
        [social_users_id] => 10000022
        [friends_id] => 10000016
        [relation] => 3
        [status] => 0
        [html] => Pradeep Kumar has update profile picture.
    )

I want to select and update 'status' index of the array '10000015' I have use longtext datatype of the coloum of the table.

I want to first search that status index and after that update, can we d with query Or I have to use PHP code for this..

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?)

any ideas... thanks

Pradeep Kumar
  • 11
  • 5
  • This seems like a bad database design. However, you could probably use a LIKE to only fetch the rows you need but it will still be slow. – hank May 24 '13 at 06:56
  • 1
    Seems like you should store the serialized array elements as separate column values. – chuckieDub May 24 '13 at 06:58
  • 2
    Right. Why are you serializing if you need to be able to search it? Serialize should be used for opaque storage. – Barmar May 24 '13 at 07:04
  • Yes it is possible. There is even a PHP library that does so: https://github.com/ktomk/Serialized – hakre Jun 29 '13 at 10:01

3 Answers3

2

As far as I know it is not possible to do this, that is the drawback of the use of arrays in MySQL (you need to serialize and unserialize the data all of the time).

Next I see your array look the same so put them in tables.

row => social_users_id|friends_id|relation|status|html

If you want to use array's you could look at NoSQL. I use mongodb with a lot of success. One huge drawback of a NoSQL db is you need to get rid of the SQL mindset. You can't use JOINS for example.

But in MonoDB arrays are supported and "queryable" what is a huge advantage for dynamic data sets

If you really want it you could make a table array and array_item

With array as followed: id|name

And array_item: id|array_id|key|value

Now you can say on array item I want the value of array id x

But a lot of other stuff too

Like i want the array where the key y and the value needs to be x

note this dataset will become huge overtime and is hard to read

MKroeders
  • 7,562
  • 4
  • 24
  • 39
  • So you are telling me tha is not possible i mysql? – Pradeep Kumar May 24 '13 at 07:07
  • No we can not store these data into another table that will unnecessary data into database, for one tasks we can do in only one coulom, I have all the data in one time, why not we go for single coulom. Can we use SUBSTRING_INDEX to find out the array in serialize data on the basis of [10000015] of an array – Pradeep Kumar May 24 '13 at 07:21
  • Well is it partialy possible, you could use a regex http://dev.mysql.com/doc/refman/5.1/en/regexp.html. I but I highly discourage it – MKroeders May 24 '13 at 07:40
  • http://stackoverflow.com/questions/11916097/how-to-update-mysql-row-that-has-serialized-data – Pradeep Kumar May 24 '13 at 07:40
  • 1
    As said in the answer it is looking for trouble. "Note that there is potential for trouble" – MKroeders May 24 '13 at 07:41
1

Strictly speaking, any row of your database can be updated, of course. The point is : is it really a good idea to proceed like this ?

Don't forget that serialization follows rules ans specifications. s:15:"social_users_id" means that the unserialize() function will process a string of 15 characters. That's an example, there are other rules.

So yes, strictly speaking, you can update your string in your serialized array but you have to compute specifications and because you don't really want to rewrite a serializer yourself, you will for sure, sooner or later, break your serialization and mess your data.

Anyway, if you want more details about specifications, check these comments on the PHP manual.

The other point is that obviously, if you need to update a field, then this field must exist as a field, not as a substring. That's weak database design and this point will also, sooner or later, lead you into troubles.

Now, if you combine both drawbacks, well... you guess ;o)

ChristopheBrun
  • 1,237
  • 7
  • 22
  • Thanks for idea, I thing my database design is very optimum and good, I just want to find out an array from multidimensional array which are in serialize formate and I wat to do with MYSQl query so that it will take not more than 2 second if I have lot of data in heavy traffic, thats why I am suing serialize all the array and put into one coloum,and operation will be done with those array which are which array belongs to logged user id, and logged user id will be index of the array which are present in serialize formate. Every time I have to update that array o the basis of login user. – Pradeep Kumar May 24 '13 at 07:46
  • @PradeepKumar That's up to you ! FYI, I've added a link to some specification's details in my post. I had better specs once but I can't find them right now. Good luck. – ChristopheBrun May 24 '13 at 08:24
0

The short answer is no, serialized data must be unserialized,then edited, then reserialized unless you want to run regex functions like preg_replace(); which is fairly intensive processing.

My commendation is to just store the values separately in the database.

chuckieDub
  • 1,767
  • 9
  • 27
  • 46
  • No we can not store these data into another table that will unnecessary data into database, for one tasks we can do in only one coulom, I have all the data in one time, why not we go for single coulom. Can we use SUBSTRING_INDEX to find out the array in serialize data on the basis of [10000015] of an array – Pradeep Kumar May 24 '13 at 07:20
  • http://stackoverflow.com/questions/4116419/mysql-select-query-within-a-serialized-array – Pradeep Kumar May 24 '13 at 07:22