Sorting by Joined Table Rows
Results 1 to 3 of 3

Thread: Sorting by Joined Table Rows

  1. #1
    Senior Member
    Join Date
    Jul 2007
    Posts
    181

    Sorting by Joined Table Rows

    Hi all

    I need help with a MySQL query more than anything I think so I hope I am not posting this incorrectly.

    I have a list of Hotels in a page and each Hotel has multiple Hotel Rooms associated to it. My Hotel list page needs to display the cheapest priced room such as:

    "Rooms available from: x.xx".

    Now, I obtain this price with a second query inside the loop of my main Hotel query as such:

    PHP Code:
    // main query loops through all Hotels and pulls out values such as $hotelid and shows the hotel details on the page
    $qry "SELECT hotelid, field2, field3 etc...";
    while (
    $row mysqli_fetch_array($link,$qry)) {
    $hotelid $row['hotelid'];

    // now a second query to get the minimum hotel room price for this Hotel
    $min     "SELECT MIN(CAST(sellcost AS DECIMAL(8,2))) AS price FROM tblhotelrooms WHERE hotelid = $hotelid LIMIT 1";
    $minres mysqli_query($link$min);

    Now this is working fine however I need to allow the user to be able to SORT the page by price ascending / descending and as the price is not physically assigned to the main Hotel query, I am not sure how I can do this.

    Does the second query need to be integrated with my first query somehow so I can then sort by it?

    If so, could anyone help in constructing that query structure?

    Many thanks for reading and any help offered.

    Doug

  2. #2
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    14,541
    My first thought is that maybe a sub-query would be the simplest way?
    PHP Code:
    // define $sortOrder based on user input, then...
    $qry "
    SELECT 
      hotelid,
      field2,
      field3. -- etc...
      (
        SELECT MIN(CAST(sellcost AS DECIMAL(8,2))) 
        FROM tblhotelrooms WHERE hotelid = 
    $hotelid LIMIT 1
      ) AS price
    FROM -- etc....
    ORDER BY price 
    $sortOrder
    "

    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    14,541
    Actually, I guess you could just do it with a JOIN:
    PHP Code:
    $qry "
    SELECT 
      t1.hotelid,
      t1.field2,
      t1.field3,
      MIN(CAST(t2.sellcost AS DECIMAL(8,2))) AS price 
        FROM tblhotelrooms WHERE hotelid = 
    $hotelid LIMIT 1
      ) AS price
    FROM first_table AS t1
    INNER JOIN tblhotelrooms AS t2 USING(hotelid)
    -- WHERE (whatever your conditions are for the main query
    ORDER BY price 
    $sortOrder
    "

    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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