Barand

I see you couldn't resist attempting to go straight to a coding "solution". If you cannot describe what you are trying to achieve, how do you expect to program it? So it seems your test_db table is actually a "lot" table, showing what is to be auctioned and when. It seems you also have need of an "auction" entity auction (auction_id, auction_date, venue). The date in the lot record would be replaced by the auction_id (as the date would belong to the auction). So far you have only mentioned historical data, like the average price that they sold at. It now seems you need to know which auctions are coming up what will be available (lots) at each auction (is a lot always a single bottle?) and, alternatively, what whiskies as coming up for auction at which auction/s will they be sold From either list click on a whisky for historical data (min, max, avg prices ?) click on auction for details of the venue All this is assumption and guesswork. Am I close?

Whatever you are wanting to do, there should be that new table. test_db whisky -------------- ---------- lot_id +---- whisky_id date | whisky_name whisky_id >---+ price url_img url_sale It would now help if you described precisely what you are trying to achieve. The business objective, not the coded solution.

So it is the transaction that is being reviewed. In which case there would be a transaction table too.

You need to clarify exactly what you want to search for. If you are searching for a type of whisky then you shouldn't searching test_db, which is a transaction table. You should be listing the separate table which I (and others) have already mentioned whisky (whisky_id, whisky_name) whisky_id should then be stored in test_db instead of the name. If you are searching for a particular transaction then use test_db.

What is being reviewed?

GROUP BY is for aggregation (SUM, COUNT, MIN, MAX etc). If you just want one of each, use DISTINCT and do not (ever) use SELECT * (except for testing). Always specify the columns you want to select. So to select one of each name you would SELECT DISTINCT name FROM test_db... Note that you cannot select the lot_id with DISTINCT as that would give every record.

You select records with same name but you then ORDER BY name - what is the point? If you want to see changes in price over time it would make more sense to ORDER BY date. Your prices are all integers, so why use the floor() function? My 0.02 worth.

Then all you need to do is loop through the results of that query, for each row send an email to the realtor email containing the customer email. end foreach

Use a combination of subquery and union. (Assumes the tablename is `fixture`) SELECT player , SUM(win) as won , SUM(draw) as drawn , SUM(lose) as lost FROM ( SELECT player_home as player , score_home > score_away as win , score_home = score_away as draw , score_home < score_away as lose FROM fixture UNION ALL SELECT player_away as player , score_away > score_home as win , score_away = score_home as draw , score_away < score_home as lose FROM fixture ) results GROUP BY player;

Yes, it gets the average prices of all whiskies and matches the one with same name as the one you clicked on. In the example in reply #10 above, lot_id was selected. +----------------+------------+---------+-----------+ | name | AVG(price) | url_img | avprice | +----------------+------------+---------+-----------+ | Aberfledy 20yo | 20.000000 | www. | 24.250000 | +----------------+------------+---------+-----------+ As this query shows, the averge price for all those with the name "Aberfledy 20yo" is £24.25 mysql> SELECT AVG(price) -> FROM test_db -> WHERE name='Aberfledy 20yo'; +------------+ | AVG(price) | +------------+ | 24.250000 | +------------+

You don't have a $row['price']. Your result columns are the same as those selected I left in AVG(price) to demonstrate that it wouldn't give what you expected. Change "AVG(price)" to "price" in that first part of the query. if (isset($_GET['id'])) { $sql = "SELECT name , price , url_img , avprice FROM test_db JOIN ( SELECT name , AVG(price) as avprice FROM test_db GROUP BY name ) avcalc USING (name) WHERE lot_id = :id"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo "{$row['name']}<br> £{$row['price']}<br> <img src='{$row['url_img']}'/><br> {$row['avprice']}"; }

That query gives results similar to this (as per my reply over at devshed) +-----+--------------------+----------+-------------------+ | rid | rEmail | fullname | email | +-----+--------------------+----------+-------------------+ | 1 | [email protected] | Cust_1 | [email protected] | | 1 | [email protected] | Cust_3 | [email protected] | | 1 | [email protected] | Cust_5 | [email protected] | | 1 | [email protected] | Cust_10 | [email protected] | | 2 | [email protected] | Cust_2 | [email protected] | | 2 | [email protected] | Cust_6 | [email protected] | | 2 | [email protected] | Cust_8 | [email protected] | | 2 | [email protected] | Cust_9 | [email protected] | | 3 | [email protected] | Cust_4 | [email protected] | | 3 | [email protected] | Cust_7 | [email protected] | +-----+--------------------+----------+-------------------+ This gives the realtors with customers that are within the prescribed distance (in this case 100Km). So realtor 1 has customers 1, 3, 5, 10 within range, realtor 2 has 2, 6, 8, 9 and realtor 3 has 4 and 7 within range. What you haven't told is us what you want to do with that data, other than a vague "I want to send emails"

If you run the query with the sample data you gave SELECT name , AVG(price) , url_img , avprice FROM test_db JOIN ( SELECT name , AVG(price) as avprice FROM test_db GROUP BY name ) avcalc USING (name) WHERE lot_id = 12346; then you get +----------------+------------+---------+-----------+ | name | AVG(price) | url_img | avprice | +----------------+------------+---------+-----------+ | Aberfledy 20yo | 20.000000 | www. | 24.250000 | +----------------+------------+---------+-----------+ As I told you, AVG(price) is the price of that single lot. The average for those with the same name (given in avprice from the subquery) is 24.25. To output the avprice value after running the query you obviously need to use $row['avprice']

If you select the AVG(price) WHERE lot_id = id then that average will always be the price of the single record that is selected. You need to use a subquery to get the av price of the whiskies with the same name. Incidentally, those names should be in a separate table (with an id) and that id should be store in test_db instead of the name. SELECT name , AVG(price) , url_img , avprice FROM test_db JOIN ( SELECT name , AVG(price) as avprice FROM test_db GROUP BY name ) avcalc USING (name) WHERE lot_id = :id";

You may find my resource booking tutorial is of some help.

It is often better to describe what the problem is rather try to describe your solution to that problem.

You could always send me one

I altered its color property just to hilight it. You can change the style in the "model" menu EDIT: * how refreshing to see someone correctly use "different from" instead "different than".

Sorry, my fault. I was torn between writing pseudocode and actual code. Should be $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo $row['name'];

No. The search stays as it is. That lists all the products matching the search criteria. The user then clicks on one of the links you just added. This takes them to detail.php which queries the db for that selected product and displays the full details (for that one product).

The table you needed to add is the "reserva_hora" to give a many-to-many relationship between reserva and hora.

Your syntax makes no sense. You need something like if (isset($_GET['id'])) { $sql = "SELECT name, etc FROM test_db WHERE lot_id = :id"; $conn->prepare($sql); $conn->execute( [ 'id' => $_GET['id'] ] ); /// display results }

right.

In your link, include the id of the whisky record in the querystring echo "<a href='whisky_details.php?id={$row['id']}' >{$row['name']}</a>;"; In the detail page, query the table for the record with that id (in $_GET['id'] ) then display the details.

I added the code tags for you this time. Is it the linking to the whisky details you are having a problem with?