Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Question Unanswered: Can I sort by a field in suquery

    Hi all, I am relatively new to mysql and I am having difficulty getting the syntax of a particular query correct. I have 3 tables that I access to display photos on a webpage.
    Table 1: photos (id, title, src)
    Table 2: menuItem (id etc. keeps info for each webpage... )
    Table 3: menuItemPhotos (menuItemid, photoid, ranking, Keeps track of what photos to display on a page and in what order to display them)

    My problem is displaying the images in the chosen order (eg. ranking in menuItemPhotos)
    eg.
    The webpage has an id = 6 from menuItem.
    The menuItemPhotos table holds 3 columns: id of webpage, photoid from photos, ranking (the order in which to display the photo)
    The photos table holds photo title and path.

    My problem is trying to display the photos in the order specified by the ranking. But it is just displaying them in Asc order according to the photo id.

    My query is:
    $select = "SELECT *";
    $from = " FROM photos";
    $where = " WHERE id IN";
    $inselect = " (SELECT photoid ";
    $infrom = " FROM menuItem, menuItemPhotos ";
    $inwhere = " WHERE menuItem.id = menuItemPhotos.menuItemid ";
    $inand1 = " AND menuItem.id = $id";
    $inorderby = " ORDER BY menuItemPhotos.ranking ASC)";

    What I am trying to do is display a series of images in the order specified by the ranking field.
    The above query displays the images according to the id of the photo, How can I display the results according to the subquery ranking field?

    Thanks
    Conor

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM menuItem
    INNER
      JOIN menuItemPhotos
        ON menuItemPhotos.menuItemid = menuItem.id
    INNER
      JOIN photos
        ON photos.id = menuItemPhotos.photoid
     WHERE menuItem.id = $id
    ORDER 
        BY menuItemPhotos.ranking ASC
    or alternatively...
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM menuItemPhotos
    INNER
      JOIN photos
        ON photos.id = menuItemPhotos.photoid
     WHERE menuItemPhotos.menuItemid = $id
    ORDER 
        BY menuItemPhotos.ranking ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •