Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Great. Now just what is it you want to display, GIVEN that sample data??

    Not sure that's exactly what you want, and of course it doesn't relate all that well to the expedia data, but the point is that you can do everything in a single query.

    no it's good info.. thanks.. I'm trying to basically a) dump all this into a table.. and for each <tr> tag add a Title attribute that will display the lowest price for the route that is displayed in the field "tag".. there may be 1000 "NYC-ORD" tags with different prices.. but only 1 lowest.. so when i mouseover any row with "NYC-ORD" as the tag (or whatever) it pops up the title that says "Lowest Price is $142.17 on expedia.com" (price and url are both stored, but i can do wthout the "expedia.com" as it's easily searchable in the table once you know..)

    i'll try to massage this to work for my scenario.. I've been doing PL/PY/JS for a bit, and now I'm working on a front end for some of my scripts.. and dumping all the data into SQL has me learning php/sql.. so unfortunately I'm a bit of a newb and it's showing.. my apologies

  2. #17
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks Old Pedant.. I appreciate your help here as well..

    If i could as a couple of elementary questions (feel free not to answer as I'm certain they are very elementary)

    Code:
    SELECT L.lowest, E.*
    FROM expedia AS E,
         ( SELECT tag, MIN(price) AS lowest
           FROM expedia
           GROUP BY tag ) AS L
    WHERE E.tag = L.tag
    ORDER BY E.tag, E.price
    what is the significance of 'L' and 'E'
    basically.. what exactly is happening here..
    with L.lowest, L, and E not being declared before.. i'm a bit confused.. where would this go?

  3. #18
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    NOTE: Do you REALLY want this grouped by tag??? Wouldn't it make more sense to have it grouped by (say) leg1? So you would have the lowest rate for all "BHM-MAD" flights? But that's up to you and a trivial change to the SQL query, of course.

    it could be whats called an "open jaw" trip.. BHM-MAD + BCN-BHM which would be different than just BHM-MAD-BHM.. so i added the tag to track each individual search.. thats why i chose to use the tag as the 'go-to' for this.. the script behind this is pretty good, so routes become fairly versitile... it assumes nothing.. like BHM-MAD for leg one could have MOW-BKK for leg2..

  4. #19
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,574
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Quote Originally Posted by stevenryals View Post
    Code:
    SELECT L.lowest, E.*
    FROM expedia AS E,
         ( SELECT tag, MIN(price) AS lowest
           FROM expedia
           GROUP BY tag ) AS L
    WHERE E.tag = L.tag
    ORDER BY E.tag, E.price
    what is the significance of 'L' and 'E'
    basically.. what exactly is happening here..
    with L.lowest, L, and E not being declared before.. i'm a bit confused.. where would this go?
    E and L are just "aliases". It allows me to use just "E." in place of "expedia." in the query and "L." to refer to the inner SELECT there.

    It's basically the same thing as when I do MIN(price) AS lowest, which then later allows me to refer to MIN(price) with just the name lowest.

    You don't have to use alias for tables, but you do have to create one when you use an inner SELECT as I did there: I have to give the result of that inner SELECT some sort of name so that I can use it as a pseudo-table in other parts of the query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  5. #20
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,574
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    And so instead of my
    Code:
    <tr onmouseover="...">
    you can try just using
    Code:
    <tr title="Lowest price for this flight is $<?php echo row['lowest'];?>">
    But the problem with a title is that it doesn't stay on the screen very long. If you want a more persistent message, you can use JS as I showed you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  6. #21
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm having a tough time getting this to work...

    what if I selected all my data and put it in $row ... then when i get to the `tag` <td> on each row.. could i do something like this specific for that field??

    Code:
    //should this only return 1 record? the minimum of the specified tag?
    $min = mysql_query("SELECT `tag`, MIN(`price`) AS lowest FROM expedia where `tag` = $row[`tag`]") ;
    
    while ($tagrow = mysql_fetch_array($result, MYSQL_NUM)) {
        "PRINT TD with TITLE HERE";
    }
    
    mysql_free_result($min);
    and this would cycle though the larger "while" statement to print the rest of the fields. but would go through this once for each row??

    is that a bad idea?

  7. #22
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    OK.. I think i'm very close now..

    when i vardump as shown below i get: about 500 lines of this:
    array(4) { [0]=> NULL ["tag"]=> NULL [1]=> NULL ["MIN(`price`)"]=> NULL } 0.779177 array(4) { [0]=> NULL ["tag"]=> NULL [1]=> NULL ["MIN(`price`)"]=> NULL } 80.4506 array(4) { [0]=> NULL ["tag"]=> NULL [1]=> NULL ["MIN(`price`)"]=> NULL } 0.779177 array(4) { [0]=> NULL ["tag"]=> NULL [1]=> NULL ["MIN(`price`)"]=> NULL } 80.4506 array(4) { [0]=> NULL ["tag"]=> NULL
    So i'm obviously not passing $rows[`tag`] correctly into the new query.. and also, not sure where all the 0.779177 and all those numbers are coming from either??


    Code:
    $result = mysql_query("SELECT * FROM {$table}");
    if (!$result) {
        die("Query to show fields from table failed");
    }
    
    $fields_num = mysql_num_fields($result);
    
    
    while($rows=mysql_fetch_array($result))  {
    
    $min = mysql_query("SELECT `tag`, MIN(`price`) FROM {$table} AS `minprice` where `tag` = ' . rows[`tag`] . '") or die(mysql_error());
    $minrow = mysql_fetch_array($min) or die(mysql_error());
    var_dump($minrow);
    
    $rownumber++;
    echo "<tr title='Lowest Price is " . $minrow['minprice'] . "'>";
    echo "<td align='center'> $rownumber </td>";
    
    mysql_free_result($min);
    ?>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['tag']; ?></td>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['url']; ?></td>
    
    <?php
    
    }
    ?>
    </table>
    <?php
    
    mysql_free_result($result);
    mysql_close();
    ?>
    I think i'm very close on this... i can taste it LOL

  8. #23
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    OK. now i'm passing a variable over well enough.. but i only get 1 variable for all different tags.. so i get the lowest overall price instead of the lowest price for a particular tag..

    here's my code as of now:

    Code:
    $result = mysql_query("SELECT * FROM {$table}");
    if (!$result) {
        die("Query to show fields from table failed");
    }
    
    $fields_num = mysql_num_fields($result);
    
    
    while($rows=mysql_fetch_array($result))  {
    
    $min= 'SELECT min(price) as min FROM expedia';
    $minrow = mysql_query($min);
    $row = mysql_fetch_assoc($minrow);
    $min_price_raw = $row['min'];
    $min_price = substr($min_price_raw, 0, -10);
    
    $rownumber++;
    echo "<tr title='Lowest Price is $" . $min_price . " for " . $rows['leg1'] . " + " . $rows['leg2'] . "'>";
    
    unset($min_price_raw);
    unset($min_price);
    mysql_free_result($minrow);
    
    echo "<td align='center'> $rownumber </td>";
    
    ?>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['tag']; ?></td>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['url']; ?></td>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['price']; ?></td>

  9. #24
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,574
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Well, since you are no longer using my answer, at all, I'm not sure what to tell you.

    But of course you are getting the lowest overall price. That's what
    Code:
    SELECT min(price) as min FROM expedia
    is *SUPPOSED* to do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  10. #25
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Well, since you are no longer using my answer, at all, I'm not sure what to tell you.

    But of course you are getting the lowest overall price. That's what
    Code:
    SELECT min(price) as min FROM expedia
    is *SUPPOSED* to do.
    Sorry Old Pedant.. being totally honest, your answer was probably perfect, but was just over my head.. LOL

    I had the "where" on there but couldnt get it to bring in the $rows['tag'] from the original query..

    I did find out what was happening though..

    through a series of vardumps.. i found it was bringing back "NULL" values which was causing me a problem with the "WHERE `tag` = `". $rows['tag'] ."`" statement

    it was pulling the 'id' from teh database as 'tag' ... i have no idea why.. i never once specified the term "id" within the script..

    so i changed the original SELECT to specify the fields i wanted, instead of SELECT * FROM expedia.. so it's working like a charm now..

    here's what I ended up with:


    $result = mysql_query("SELECT tag, url, price, miles, cpm, leg1, departuredate, leg2, arrivaldate, strike, strikedate, localprice, currency, fxrate, date FROM {$table}");
    if (!$result) {
    die("Query to show fields from table failed");
    }

    $fields_num = mysql_num_fields($result);


    while($rows=mysql_fetch_array($result)) {

    // POPULATE MINIMUM PRICE BY TAG



    $min= "SELECT `tag`, min(`price`) AS `min` FROM {$table} WHERE `tag` = '". $rows[`tag`] ."'";
    $minrow = mysql_query($min) or die(mysql_error());
    $row = mysql_fetch_assoc($minrow) or die(mysql_error());
    $min_price_raw = $row['min'];
    $min_price = substr($min_price_raw, 0, -10);
    $rownumber++;
    echo "<tr title='Lowest Price is $" . $min_price . " for " . $rows['leg1'] . " + " . $rows['leg2'] . " with Search Tag :" . $rows['tag'] . "'>";

    unset($min_price_raw);
    unset($min_price);

    mysql_free_result($minrow);

    echo "<td align='center'> $rownumber </td>";

    ?>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['tag']; ?></td>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['url']; ?></td>
    <td align='center' border='1' frame="box" rules="all"><? echo $rows['price']; ?></td>


    }
    ?>
    </table>
    <?php

    mysql_free_result($result);
    mysql_close();
    ?>



    it's probably more cumbersome than any of the other suggestions in this thread.. but it was something i was capable of doing without asking one of you to take my code and just do it for me.. I wouldnt have learned anything having done that..

    However, I am going to copy this file, and continue to try to improve this solution by utilizing your and other suggestions.. and hopefully that will contribute to more learning opportunities for me.. and also probably speed the page up a bit..

    again, thanks so much for your, and everyone elses, help.. I'm sorry i was such a bother.. i hope i can learn more and contribute to the community

  11. #26
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,574
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Your HTML there is ILLEGAL. You have no </tr> to match each <tr>.

    Anyway, I don't use PHP, but I don't see why you couldn't have at least tried my query. The transformation of your code to use it iis nearly trivial.
    Code:
    <?php
    
    $sql = "
        SELECT L.lowest, E.*
        FROM expedia AS E,
             ( SELECT tag, MIN(price) AS lowest
               FROM expedia
               GROUP BY tag ) AS L
        WHERE E.tag = L.tag
        ORDER BY E.tag, E.price"; // change ORDER BY if you wish
    
    $result = mysql_query( $sql ) or die( mysql_error() );
    
    $rownumber = 0;
    while( $rows = mysql_fetch_array($result) ) 
    {
        ++$rownumber;
    
        $lowest = "$" . $rows["lowest"];
        $leg1   = $rows["leg1"];
        $leg2   = $rows["leg2"];
        $tag    = $rows["tag"];
        $url    = $rows["url"];
        $price  = $rows["price"];
       
        $ttl = "Lowest Price is $lowest for $leg1 - $leg2 with Search Tag : $tag";
    ?>
    <tr title="<?echo $ttl;?>">
        <td align="center"><?echo $rownumber ?></td>
        <td align='center' border='1' frame="box" rules="all"><? echo $tag; ?></td>
        <td align='center' border='1' frame="box" rules="all"><? echo $url; ?></td>
        <td align='center' border='1' frame="box" rules="all"><? echo $price; ?></td>
    </tr>
    <?php
    }
    mysql_free_result($result);
    mysql_close();
    ?>
    </table>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  12. Users who have thanked Old Pedant for this post:

    stevenryals (11-28-2012)

  13. #27
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Your HTML there is ILLEGAL. You have no </tr> to match each <tr>.

    Anyway, I don't use PHP, but I don't see why you couldn't have at least tried my query. The transformation of your code to use it iis nearly trivial.
    Code:
    <?php
    
    $sql = "
        SELECT L.lowest, E.*
        FROM expedia AS E,
             ( SELECT tag, MIN(price) AS lowest
               FROM expedia
               GROUP BY tag ) AS L
        WHERE E.tag = L.tag
        ORDER BY E.tag, E.price"; // change ORDER BY if you wish
    
    $result = mysql_query( $sql ) or die( mysql_error() );
    
    $rownumber = 0;
    while( $rows = mysql_fetch_array($result) ) 
    {
        ++$rownumber;
    
        $lowest = "$" . $rows["lowest"];
        $leg1   = $rows["leg1"];
        $leg2   = $rows["leg2"];
        $tag    = $rows["tag"];
        $url    = $rows["url"];
        $price  = $rows["price"];
       
        $ttl = "Lowest Price is $lowest for $leg1 - $leg2 with Search Tag : $tag";
    ?>
    <tr title="<?echo $ttl;?>">
        <td align="center"><?echo $rownumber ?></td>
        <td align='center' border='1' frame="box" rules="all"><? echo $tag; ?></td>
        <td align='center' border='1' frame="box" rules="all"><? echo $url; ?></td>
        <td align='center' border='1' frame="box" rules="all"><? echo $price; ?></td>
    </tr>
    <?php
    }
    mysql_free_result($result);
    mysql_close();
    ?>
    </table>
    i do have the <tr></tr> but not in that paste.. it's before the code when i specify the headers and the </tr> is after the <td>'s at the bottom (have about 15 more columns i'm outputting, so i accidently deleted that to make my code paste a bit smaller and easier to read... sorry about that.

    That does make more sense now, seeing it in the code.. but i didnt want to say "i dont get it , can you just do it for me?"

    so E.* is getting everything from the table, and L. is getting the minimum price for each tag... is that correct?

    so your $sql query returns: All items in database (as E) + minimum price (as L) from the same query..

    I am now following you.. and this is (obviously) way better and faster than my nested query.. which in time, will be very slow.. I'm already at 3500 records..

    Again, thanks so much for your help.. i'll give this a go.. i really appreciate it..

  14. #28
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ok trying it out now..
    Last edited by stevenryals; 11-28-2012 at 06:58 PM.

  15. #29
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,574
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    FWIW, you could have tried my query in some standalone query tool (e.g., phpmyadmin or whatever it is called) without using your own PHP code. That would have shown you what the query is doing. And whether or not I had it right.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  16. #30
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    FWIW, you could have tried my query in some standalone query tool (e.g., phpmyadmin or whatever it is called) without using your own PHP code. That would have shown you what the query is doing. And whether or not I had it right.
    Thanks for the tip

    that occurred to me.. seeing the results helped a tad.. but the aliases (and the nested query in there) was new to me.. so i wanted to learn exactly how the command structure was creating those results..

    still a little foggie on it.. but i do think i understand now.. recreating it in another situation may be difficult for me.. but at least thats another building block to a decent foundation of understanding.



    Thanks


 
Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

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