Rodrigo, Yuk! No wonder I was having trouble visualising your problem... The serialised field cannot be meaningfully indexed (it's a waste of time if it is). That means that any search against a criteria involving a/the serialised field will require (the time to perform an) the examination of every row - so make it the last clause of a SELECT - WHERE process.
Given that I would expect that you/users would want to search on such an important field, the db designer/implementer deserves to be first up against the wall... NB if a user issues a fairly open search, eg all programmers (on an IT staffing database) - and let's just say that such represents a 50% 'hit rate' against the total number of rows/people's data stored, then if the table is thousands of names long, such a search for English-Advanced will be very, very 'expensive'/slow! However, I think we can do it. Two possibilities: 1 it may be possible to use a RegEx to examine the serialised text field for "English" and "Advanced" in the correct sequence and proximity. 2 haul all of the rows that satisfy the other WHERE criteria into PHP, deserialise each field, and do the check there and then. The second will be more expensive in terms of RAM and possibly time, but offers a guaranteed result. The first is possibly faster and is more storage-efficient but will require some thought/clever coding. I assume you could manage the first. If you would like me to have a go at tackling the second for/with you, then please provide: a) a copy of the PHP code which serialises the data and stores it in the database, b) a copy of any relevant array/class definitions, c) the MySQL CREATE TABLE line for the TEXT field used, d) (if not evident from the above), a list of some of the more likely languages and the skill levels used within the data, e) (if possible) some sample data to play with/prove. Could be fun, (but it won't be pretty!) Regards, =dn > I mean double ouch!!! :-) > > well.. > > 1 no, it's a select for the language (<select name=language[]) and another > to skill level > > 1b no separator it's stored as array. The array generated by language[] and > skill[] are stored into another array, serialized and put into DB > > 2 yes. could be up to 5 > > 2b again, none. only the array structure > > the field is a text. > > > on 8/26/02 9:58 AM, DL Neil at [EMAIL PROTECTED] wrote: > > > Rodrigo, > > > > Inherited problems = ouch! > > > > Sorry I don't keep old list msgs, perhaps you mentioned this before: does > > the language skills field contain: > > 1 both the language and the skill-level, eg English - Advanced > > 1b what separator is used between the two words? > > 2 more than one language/skill-level where applicable, eg English - > > Advanced, German - Intro. > > 2b what separator is used between language entries? > > > > What is the database field description in the table schema? > > > > Please advise, > > =dn > > > > > > > >> well, I my opinion is not the way to work, but like I said the work was > > done > >> by other guy, and now I have the problem to solve. So i back to old > > question > >> :-) > >> How can I search with some precision in a serialized string? > >> > >> > >> > >> on 8/24/02 12:13 PM, DL Neil at [EMAIL PROTECTED] wrote: > >> > >>> Rodrigo, > >>> > >>> Then the question to be asked is: is serialize()ing this data good > >>> design/technique, or is there a better way? > >>> > >>> Regards, > >>> =dn > >>> > >>> > >> > >> > >> -- > >> PHP General Mailing List (http://www.php.net/) > >> To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> > > > > -- > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
