storing arrays
Moderator: General Moderators
-
rubberjohn
- Forum Contributor
- Posts: 193
- Joined: Fri Feb 25, 2005 4:03 am
storing arrays
I have an array that stores the values of a variable number of linked select lists. I want to store the values as they are part of a user's profile.
I also want the same system to be used for searching meaning that another user will use the same linked lists to build up search criteria.
I cant store the array in the database so what is the best way fo storing an array of variable length in a database?
Also during the search I want to return partial matches and know which part of the arrays have matched.
I have looked at serialize/unserialize but this won't allow me to search for partial matches because it will only match if the serialized user profile string exactly matches the serialized search criteria string and I dont really want to unserialize every saved profile entry during the search.
any ideas?
thanks
rj
I also want the same system to be used for searching meaning that another user will use the same linked lists to build up search criteria.
I cant store the array in the database so what is the best way fo storing an array of variable length in a database?
Also during the search I want to return partial matches and know which part of the arrays have matched.
I have looked at serialize/unserialize but this won't allow me to search for partial matches because it will only match if the serialized user profile string exactly matches the serialized search criteria string and I dont really want to unserialize every saved profile entry during the search.
any ideas?
thanks
rj
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
-
rubberjohn
- Forum Contributor
- Posts: 193
- Joined: Fri Feb 25, 2005 4:03 am
-
rubberjohn
- Forum Contributor
- Posts: 193
- Joined: Fri Feb 25, 2005 4:03 am
would this still work if i added another field for the order as this has to remain intact
I am using an ontology-like structure to store areas of interest so to get matches and to be able to calculate the relevance the order is important
how about this - would it be better if i just used three columns - root, branches and leaves.
from the array pop the first and last element (the root and the leaf) as they are the most important, and store them in the database. then serialize the rest of the array(the branches)
now when searching just search for the root and the leaf - if they match retrieve all three columns.
then for the branches (of which there are an unknown number) use serialize and then evaluate how much of a match there is because as long as the root and leaf match the rest of the array does not matter so much because the row will still be relevant - how much will depend on the branches
does that make sense??
or is that over-complicating things??
any suggestions are greatly appreciated
thanks
rj
I am using an ontology-like structure to store areas of interest so to get matches and to be able to calculate the relevance the order is important
how about this - would it be better if i just used three columns - root, branches and leaves.
from the array pop the first and last element (the root and the leaf) as they are the most important, and store them in the database. then serialize the rest of the array(the branches)
now when searching just search for the root and the leaf - if they match retrieve all three columns.
then for the branches (of which there are an unknown number) use serialize and then evaluate how much of a match there is because as long as the root and leaf match the rest of the array does not matter so much because the row will still be relevant - how much will depend on the branches
does that make sense??
or is that over-complicating things??
any suggestions are greatly appreciated
thanks
rj
-
rubberjohn
- Forum Contributor
- Posts: 193
- Joined: Fri Feb 25, 2005 4:03 am
ok ive decided not to use serialize because i wont be able to perform partial matches so ill use the method suggested by timvw and d11wtq...
but as well as this information being displayed for the user (ie display their own profile) it also has to be searchable and therefore i need to maintain the order of the array
also there can be multiple entries for each user. So...
I have a user table tblUSER(user_id, username)
and a profile table(user_id, element_id, order, value)
using the example - Computing/IT (1)>>databases (7)>>MySql (35) which is from the select list (numbers represent their value)
would result in the following entries in the profile table:
computing/it - (user_id, 1, 1, 1)
databases - (user_id, 1, 2, 7)
MySql - (user_id, 1, 3, 35)
is there a cleaner way of doing this because this would need a complicated SELECT to match it with another user's search criteria
thanks in advance for you help
rj
but as well as this information being displayed for the user (ie display their own profile) it also has to be searchable and therefore i need to maintain the order of the array
also there can be multiple entries for each user. So...
I have a user table tblUSER(user_id, username)
and a profile table(user_id, element_id, order, value)
using the example - Computing/IT (1)>>databases (7)>>MySql (35) which is from the select list (numbers represent their value)
would result in the following entries in the profile table:
computing/it - (user_id, 1, 1, 1)
databases - (user_id, 1, 2, 7)
MySql - (user_id, 1, 3, 35)
is there a cleaner way of doing this because this would need a complicated SELECT to match it with another user's search criteria
thanks in advance for you help
rj
-
rubberjohn
- Forum Contributor
- Posts: 193
- Joined: Fri Feb 25, 2005 4:03 am
sorry for treble posting this but i have just been looking at regex - would this work - store the root, store the rest of the array serialized - then serialize a user's search criteria and use regex to find a match?
so i store the root of the tree along with the serialized branches - return all rows that have the same root and then use a regex to determine the extent of the match
can i use regex to find partial matches - ie so i can order results "found user with 1/2/3/all matching entries"?
this looks like it could work better than the method posted above, what do you think?
thanks again
rj
so i store the root of the tree along with the serialized branches - return all rows that have the same root and then use a regex to determine the extent of the match
can i use regex to find partial matches - ie so i can order results "found user with 1/2/3/all matching entries"?
this looks like it could work better than the method posted above, what do you think?
thanks again
rj
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
-
rubberjohn
- Forum Contributor
- Posts: 193
- Joined: Fri Feb 25, 2005 4:03 am
i dont think i have explained myself very well here...
i have the data structure, that all works - as the user selects a value from a select list the next one is populated with the relevant values and so on until the user reaches the the leaf - thats fine the tree, parent, child, leaf all works
The problem i am having is when a leaf i selected i want to store the path that the user has just made to reach the leaf and not only do i want to store it to display to the user, i also want other users to be able to search for it.
the whole path and not just the resulting leaf must be stored to accomodate multiple inheritance of a value for eg
computing/IT >> programming languages >> php
computing / IT >> web dev >> programming languages >> php
and also to accomodate partial matches partial matches so when another user performs a search and finds no results for php (using the example above) i can step up a level and suggest other users with other programming languages - probably not the best example but it illustrates what i need
so, i am working on using serialize as I said in my previous post as i think the 'shopping cart' structure mentioned above would be too complicated. unless any one has any other suggestions??
sorry for the mixup
rj
i have the data structure, that all works - as the user selects a value from a select list the next one is populated with the relevant values and so on until the user reaches the the leaf - thats fine the tree, parent, child, leaf all works
The problem i am having is when a leaf i selected i want to store the path that the user has just made to reach the leaf and not only do i want to store it to display to the user, i also want other users to be able to search for it.
the whole path and not just the resulting leaf must be stored to accomodate multiple inheritance of a value for eg
computing/IT >> programming languages >> php
computing / IT >> web dev >> programming languages >> php
and also to accomodate partial matches partial matches so when another user performs a search and finds no results for php (using the example above) i can step up a level and suggest other users with other programming languages - probably not the best example but it illustrates what i need
so, i am working on using serialize as I said in my previous post as i think the 'shopping cart' structure mentioned above would be too complicated. unless any one has any other suggestions??
sorry for the mixup
rj