SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A tricky SELECT related question

    Well, I am having a field in my musql table, say my_type, where I can have a maximum of three different type of entries, say they are 0, 1 and 2.

    I want to list them in a webpage, with fields belong to the same row as 0 comes first, followed by that of 1 and then 2. I also need need to now the number of rows returned for each of these types (there may be all 3 type present, or less, depending on what user is entering). And I also need to title the types, like, "here goes the row of type 0" before each type.

    I was wondering if there's a SELECT command that can handle this tricky case, or I will have to have three sql queries one for each of 0, 1 and 2.

    Thanks for any tips / hints.
    Best.

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the first part - I wouldn't select what the actual 3 options are unless they are going to be changing a lot (which shouldn't be the case for your DB structure). Just hard code what those 3 options are. This will save you running an unnecessary query every time this feature is used. If you really need it to be dynamic you could probably use a DESCRIBE `table` query.
    Btw this field should be the ENUM type (short for enumeration)

    For the second part, I'm confused by what you want to do.
    Do you need to count the number of rows in the table belonging to each possibly value of my_type ?
    If so something like this should work:

    Code:
    SELECT count(*), my_type FROM `table` GROUP BY my_type
    That will only return the number of records associated with each possible my_type where one exists.
    You could use your programming language to assign 0 values to those types not returned by the query.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  3. #3
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    btw your problem has nothing to do with PHP. Try posting in the MySQL forum, you'll probably get a better response there!
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  4. #4
    SitePoint Addict smittenbite's Avatar
    Join Date
    Jul 2005
    Location
    Friendswood
    Posts
    397
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also if you have a table with the other information for example if you hav a table with name, email, user_id, and my_type, well if many rows can have the same type (which with only 3 possible set ones i wouuld think this is the case) you need to put my_type into a different table to normalize. this will make your more queries easier to figure out later.
    nothing.

  5. #5
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your help guys ...


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
  •