Professional Web Applications Themes

UPDATE and SELECT MAX - MySQL

Hi there, I'm trying to update a table to set the sorting order of items and get my head around some fancier SQL ( I'ld love to get the hang of it). But it's to no avail so far. The CMS admin of the site I'm working on should be able to move items displayed on a webpage up or down for visitors to see on the live page. Consider the following if you will: The table I'm working with is the following: CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL auto_increment, `pageId` int(10) unsigned default NULL, `sortId` int(10) ...

  1. #1

    Default UPDATE and SELECT MAX

    Hi there,

    I'm trying to update a table to set the sorting order of items and get my
    head around some fancier SQL ( I'ld love to get the hang of it). But it's to
    no avail so far.

    The CMS admin of the site I'm working on should be able to move items
    displayed on a webpage up or down for visitors to see on the live page.
    Consider the following if you will:

    The table I'm working with is the following:

    CREATE TABLE `item` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `pageId` int(10) unsigned default NULL,
    `sortId` int(10) unsigned default NULL,
    `type` enum('paragraph','staffmember','article') default 'paragraph',
    `title` varchar(255) NOT NULL default '',
    `text` text,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    The admin decides to move an item ( with sortId 7 and pageId 1 ) up one
    position (it will never be more than one position at a time)

    So, in pseudo code it would be something like:
    1. Select (if any) the item that has the biggest sortId smaller than the
    current item's sortId and has the same pageId
    That would be something like:
    SELECT MAX( sortId ) FROM item WHERE sortId < 7 AND pageId = 1

    2. Update that item's sortId and set it to the sortId of the item we're
    working with.
    3. Update the item we're working with to the sortId of previous item. (or
    perhaps 2. and 3. vice versa )

    But this is where SQL becomes total abacadabra for me. I'm trying things
    like the following:

    UPDATE item SET sortId = 7 WHERE sortId = ( SELECT MAX( sortId ) WHERE
    pageId = 1 AND sortId < 7 );

    Just for starters ( that is not even trying to cover lid. 3 yet as you can
    see ). If I could do all 3 things in one go that would be splendid.

    So, I'm hoping some of you could give me some pointers, cause the supposed
    logics of the more advanced SQL isn't getting through to me. Thank you for
    your time.


    amygdala Guest

  2. #2

    Default Re: UPDATE and SELECT MAX


    "amygdala" <com> schreef in bericht
    news:46342cdb$0$16949$kpnplanet.nl... 

    So now I'm trying:

    UPDATE item SET sortId = 7 WHERE sortId IN ( SELECT MAX( sortId ) as sortId
    FROM item WHERE sortId < 7 AND pageId = 1 );

    And it yields the error:
    You can't specify target table 'item' for update in FROM clause

    Why is this? Am I not allowed to update a table and select from the same
    table simultaneously?

    Any pointers to some good tutorials would also be greatly appreciated.

    Thanks in advance people.


    amygdala Guest

  3. #3

    Default Re: UPDATE and SELECT MAX

    amygdala wrote: 
    >
    > So now I'm trying:
    >
    > UPDATE item SET sortId = 7 WHERE sortId IN ( SELECT MAX( sortId ) as sortId
    > FROM item WHERE sortId < 7 AND pageId = 1 );
    >
    > And it yields the error:
    > You can't specify target table 'item' for update in FROM clause
    >
    > Why is this? Am I not allowed to update a table and select from the same
    > table simultaneously?
    >
    > Any pointers to some good tutorials would also be greatly appreciated.
    >
    > Thanks in advance people.
    >
    >[/ref]
    if you're on version 5.0 or higher, this type of processing is the thing
    that should be done with a stored procedure. that is exactly what i'd do
    if i was you. i'd write a sp and pass to it the pageid, the current
    sortid and any other value that may be helpful in processing. within the
    procedure, you'd run your logic more effectively and cleanly and can
    update the table. in an sp you'd be able to setup local variable and use
    them in the body of the sp to figure our the right sortid.
    lark Guest

  4. #4

    Default Re: UPDATE and SELECT MAX

    On Apr 29, 3:41 pm, "amygdala" <com> wrote: 











    >
    > So now I'm trying:
    >
    > UPDATE item SET sortId = 7 WHERE sortId IN ( SELECT MAX( sortId ) as sortId
    > FROM item WHERE sortId < 7 AND pageId = 1 );
    >
    > And it yields the error:
    > You can't specify target table 'item' for update in FROM clause
    >
    > Why is this? Am I not allowed to update a table and select from the same
    > table simultaneously?
    >
    > Any pointers to some good tutorials would also be greatly appreciated.
    >
    > Thanks in advance people.[/ref]


    FWIW I do this using the example provided by Christain Darie in his
    book AJAX and PHP: Building Responsive Web Applications. A chapter
    within the book describes a drag-and-drop interface specifically tied
    back to a mysql database, but there are obviously plenty of other
    examples of drag-and-drop implementations on the web.

    strawberry Guest

  5. #5

    Default Re: UPDATE and SELECT MAX

    > if you're on version 5.0 or higher, this type of processing is the thing 

    Hi Lark,

    Thank you for the response. Actually I am working with version 5. I
    definately want to learn how to use stored procedures at one point in time.
    And although I have limited experience, I can see that this is probably a
    good tool to use for these types of situations. But I have a few reasons I
    don't want to use them at this point.

    First off, I don't feel comfortable using them when I don't even know the
    basics of more complex queries using subqueries and the likes. To me it
    feels like diving in a deep pond when I don't even know how to swim yet.
    Second, I'm only using regular queries for this project so far, and I don't
    like the idea of using a sp just for this routine. I'ld like to keep all
    queries in the PHP code for easy reference. (But perhaps one is able to
    define sp's on the fly in PHP, I don't know) But still, I'ld like to keep my
    queries consistent. (A bit neurotic perhaps ;-)

    I have the feeling that the routine I'm trying to hack shouldn't even be
    that complex of a thing to do with regular queries. I just don't have a good
    grasp of the grammar and logics of subqueries etc.

    So, with this in mind, do you perhaps have some other pointers for me?
    Thanks again.


    amygdala Guest

  6. #6

    Default Re: UPDATE and SELECT MAX

    > FWIW I do this using the example provided by Christain Darie in his 

    Hi Strawberry,

    Thank you for the response. This does sound like it's worth something ;)
    Although I'm not using AJAX and a drag and drop implementation here (I have
    and am still considering it BTW) that indeed sounds like the sort of thing
    I'm after. First off I would just like to have a working implementation
    without the use of AJAX etc. though. I'll see what I can find on the web
    about drag and drop implementations and report back.
    In the mean time, if it's not to much trouble, would you mind posting just a
    brief example SQL query (if you have any) so I can get a feel of what path I
    should take. Just so I can get a grasp of the logics of these types of
    queries?

    Cheers


    amygdala Guest

  7. #7

    Default Re: UPDATE and SELECT MAX

    amygdala wrote: 
    >
    > Hi Lark,
    >
    > Thank you for the response. Actually I am working with version 5. I
    > definately want to learn how to use stored procedures at one point in time.
    > And although I have limited experience, I can see that this is probably a
    > good tool to use for these types of situations. But I have a few reasons I
    > don't want to use them at this point.
    >
    > First off, I don't feel comfortable using them when I don't even know the
    > basics of more complex queries using subqueries and the likes. To me it
    > feels like diving in a deep pond when I don't even know how to swim yet.
    > Second, I'm only using regular queries for this project so far, and I don't
    > like the idea of using a sp just for this routine. I'ld like to keep all
    > queries in the PHP code for easy reference. (But perhaps one is able to
    > define sp's on the fly in PHP, I don't know) But still, I'ld like to keep my
    > queries consistent. (A bit neurotic perhaps ;-)
    >
    > I have the feeling that the routine I'm trying to hack shouldn't even be
    > that complex of a thing to do with regular queries. I just don't have a good
    > grasp of the grammar and logics of subqueries etc.
    >
    > So, with this in mind, do you perhaps have some other pointers for me?
    > Thanks again.
    >
    >[/ref]
    ok!

    let's see what we can do here. trying to upate a sortid on an item
    within a page. i'd think that there are several items within a page all
    with different ids and sortids, right?

    if every item has an id (which I think they do)

    CREATE TABLE `item` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `pageId` int(10) unsigned default NULL,
    `sortId` int(10) unsigned default NULL,
    `type` enum('paragraph','staffmember','article') default 'paragraph',
    `title` varchar(255) NOT NULL default '',
    `text` text,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;


    why wouldn't you, just update the item like this:

    update item set sortID= sortID-1 where id = xx;
    or if they're increasing it, use this:
    update item set sortID= sortID+1 where id = xx;

    that is of course if you have access to the id which i don't know if you
    do or not.
    lark Guest

  8. #8

    Default Re: UPDATE and SELECT MAX

    On Apr 30, 4:20 pm, "amygdala" <com> wrote: 
    >
    > Hi Strawberry,
    >
    > Thank you for the response. This does sound like it's worth something ;)
    > Although I'm not using AJAX and a drag and drop implementation here (I have
    > and am still considering it BTW) that indeed sounds like the sort of thing
    > I'm after. First off I would just like to have a working implementation
    > without the use of AJAX etc. though. I'll see what I can find on the web
    > about drag and drop implementations and report back.
    > In the mean time, if it's not to much trouble, would you mind posting just a
    > brief example SQL query (if you have any) so I can get a feel of what path I
    > should take. Just so I can get a grasp of the logics of these types of
    > queries?
    >
    > Cheers[/ref]

    I don't fully get the darn thing myself but, as I understand it,
    certain actions trigger the following script (snipped), with $content
    being a list of items and $action being one of a set of possible
    commands (in this case 'updateList'). The BuildItemsList() object at
    the end is the SELECT statement for generating the new view from the
    database (and, in ajax, seamlessly swapping it with the current view):

    public function Process($content, $action)
    {
    // perform action requested by client
    switch($action)
    {
    // Reorder item list
    case 'updateList':
    // retrieve update details
    $new_order = explode('_', $content);
    // update list

    for ($i=0; $i < count($new_order); $i++)
    {
    // escape data received from client
    $new_order[$i] =
    $this->mMysqli- 
    // update item
    $query = "UPDATE items SET ranking = $i WHERE item_id=
    $new_order[$i];";

    $result = $this->mMysqli->query($query);
    }
    $updatedList = $this->BuildItemsList();
    return $updatedList;
    break;

    strawberry Guest

  9. #9

    Default Re: UPDATE and SELECT MAX

    amygdala wrote: 
    >
    > Hi Strawberry,
    >
    > Thank you for the response. This does sound like it's worth something
    > ;) Although I'm not using AJAX and a drag and drop implementation
    > here (I have and am still considering it BTW) that indeed sounds like
    > the sort of thing I'm after. First off I would just like to have a
    > working implementation without the use of AJAX etc. though. I'll see
    > what I can find on the web about drag and drop implementations and
    > report back. In the mean time, if it's not to much trouble, would you mind
    > posting
    > just a brief example SQL query (if you have any) so I can get a feel
    > of what path I should take. Just so I can get a grasp of the logics
    > of these types of queries?
    >
    > Cheers[/ref]

    Take a look at
    http://www.phpriot.com/d/articles/client-side/sortable-lists-with-php-and-ajax/page5.html
    This method'll work with or without AJAX


    Paul Guest

  10. #10

    Default Re: UPDATE and SELECT MAX


    "lark" <net> schreef in bericht
    news:WqoZh.20265$news.prodigy.net... 

    Both assumptions are correct, yes.
     

    Well, if it were only that simple ;-) The thing is, like I tried to point
    out in pseudo code in my initial message, that I also need to update the
    sortId (if any) of the item which this item is now replacing. I'm basically
    swapping items.

    So consider this, if you will (sortId will always be the lowest sortId):

    | pageId | sortId | title |
    | 1 | 0 | item 1 |
    | 1 | 1 | item 2 |
    | 1 | 2 | item 3 |
    | 1 | 3 | item 4 |

    Let's say 'item 2' moves up one spot. That would mean 'item 2' gets sortId 0
    and 'item 1' gets sortId 1.
    Or let's say (with the initial values) 'item 1' moves up. That would mean it
    can't, because it is already the lowest value (0) in the sortId order (and
    it actually shows up highest on the actual page).

    The way I'm building the application now, if all goes well, there should
    never turn up any gaps in sortIds, but to be on the safe side I want to
    check it. So let's say I wanted to move item 3 (sortId 2) up, I would want
    to make sure that sortId 1 is really the next sortId in the 'move up' order.

    Hopefully this clearifies what I have in mind a bit more.

    I could of course try and get all the needed values one at a time with
    different queries. But I thought it would be neat (especially since I want
    to learn some more complex SQL) to do it all in one query.
     

    I do actually. I could of course also write a routine in PHP that handles
    the sorting stuff, but that feels a bit like overkill if I could do it in
    SQL too.

    Thanks again.


    amygdala Guest

  11. #11

    Default Re: UPDATE and SELECT MAX


    "strawberry" <com> schreef in bericht
    news:googlegroups.com...
     
    > // update item
    > $query = "UPDATE items SET ranking = $i WHERE item_id=
    > $new_order[$i];";
    >
    > $result = $this->mMysqli->query($query);
    > }
    > $updatedList = $this->BuildItemsList();
    > return $updatedList;
    > break;[/ref]

    Hi strawberry,

    Well this seems more of a pretty basic update SQL query, where, I presume,
    PHP takes care of all the sorting stuff before it gets put in the database.
    Which off course is still a valid approach. But I was hoping to learn some
    more complex SQL and let the SQL do all the work.

    Then again, the more I think about it, maybe it isn't even such a bad idea
    to let PHP do the work, because it probably simplefies error handling (is
    item x already highest in the sorting order, etc. ). Arghhhh, my changable
    character is playing parts here, lol. Sorry about that.

    I think it would be best just for me to think this over a good time once
    more before posting another question on the matter again. ;-)

    Thanks again for your time and input anyway!

    Cheers


    amygdala Guest

  12. #12

    Default Re: UPDATE and SELECT MAX


    "amygdala" <com> schreef in bericht
    news:46367296$0$16945$kpnplanet.nl... 
    >
    > Both assumptions are correct, yes.

    >
    > Well, if it were only that simple ;-) The thing is, like I tried to point
    > out in pseudo code in my initial message, that I also need to update the
    > sortId (if any) of the item which this item is now replacing. I'm
    > basically swapping items.
    >
    > So consider this, if you will (sortId will always be the lowest sortId):
    >
    > | pageId | sortId | title |
    > | 1 | 0 | item 1 |
    > | 1 | 1 | item 2 |
    > | 1 | 2 | item 3 |
    > | 1 | 3 | item 4 |
    >
    > Let's say 'item 2' moves up one spot. That would mean 'item 2' gets sortId
    > 0 and 'item 1' gets sortId 1.
    > Or let's say (with the initial values) 'item 1' moves up. That would mean
    > it can't, because it is already the lowest value (0) in the sortId order
    > (and it actually shows up highest on the actual page).
    >
    > The way I'm building the application now, if all goes well, there should
    > never turn up any gaps in sortIds, but to be on the safe side I want to
    > check it. So let's say I wanted to move item 3 (sortId 2) up, I would want
    > to make sure that sortId 1 is really the next sortId in the 'move up'
    > order.
    >
    > Hopefully this clearifies what I have in mind a bit more.
    >
    > I could of course try and get all the needed values one at a time with
    > different queries. But I thought it would be neat (especially since I want
    > to learn some more complex SQL) to do it all in one query.

    >
    > I do actually. I could of course also write a routine in PHP that handles
    > the sorting stuff, but that feels a bit like overkill if I could do it in
    > SQL too.
    >
    > Thanks again.[/ref]

    In answering strawberry's last message I said I'm going to re-think my
    strategy here. If your interested, please see my considerations in that
    message.



    amygdala Guest

  13. #13

    Default Re: UPDATE and SELECT MAX


    "Paul Lautman" <com> schreef in bericht
    news:individual.net... 
    >>
    >> Hi Strawberry,
    >>
    >> Thank you for the response. This does sound like it's worth something
    >> ;) Although I'm not using AJAX and a drag and drop implementation
    >> here (I have and am still considering it BTW) that indeed sounds like
    >> the sort of thing I'm after. First off I would just like to have a
    >> working implementation without the use of AJAX etc. though. I'll see
    >> what I can find on the web about drag and drop implementations and
    >> report back. In the mean time, if it's not to much trouble, would you
    >> mind posting
    >> just a brief example SQL query (if you have any) so I can get a feel
    >> of what path I should take. Just so I can get a grasp of the logics
    >> of these types of queries?
    >>
    >> Cheers[/ref]
    >
    > Take a look at
    > http://www.phpriot.com/d/articles/client-side/sortable-lists-with-php-and-ajax/page5.html
    > This method'll work with or without AJAX[/ref]

    Hi Paul,

    Yeah, this also let's PHP take care of the sorting for the most part. Like I
    told strawberry, I'm going to re-think my strategy, because using PHP to do
    the sorting stuff is probably not a bad idea after all. :-S At first I
    wanted to let SQL do all the work, but errorhandling is probably easier if I
    let PHP do it.

    Thanks anyway.


    amygdala Guest

  14. #14

    Default Re: UPDATE and SELECT MAX


    "amygdala" <com> schreef in bericht
    news:46367296$0$16945$kpnplanet.nl... 

    Here I meant to say:
    sortId 0 (zero) will always be the lowest sortId
    of course.


    amygdala Guest

  15. #15

    Default Re: UPDATE and SELECT MAX


    "amygdala" <com> schreef in bericht
    news:46367af7$0$16943$kpnplanet.nl... 
    >>
    >> Take a look at
    >> http://www.phpriot.com/d/articles/client-side/sortable-lists-with-php-and-ajax/page5.html
    >> This method'll work with or without AJAX[/ref]
    >
    > Hi Paul,
    >
    > Yeah, this also let's PHP take care of the sorting for the most part.[/ref]

    Or AJAX rather.


    amygdala Guest

  16. #16

    Default Re: UPDATE and SELECT MAX


    "amygdala" <com> schreef in bericht
    news:463676f8$0$16942$kpnplanet.nl... 
    >> // update item
    >> $query = "UPDATE items SET ranking = $i WHERE item_id=
    >> $new_order[$i];";
    >>
    >> $result = $this->mMysqli->query($query);
    >> }
    >> $updatedList = $this->BuildItemsList();
    >> return $updatedList;
    >> break;[/ref]
    >
    > Hi strawberry,
    >
    > Well this seems more of a pretty basic update SQL query, where, I presume,
    > PHP takes care of all the sorting stuff before it gets put in the
    > database. Which off course is still a valid approach. But I was hoping to
    > learn some more complex SQL and let the SQL do all the work.
    >
    > Then again, the more I think about it, maybe it isn't even such a bad idea
    > to let PHP do the work, because it probably simplefies error handling (is
    > item x already highest in the sorting order, etc. ). Arghhhh, my changable
    > character is playing parts here, lol. Sorry about that.
    >
    > I think it would be best just for me to think this over a good time once
    > more before posting another question on the matter again. ;-)
    >
    > Thanks again for your time and input anyway!
    >
    > Cheers[/ref]

    Alright, FWIW I've come up with the following queries, which still uses SQL
    to swap for the most part, but lets PHP test whether there is an item to
    swap with.

    SELECT MAX(sortId) AS sortId
    FROM item
    WHERE pageId = :pageId AND sortId < :sortId LIMIT 1

    where I set the parameters
    :pageId to the current page id
    :sortId to the current sort id

    then, after I let PHP test whether the returned sortId is NOT null (there is
    a higher item to swap with) I do:

    UPDATE item AS i1, item AS i2
    SET i1.sortId = :newSortId, i2.sortId = :oldSortId
    WHERE i1.pageId = :pageId
    AND i1.pageId = i2.pageId
    AND i1.sortId = :oldSortId
    AND i2.sortId = :newSortId

    where I set the parameters
    :pageId to the current page id
    :oldSortId to the current sort id
    :newSortId to the found sortId in the previous query

    Don't know if this is the most efficient way, but it works like a charm.
    Suggestions for improvement are very welcome of course.

    Cheers


    amygdala Guest

Similar Threads

  1. Select-And-Update in one statement?
    By Ignoramus23298 in forum MySQL
    Replies: 1
    Last Post: May 19th, 07:02 PM
  2. a SELECT FOR UPDATE question
    By Tim in forum PostgreSQL / PGSQL
    Replies: 10
    Last Post: February 10th, 03:30 PM
  3. Update from a select
    By Bob Bedford in forum PHP Development
    Replies: 1
    Last Post: January 15th, 10:14 PM
  4. update from select
    By Gary Stainburn in forum PERL Beginners
    Replies: 2
    Last Post: October 29th, 04:51 PM
  5. Update and Select Statement
    By hngo01 in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 01:32 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139