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
