SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,316
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Moving row in table to another place?

    My boss wants me to move a single photo at the bottom of the page to near the top. However, the photos display in the order they appear in the table. What PHP script do I give MySQL to move a row to another place in the table?

    I don't remember having to do this before and I can't find such a command in the Alter Table section.

    Thanks!

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    The quick and dirty way would be to alter the table with a weight(tinyint) column. Change the associated queries to order by that column. Then make sure the photo has the highest/lowest weight based one on the sort order.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StevenHu View Post
    However, the photos display in the order they appear in the table.
    actually, that's just a coincidence, and you cannot rely on it

    better would be to use an ORDER BY column, as that way you can control the sequence, not rely on luck

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,316
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    The quick and dirty way would be to alter the table with a weight(tinyint) column. Change the associated queries to order by that column. Then make sure the photo has the highest/lowest weight based one on the sort order.
    Create a customized sort order column - very clever!

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,316
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, that's just a coincidence, and you cannot rely on it

    better would be to use an ORDER BY column, as that way you can control the sequence, not rely on luck

    So then in ORDER BY I would list all the row IDs in the order I want them listed? Never tried that before.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StevenHu View Post
    So then in ORDER BY I would list all the row IDs in the order I want them listed? Never tried that before.
    no, don't do that

    instead, the table should have a column which you can specify in the ORDER BY, such that the values in that column determine the quence of rows in the output

    that's typically ~not~ the id column

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,316
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937,

    OK, you are adding a little more detail to oddz suggestion. Thanks!

    BTW, what does r937 mean?

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,316
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm doing an inner join of three tables, so I'll have to figure out which one of two tables to put the extra column.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    ALTER {table table_name} ADD weight tinyint unsigned NOT NULL DEFAULT '0' after {some_column_that_already_exists}

    UPDATE {table_name} SET weight=1 WHERE id = {primary_key}

    SELECT x,y,z FROM table_name ORDER BY weight DESC

    Obviously that will only affect the single image, but now you have the option of controlling the order of all other images also by updating their weight.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StevenHu View Post
    BTW, what does r937 mean?
    http://www.sitepoint.com/forums/show...hreadid=111656
    r937.com | rudy.ca | 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
  •