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..
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).
$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']); } } }
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).
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);
?>
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..
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.
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.
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.
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..
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;
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.
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.
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.
Before you post, read our: 
