SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Select Order By

  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Order By

    How can 6 different SELECT statements be made to call certain values from an ORDER, ex: value for row 2? There are 6 row values allotted per user. This occurs on an Update form.

    Table: ContCat (many to many MyISAM table)

    W_ID
    (int4) not null // relates to a user entity
    Cat_ID (int4) not null // relates to a category entity

    Statement:
    SELECT ContCat.W_ID, Contact.Cat_ID ORDER BY 'ContCat.W_ID' DESC (how to pull a row among the order, for example the 2nd of the 6 rows here?)

    Which can be echoed onto six drop menus, in consecutive order 1 thru 6 for each drop menu appearing. (note: some Cat_ID values will have '0' as default so users who initially opted to only Insert 3 total values/rows could add more, say 3 more in this case, if they ever choose to. So all users have 6 values max. Also, there are 250+ possible values for each Cat_ID)

    One of the Update drop menus:
    PHP Code:
     <select name="Cat_ID">
    <option value="1" <?php if (!(strcmp(htmlentities($row_Recordseta['Cat_ID']), "1"))) {echo "SELECTED";} ?>>1st choice</option>
    <option value="2" <?php if (!(strcmp(htmlentities($row_Recordseta['Cat_ID']), "2"))) {echo "SELECTED";} ?>>2nd choice</option> 
    </select>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    unless you mean LIMIT 1,1 (which will give you the 2nd row), i'm totally lost

    perhaps you could give an example of several rows of data, then show what you want the query to return
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I' after is each of the (6) rows per user, in the order that they occur on ContCat with DESC.

    I need t0 apply these to each of the 6 drop menus to Update the values:
    (from many to many table - no primary ID)

    1 st Drop menu : 1st value Insert for that user ( first Cat_ID / W_ID )

    2nd Drop menu : 2nd value Insert for that user ( second Cat_ID / W_ID )

    and so on, for all six.

    I am asking how this should be written for each menu to acheive this, so all six may be Updated from a single form:

    SELECT ContCat.W_ID, Contact.Cat_ID ORDER BY 'ContCat.W_ID' DESC

    If you want a sample row values:
    Cat_ID 9 W_ID 247
    Cat_ID 10 W_ID 196
    Cat_ID 11 W_ID 3

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    totally, totally lost

    i don't see "each of the 6 rows" in your three sample values, and i don't see the user

    maybe you have a web page where this form is mocked up?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ContCat.W_ID would be linked to the user from a JOIN

    This would do for six row values (had reversed):

    W_ID 9, Cat_ID 119
    W_ID 10, Cat_ID 234
    W_ID 11, Cat_ID 57
    W_ID 12, Cat_ID 0
    W_ID 13, Cat_ID 0
    W_ID 14, Cat_ID 0

    -Trying to stay away from LIMIT (don't know if will recognize a '0' given that there may be a problem when there is only a single row result, which would include five 0's, see http://archives.postgresql.org/pgsql...1/msg00208.php)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why would you even insert those last 3 rows into the table in the first place? that design is sure to get you in trouble

    also, do you really have a myISAM table in postgresql?

    okay, so suppose you did select 6 non-zero rows for some user, could you explaiun again what you want to do with them?

    you want to create 6 dropdowns with the same 6 entries? just script the same result set 6 times

    getting more confused by the minute...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, thanks for sticking in there.

    why would you even insert those last 3 rows into the table in the first place? that design is sure to get you in trouble
    Those zeros will be inserted whenever the user chooses less than three values to Insert. The Inserted zeros (by default) are a way to reserve values to a maximum of 6 per each user. The zeros are intended to be read as "values" on the Update form since users cannot add addtional rows while within an Update form -- which only updates existing values. If the zeros (to make up for values not selected) were not present, the values unchosen (the difference between those chosen and the "6 alotted values") could not be made (in this case "Updated" really means "chosen for the first time", it just doesn't appear so)

    This limits the number of listings any given "registrant" can have in this directory type structure to six. This is for a direcoty type structure and the number of values must be limited since each directory appearance is based on this.

    I'm more than curious how this might "be sure to get you in trouble"?
    How would you do it?

    also, do you really have a myISAM table in postgresql?
    myISAM table, yes. whether it is postgresgl I cannot say.

    okay, so suppose you did select 6 non-zero rows for some user, could you explaiun again what you want to do with them?you want to create 6 dropdowns with the same 6 entries? just script the same result set 6 times
    getting more confused by the minute...
    Now I'm getting confused. What I have wanted to do is always have six values updateable (for the reasons above) from this many to many table. I have chosen drop menus since it is limited to six values chosen (over the other options -- scrollbox, etc). Each of the drop menus should update each of the row values, 1 thru 6, for each user. For each of these Cat_ID values there are 250+ possible values. Each of the W_ID 's are limited to six.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how would i do it? without zeros

    so if a user has only 3 options, there would be only 3 rows

    how would i limit the number selected to 6?

    by doing select count(*) as options from ... and checking the value of options before allowing an INSERT

    i would also have only one dropdown list (250 values), with the MULTIPLE attribute

    i would let the user choose whichever options desired, then do a complete DELETE followed by an INSERT

    i.e. no UPDATE, ever

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    I'm sure its there, though I don't fully understand it. You are more the programmer.

    I've been in a drawn-out self debate whether to just do 6 table columns (indexed) which are much easier to update, or, to do this many-to-many table.

    If I can get the index right (no scan) say, if I can process a zip code column before six OR's in a SELECT statment (since these are local listings/zip code joined) -- maybe I should just keep to six table columns and be prepared to keep it at that (not change to 7 columns)

    I've consulted others about an Update on a many to many table & I'm sensing there is risk involved -- more than a one-to-many table.
    --------------------------------------------------
    As for another application, on a many to many table with practically unlimited values -- would you not make that Updateable either (I have started the update using the more normal checkboxes)

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not a programmer, i'm an sql consultant, data modeller, database designer

    however, i have done programming, but in coldfusion, so i can't really share my code with you, because you're a real programmer, because you use php



    whoever suggested there was a risk in an update on a many-to-many table is perhaps pulling your leg

    in my opinion -- which you are free to disregard -- having 6 fields would be very, very wrong, because of the ugly and complex sql you will need to support it, and because, as you already anticipated, you can't easily go to 7

    for example, assume you have a table with 6 columns, what's the sql to find all users who have a value of X in any one of those columns? that's right, a table scan

    and the point about the update -- if the many-to-many table has only two colulmns, the Xid and the Yid, then trying to do an update on just one of 6 values involves a lot of code to (a) find which one, and (b) match the correct form field

    whereas delete/insert is a lot simpler

    it all comes down to balancing two objectives -- efficient sql, and as little programming code as possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •