storing arrays

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

storing arrays

Post by rubberjohn »

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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Why not just have the correct structure in your database to hold this info? A profiles table should be a good idea ;)
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

do you mean store each element of the array in its own column in the profile database?

I thought of that but there are not a set number of array elements - could be two, could be 10
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Assuming you have profile (profile_id, ... ) table.
Simply add profile_elements (profile_id, element_id) table.

Now you can have as many profile_elements linked to a profile.
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

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
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

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
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

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
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

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
Post Reply