SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: MySQL data type

  1. #1
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  2. #2
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    pretty please?

    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  3. #3
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Pamalea Anderson/Drew Barrymore/Cameron Diaz/Brad Pitt/Johnny Depp/Tom Cruise please?





    (incedentally, it's nearly my birthday...post 199!)
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  4. #4
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  5. #5
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thankyou

    With single field option - would varchar be best data type or is there something better suited?
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'
    Last edited by freakysid; Apr 20, 2001 at 07:25.

  9. #9
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •