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 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Hover & Show Data in PHP/SQL table

    HI all..

    I have a table generated by PHP from my SQL database..
    it's quite simple..

    in one column i have a identifier (which is not unique) and in another column i have a price.

    Is there any way to hover the table row, and have a small popup (or title?) displaying the lowest price for the column with the identifier?

    for instance.. i have a talbe with 300 records.. 25 of them have the identifier "Television" in the 1st column.. can i hover over a row(or just the identifier) with "Television" and have little pop-up tell me what the lowest price(located in column 2) associated with this identifier throughout the table is??
    Last edited by stevenryals; 11-26-2012 at 06:53 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You can use the title attribute.
    For the lowest price, that would best be done when selecting from the database using the MIN() function. Group by the identifier, and it will pull the minimum price.
    Can't work without any data, but that would look something like:
    PHP Code:
    $sQry 'SELECT `identifier`, `price`, MIN(`price`) AS lowest FROM myproducts GROUP BY `identifier` ORDER BY `identifier`';
    if (
    $qry mysql_query($sQry))
    {
        while (
    $row mysql_fetch_assoc($qry))
        {
            
    printf('<tr><td title="%s">%s</td><td>%0.2f</td></tr>''Lowest Price: ' $row['lowest'], $row['identifier'], $row['price']);
        }

    For example.
    Take note though that this will only pull the lowest of the returned resultset, so if you limit it you will have skewed results. To pull from the entire table all the time, either use a second query or use a sub select query within the select. The SQL guys would be able to tell you which is better (I'd expect 2x queries to be better than 1x query with a nested select).

  • #3
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    This is a great idea..

    what i have now is a bit simpler..

    Code:
    while($row = mysql_fetch_row($result))
    {
        echo "<tr>";
        echo "<td> $rownumber </td>";
        foreach($row as $cell)
            echo "<td  align='center' border='1' bordercolor=black>$cell</td>";
        $rownumber++;    
        
        echo "</tr>";
    }
    I'll see what i can do with what you have above, but i'd like to see what the 2x queries structure may look like as well..

    Maybe this thread should be moved to the SQL forum?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    2x queries would look like so:
    PHP Code:
    $sMinQry 'SELECT `identifier`, MIN(`price`) AS lowest FROM myproducts GROUP BY `identifier`';
    if (
    $minQry mysql_query($sMinQry))
    {
        
    $aMins = array();
        while (
    $row mysql_fetch_assoc($minQry))
        {
            
    $aMins[$row['identifier']] = $row['lowest'];
        }

        
    $sQry 'SELECT `identifier`, `price` FROM myproducts ORDER BY `identifier`';
        if (
    $qry mysql_query($sQry))
        {
            while (
    $row mysql_fetch_assoc($qry))
            {
                
    printf('<tr><td title="%s">%s</td><td>%0.2f</td></tr>''Lowest Price: ' $aMins[$row['identifier']], $row['identifier'], $row['price']);
            }
        } 

    Yeah I'll move this to the MySQL forum as well.

  • #5
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Yeah I'll move this to the MySQL forum as well.
    Thanks a lot for your help. i've used that and it's returning a blank set of results.. Hmmmmm..


    Here's what i have after connecting, testing and printing the table headers:

    Code:
    $sMinQry = 'SELECT `tag`, `price`, MIN(`price`) AS lowest FROM expedia GROUP BY `tag`';
    if ($minQry = mysql_query($sMinQry))
    {
        $aMins = array();
        while ($row = mysql_fetch_assoc($minQry))
        {
            $aMins[$row['tag']] = $row['lowest'];
        }
    
        $sQry = 'SELECT `tag`, `price` FROM myproducts ORDER BY `tag`';
        if ($qry = mysql_query($sQry))
        {
            while ($row = mysql_fetch_assoc($qry))
            {
                printf('<tr><td title="%s">%s</td><td>%0.2f</td></tr>', 'Lowest Price: ' . $aMins[$row['tag']], $row['tag'], $row['price']);
            }
        } 
    }
    i do have complete records in the database.. not sure why it's doing that..

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Is your table actually named myproducts, or is it named expedia?
    I didn't put an else or a die on the queries, so it won't tell you if it failed (for that I wrapped it in only processing if it was successful).

    BTW, instead of a foreach within a while loop, you can use vprintf in order to just give it an array to work with (and the format string of course).

  • #7
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Is your table actually named myproducts, or is it named expedia?
    I didn't put an else or a die on the queries, so it won't tell you if it failed (for that I wrapped it in only processing if it was successful).

    BTW, instead of a foreach within a while loop, you can use vprintf in order to just give it an array to work with (and the format string of course).
    it's named 'expedia'

    if i add:

    Code:
    else
      {
      die(mysql_error());
      }
    I still get nothing but table headers..

    Here's what i have now:

    Code:
    if (!mysql_connect($db_host, $db_user, $db_pwd))
        die("Can't connect to database");
    
    if (!mysql_select_db($database))
        die("Can't select database");
    
    $sMinQry = 'SELECT `tag`, `price`, MIN(`price`) AS lowest FROM expedia GROUP BY `tag`';
    if ($minQry = mysql_query($sMinQry))
    {
        $aMins = array();
        while ($row = mysql_fetch_assoc($minQry))
        {
            $aMins[$row['tag']] = $row['lowest'];
        }
    
        $sQry = 'SELECT `tag`, `price` FROM myproducts ORDER BY `tag`';
        if ($qry = mysql_query($sQry))
        {
            while ($row = mysql_fetch_assoc($qry))
            {
                printf('<tr><td title="%s">%s</td><td>%0.2f</td></tr>', 'Lowest Price: ' . $aMins[$row['tag']], $row['tag'], $row['price']);
            }
        } 
    }
    else
      {
      die(mysql_error());
      }
    
    
    mysql_free_result($result);
    ?>

  • #8
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    OK.. i think im on a good path here..

    I'm doing this after connecting and printing the table headers:

    Code:
    $result = mysql_query("SELECT * FROM {$table}");
    if (!$result) {
        die("Query to show fields from table failed");
    }
    
    
    while($rows=mysql_fetch_array($result))  {
    ?>
    <tr>
    <?php 
    $sQry = 'SELECT `tag`, `price`, MIN(`price`) AS lowest FROM expedia GROUP BY `tag`';
    if (!$sQry) {
         printf('<td align='center' border='1' frame="box" rules="all"><? echo $rows['tag']; ?></td>');  
    }
    else 
    {
            printf('<tr><td title="%s">%s</td><td>%0.2f</td></tr>', 'Lowest Price: ' . $row['lowest'], $row['identifier'], $row['price']);
    }
    mysql_free_result($sQry);
    ?> 
    <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>
    
    </tr>
    
    <?php
    }
    ?>
    </table>
    Not working as of yet..

    i'm able to get it done with a while within the if.. but i cant get back to the original SQL data..
    (if you can't tell i've only been learning SQL for about 2 months.. sorry so noob)
    Last edited by stevenryals; 11-26-2012 at 09:50 PM.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    That will *NEVER* work!
    Code:
    SELECT `tag`, `price`, MIN(`price`) AS lowest FROM expedia GROUP BY `tag`
    It doesn't even make SENSE!

    WHY would you get *BOTH* price AND MIN(price)???

    The real problem here is that MySQL is *TOO FLEXIBLE*. That fact that it even ALLOWS you to make that query is, in my mind, a DEFICIENCY.

    No other DB engine would allow it. Others would all complain that you used the field price but then did not put it in your GROUP BY. And if you then indeed used
    Code:
    SELECT tag, price, MIN(price) AS lowest FROM expedia GROUP BY tag, price
    they would complain that you used the same field in an aggregate function as you used in GROUP BY.

    The *RIGHT* answer, in any DB, is to get rid of price in the query:
    Code:
    SELECT tag, MIN(price) AS lowest FROM expedia GROUP BY tag
    and then you *PROBABLY* also want some kind of ORDER BY on that. Most likely
    Code:
    SELECT tag, MIN(price) AS lowest FROM expedia GROUP BY tag ORDER BY lowest
    Oh...and why you think you need those back ticks all over the place, I have no idea. You only need them if the name of your field or table is a MySQL keyword *OR* if you have illegal characters in your name. Neither of which apply here.
    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
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Oh you haven't queried anything. The $sQry is simply the string, but you don't have a mysql_query called upon it.
    You definitely don't want to nest your queries if you can avoid it (usually you can). Calling a query within a query is many queries to the database which is much slower than a single large resultset query (at least I'm pretty sure its slower). Not to mention that the DB itself can be locked down to only accept x number of queries per hour, so you want to avoid looping with queries within them. Always goal for only 1 query per script (I've managed to get it down to 3 in a functional environment which has me very happy, but typically I'm between 8 and 15 due to how I build my objects).

    What I actually meant was this line here:
    PHP Code:
        $sQry 'SELECT `tag`, `price` FROM myproducts ORDER BY `tag`'
    You have still used the table I placed in there myproducts. Change that to expedia and you should get the records. myproducts is (presumably) not a valid table in your schema.

    Edit:
    Old Pedant weighed in there.
    Oh yeah, your right! How did I miss that pulling price and min(price) would just result in the record of the price and the price. Lol. Oh not to mention I missed the price in the group by.
    Last edited by Fou-Lu; 11-26-2012 at 10:07 PM.

  • Users who have thanked Fou-Lu for this post:

    stevenryals (11-28-2012)

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    If you wanted to see something like this:
    Code:
    group        product     price    lowest price
    television   lg            $388     $388
    television   rca           $430     $388
    television   sony          $478     $388
    microwave    amana         $138     $138
    microwave    panasonic     $158     $138
    microwave    ge            $169     $138
    that CAN AND SHOULD be all done in a SQL query.

    And then your PHP code would just pull the data from the records and display it with no futher PHP processing.

    If that's what you are after, provide some sample data to work with and I'll show you how.
    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
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Old pedant.. I'm sure you know better than me, but both must be displayed.. i guess you query price, and then somehow determine MIN?

    Fou-Lu.. I did get this working with your script above... but when i try to add in all of my other fields it's dying out.. and I'm certain that it's my query that's doing so.. (p.s. i was running mysql_query and trying to dump the vars... just pasted too soon and forgot to update it... sorry about that)

    here are my table details fields:

    tag
    url
    price
    miles
    cpm
    leg1
    departuredate
    leg2
    arrivaldate
    leg3
    finaldate
    localprice
    currency
    fxrate
    date

    I'm simply (it makes me feel worse actually that i keep saying "simply" lol) trying to dump these values into a table.. and show the minimum price available based on the 'tag', in the <tr title="The Minimum Price is: $MINPRICE on $URL"> of course i know thats not right.. but that's the idea behind it...

    basically a tag will be "NYC-DEN" and i will load the table with data for NYC-DEN flights.. then if you see "NYC-DEN" you can hover the table row, and it will give you a minimum price for that route..

    does that make any sense?

    here is some quick sample data:
    Code:
    sr_city-test	expedia.eu 993.385	8885.36	7.80369	BHM-MAD	2013-03-03	MAD-BHM	2013-03-10	NULL	NULL		740.27	EUR	0.779177	2012-11-23 02:32:35
    sr_city-test	expedia.jp	900.554	8885.36	7.88436	BHM-MAD	2013-03-03	MAD-BHM	2013-03-10	NULL	NULL		96360	JPY	80.4506	2012-11-23 02:32:23
    sr_bhm_mad edreams.eu 998.484	8885.36	8.98651	BHM-MAD	2013-03-03	MAD-BHM	2013-03-10	NULL	NULL		722.16	EUR	0.779177	2012-11-22 10:59:19
    sr_bhm_mad expedia.mx 970.078	8885.36	8.66682	BHM-MAD	2013-03-03	MAD-BHM	2013-03-10NULL	 NULL	   14040	MXN	13.0376	2012-11-22 10:26:44
    Last edited by stevenryals; 11-26-2012 at 11:39 PM. Reason: By the way, thank you guys so much for helping me.. I really appreciate it!!!

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    Great. Now just what is it you want to display, GIVEN that sample data??

    *********

    Just to demo the concept, here's my sample data:
    Code:
    mysql> select * from prod;
    +------------+-----------+-------+
    | prodgroup  | product   | price |
    +------------+-----------+-------+
    | television | rca       |   430 |
    | television | lg        |   388 |
    | microwave  | panasonic |   158 |
    | microwave  | amana     |   138 |
    | microwave  | ge        |   169 |
    | television | sony      |   478 |
    +------------+-----------+-------+
    And then here is the query I used:
    Code:
    mysql> select p.prodgroup, p.product, p.price, m.lowest
        -> from prod as p, ( 
        ->      select prodgroup, min(price) as lowest from prod group by prodgroup 
        -> ) AS m
        -> where p.prodgroup = m.prodgroup
        -> order by prodgroup, product, price;
    to produce this output:
    Code:
    +------------+-----------+-------+--------+
    | prodgroup  | product   | price | lowest |
    +------------+-----------+-------+--------+
    | microwave  | amana     |   138 |    138 |
    | microwave  | ge        |   169 |    138 |
    | microwave  | panasonic |   158 |    138 |
    | television | lg        |   388 |    388 |
    | television | rca       |   430 |    388 |
    | television | sony      |   478 |    388 |
    +------------+-----------+-------+--------+
    So... a list of everything in the "group" (what you were calling "tag"), ordered by price, and showing IN EACH ROW the lowest price in the group.

    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.
    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.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    It *looks* to me like you could do something like this:
    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
    to get similar results from your data.
    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.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    So if you did that, you would dump out all the data for one row into your table, using PHP, and add in something like this:
    Code:
    <tr onmouseover="showMin(this,<?php echo $row['lowest'];?>)">
        <td><?echo $row['tag'] ?></td>
        ... other fields in the row ...
    </tr>
    And now you just need the JavaScript code for that showMin function to display a nice popover box with the minimum price for that tag.

    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.
    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.


  •  
    Page 1 of 3 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
    •