SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting details by user choice

    I have stored in a database a few differerent details about a user. This user can add or remove details whenever they like. My question is, how can I make it so that the user can choose the order in which these details are displayed? Maybe this is a bit more of a database question, but this does relate to PHP as I believe PHP would have to do most of the order processing.

    Thanks,
    Brent.
    PHP | MySQL | (X)HTML | CSS

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have a column (i.e. "index_number") -- not the primary key -- that users can modify, then use ORDER BY in your statements (I'm assuming you're using MySQL).

  3. #3
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, but how can I go about ordering it, and making it easy for the user to order it? (So the user doesnt have to manually input order numbers)
    PHP | MySQL | (X)HTML | CSS

  4. #4
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unless you're using EAV, the ORDER BY approach won't work, as you want to sort columns, not rows.

    Properly implementing this isn't simple, it involves two extra tables: fields and fields_users. The latter will contain user_id, field_id and order_id, for example:

    users:
    Code:
    id    username    age
    1     user1       15
    2     user2       16
    fields:
    Code:
    id    name
    1     id
    2     username
    3     age
    fields_users:
    Code:
    user_id    field_id    order_id
    1          1           1
    1          2           2
    1          3           3
    2          1           1
    2          2           3
    2          3           2
    That will mean that for the first user, order is id, username, age and for the second user order is id, age, username.

  5. #5
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I may not have stated what I am trying to achieve correctly.

    Heres my table

    user_interests
    Code:
    user              stores the users id
    interest_id       a unique identifier for that users interest
    name              name of interest
    value             details about that interest
    order             hopefully to get a order happening
    My aim to create a simple web interface so that the user (of non code background) can change the order of which each detail appears on the page (not just sorted by name, etc)
    PHP | MySQL | (X)HTML | CSS

  6. #6
    SitePoint Guru Ruben K.'s Avatar
    Join Date
    Jun 2005
    Location
    Alkmaar, The Netherlands
    Posts
    693
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The theory behind this is that you do a different ORDER bit in a query depending on what the user has in his cookie.

    Say, we have something like

    Code:
    $order_by = ( isset( $_COOKIE['user_orderby'] ) ) ? $_COOKIE['user_orderby'] : 'id';
    $order = ( isset( $_COOKIE['user_order'] ) ) ? $_COOKIE['user_order'] : 'ASC';
    Use a ternary operator to determine whether a cookie has been set on the client side with the order data, and use 'id' and 'ASC' if it hasn't been set

    You got to have a small form that lets you set the order by and order data and put it in a cookie, allowing individual visitors to have their own sort settings

    Then use this data in your MySQL query

    Code:
    $query = "SELECT * FROM myTable ORDER BY $order_by $order";

  7. #7
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't want to order by a column. I want it so that if there are 4 rows for example, the user can choose the exact order of those rows. "1,4,2,3" or maybe "2,1,3,4". I know this can be done easily by just chaging the value of the order column and then just changing each rows order number to it works out to be "1,2,3,4", but I want a way where a user can change the order by clicking an up or down button on a friendly UI.

    Designing the UI is not a problem, its just the code on how I can get the order to change without causing massive problems, and large amounts of queries. Also, I want to try and make it so that the user can add a new row anywhere in the order without it causing major problems and multiple queries. I want to optimise this as best as possible.

    I think I may have came up with something actually... But i might think it through first and make sure everything works...
    PHP | MySQL | (X)HTML | CSS

  8. #8
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, heres what im thinking, im still stuck on one thing... but hopefully someone can help me with that. First off a new table...

    user_interests_order
    Code:
    user_id       store the id of the user whos interests we are dealing with 
    order         the order for interests to be displayed, separated by commas. The numbers being the interest_id's
    So if I do it this way, I can easily add numbers to the order (i think), I can easily update the order (i think), but displaying it using this order... this is where im stuck. How can I use the order code to order and display the rows on the page. This has really stumpted me...
    PHP | MySQL | (X)HTML | CSS

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    order the order for interests to be displayed, separated by commas. The numbers being the interest_id's
    You will end up getting something out of Mysql and asking PHP to do the work.

    Get mysql to do as much as you can. Using an order by column is going to be miles easier.

    My advice is don't do it as csv you then have to explode ...

    I made a solution where the user can type a number in a box to renumber items, I am pretty relaxed about it, if there end up 2 choices with 3 in them, then nothing fails, new rows automatically go to the bottom unless the user stipulates a particular row number.

    If I wanted to, next time the user amends the list its dead easy to throw up a screen warning saying "do you want to renumber this one?"

    The number doesn't appear on screen unless the user wants to amend the order. It works fine for my users with up to about 20 items ... maybe its sloppy but its pragmatic and it works.

  10. #10
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to offer something much more user friendly than having to enter the order numbers if possible.
    PHP | MySQL | (X)HTML | CSS

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    two suggestions:

    don't use ORDER as the name of a column, because it's a reserved word (use something like SEQ instead)

    don't store comma-delimited strings in a single column, ever (use a one-to-many table instead)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    A thought - display the info in the page, then use DHTML to allow the user to grab the displayed rows (DIVs?) into the display order (sequence) they subsequently want to see. Then when complete update the Sequence field in the database table accordingly. (I agree, DO NOT call a database field order).
    Ian Anderson
    www.siteguru.co.uk

  13. #13
    SitePoint Guru brent5392's Avatar
    Join Date
    Dec 2005
    Location
    Australia
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My aim is not to allow the order to be different for each visitor, but so that the user can set the order of their interests for their profile.

    Im getting desperate thats all, I can't think of anything else. Any suggestions on how I can do this without making too many queries. I want to make this as efficient as possible.
    PHP | MySQL | (X)HTML | CSS

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    create table user_interests_seq
    ( user_id integer not null -- id of the user whose interests we are dealing with
    , interest_id integer not null -- id of the interest this user is interested in
    , primary key (user_id,interest_id) -- each user's interests must be unique
    , seq integer not null -- the sequence value for this interest for this user
    )
    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
  •