If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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

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.

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

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.

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.