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