MySQL data type

[TheOriginalH]

I have to add an index field to a database. The indexing system uses numbers categorised by points and slashed (eg for this thread, Build your site could have a top level number of 7, ss internet dev could have a second level value of 3 and php&mySQL could have a third level value of 42, resulting in the index id "7.3/42"

Any idea what the best datatype to use would be (perhaps just varchar?)

hmmm - could be being stupid here, perhaps I should break it into three fields, as I want to be able to search by all levels. If I maintained the format and included it in just one field, is there an easy way of searching for anything in just buildyoursite > SS internet dev?

Does any of this make sense ?

Where's my avatar ?

H

[TheOriginalH]

[TheOriginalH]

Pamalea Anderson/Drew Barrymore/Cameron Diaz/Brad Pitt/Johnny Depp/Tom Cruise please?





(incedentally, it's nearly my birthday...post 199!)

[aspen]

in one field you would search like this.

where fieldname like '%3.2%'

or

where fieldname like '%/4'

% is a wildcard figure in SQL so you can put it in the appropriate spot when using a where clause with the like operator to search based on parts of a field rather than the whole field.

of course purely from organizational purposes there is alot more you can do with separate fields rather than 1 field with many components so going that route would probably be better.

[TheOriginalH]

Thankyou

With single field option - would varchar be best data type or is there something better suited?

[freakysid]

What aspen says You can get by with using one VARCHAR field but this is not the best solution.

A principle of database design is to split things up into their most atomic level. This will give you the most powerful data model in terms of being able to query and manipulate your data. So if your site is organised by top_level, second_level, third_level so ideally these should be stored as atomic elements (seperate fields) in your database. Probably in some tree structure.

However, you seem to be wanting to modify an existing schema - so the VARCHAR field might be the quick and dirty solution for you.

[TheOriginalH]

Your absolutely right Sid. I have to port an SQL database. The only problem is that the field I refer to is currently pretty much a "dead" field. Although it has no functionality per se (in the exisiting database/use), it's usefulness in a web style directory is huuuuge. I want to maintain parity between the dabases (datatype notwithstanding, updates will be conducted via csv), and am a little concerned about unforeseen problems with varchar. Like, for example,

"pants" have a category of clothes/underwear/pants.
These values are represented by 14 (clothes), 2 (undewear) and 3 (pants), giving us a code of 14.2/3 .

wheeltrims have a category of auto/cars/wheeltrims
Values represented by 4 (auto) 2 (car) and 3 (wheeltrims).

motorbike helmets have 4 (auto) 21(motorbikes) and 3(helmets)


A search for any thing "car" related (%4.2%) would surely also bring up bikes?

More worringly, a search for autos (%4%) would also bring up clothes...and ultimately pants?

I could be all wrong here, I am a relative virgin to MySQL....

I thought of somesort of routine to concatenate results of three fields for a record, but this doesn't work. I need to update the MySQL database from the SQL one, meaning that the values would need to be split from SQL rather than concatenated to SQL.

Any other advice greatly appreciated

H

[freakysid]

csv ?!? oh, duh! comma seperated values.

Well, the original designer of that database should be executed on public television.

OK well if you import the csv using a script you can just use regular expressions it split up the string into its three components and for each record insert the data into seperate category, sub_category, sub_sub_category fields. That's assuming that the three-level classification applies to each and every product. Personally, I would do this because it makes searching, filtering and ordering the data much easier. In fact, because I'm a dumb **** when it comes to regular expressions I would just use PHPs string manipulation functions - but I'd have to go read the manual.

On the other hand, again assuming that each product adheres to the three level hierarchy of categorisation, and that you keep the field in its current format - you can construct sql expressions that will discriminate between a category, sub_cateogry and sub_sub_category.

for example, while '%4.2%' will bring up cars and bikes, '4.2/%' will bring up only cars.

Search specific category:
LIKE '$c.%'

Search specific sub_cateogy
LIKE '$c.$sc/%'

Search specific sub_sub_category
LIKE '$c.$sc/$ssc'

[TheOriginalH]

woohoo, you're a star!

I'll probably have a stab at splitting the value, but it's useful that I can use varchar and the search criteria you described if I fail miserably (quite likely......)

Thanks Again,

H