CodingForums.com - Hover & Show Data in PHP/SQL table

Page 1 of 3

Show 40 post(s) from this thread on one page

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Hover & Show Data in PHP/SQL table (http://www.codingforums.com/showthread.php?t=283026)

stevenryals

11-26-2012 06:42 PM

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

Fou-Lu

11-26-2012 07:33 PM

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

stevenryals

11-26-2012 08:15 PM

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?

Fou-Lu

11-26-2012 08:21 PM

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.

stevenryals

11-26-2012 08:34 PM

Quote:

Originally Posted by Fou-Lu (Post 1294995)

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

Fou-Lu

11-26-2012 08:56 PM

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

stevenryals

11-26-2012 09:04 PM

Quote:

Originally Posted by Fou-Lu (Post 1295008)

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);

?>

stevenryals

11-26-2012 09:46 PM

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)

Old Pedant

11-26-2012 09:59 PM

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.

Fou-Lu

11-26-2012 10:04 PM

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.

Old Pedant

11-26-2012 10:05 PM

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.

stevenryals

11-26-2012 11:28 PM

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

Old Pedant

11-26-2012 11:50 PM

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.

Old Pedant

11-26-2012 11:54 PM

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.

Old Pedant

11-27-2012 12:00 AM

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.

All times are GMT +1. The time now is 11:16 PM.

Page 1 of 3

Show 40 post(s) from this thread on one page