Barand

Is the average price the only thing you want to show as a result of the query on that page?

In records.php, for example, if you want the whisky name then you need to join the test_db table to the whisky table SELECT whisky_id , date , whisky_name as name , price FROM test_db INNER JOIN whisky USING (whisky_id) -- required to get the name WHERE t.whisky_id = :id ORDER BY date DESC

Change query to SELECT rEmail , eMail , c.fullname FROM realtors r LEFT JOIN customers c ON pow(clatitude-rlatitude, 2) + pow((clongitude-rlongitude)*cos(radians(rlatitude)), 2) < pow(willtotravel/69.13, 2) ORDER BY rid, id

If you haven't already, add an index to test_db on the whisky_id column. CREATE INDEX `idx_test_db_whisky_id` ON `test_db` (whisky_id);

Change LEFT JOIN to INNER JOIN. That way only realtors with customers will be found.

It's a one-off operation so slowness isn't a real issue.

Better eyes than me. I could barely make out the last line of that screenshot.

works for me mysql> INSERT INTO whisky (whisky_name) -> SELECT DISTINCT name -> FROM test_db -> ORDER BY name; Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0

The whole point of a database is that each item of information, like a name, is stored once. The only things that appear more than once are the ids which are used to link the tables. 1 ) start with the test_db you had originally and add a new column (type INT) called "whisky_id". 2 ) Create the table "whisky". CREATE TABLE `whisky` ( `whisky_id` int(11) NOT NULL AUTO_INCREMENT, `whisky_name` varchar(40) DEFAULT NULL, `url_img` varchar(45) DEFAULT NULL, PRIMARY KEY (`whisky_id`) ) ENGINE=InnoDB; 3 ) populate the whisky table with the unique name from test_db. The whisky ids will be auto generated. INSERT INTO whisky (whisky_name) SELECT DISTINCT name FROM test_db ORDER BY name; 4 ) Now you need to put those ids from the whisky table into the new column in test_db UPDATE test_db t JOIN whisky w ON t.name=w.whisky_name SET t.whisky_id = w.whisky_id; 5 ) Similarly (but in opposite direction) transfer url_img from test_db to the matching whisky table record. UPDATE test_db t JOIN whisky w USING (whisky_id) SET w.url_img = t.url_img; Job done. Now you can drop the redundant name and url_img columns from the test_db table.

"Aberfeldy 20yo" may appear in test_db many dozens of times but will only be stored once in the whisky table, with its id. So you'd have test_db +--------+-----------+------------+-------+----------+ | lot_id | whisky_id | date | price | url_sale | +--------+-----------+------------+-------+----------+ | 12346 | 1 | 2016-05-01 | 20.00 | www. | | 13325 | 1 | 2016-07-01 | 25.00 | www. | | 17389 | 2 | 2016-07-01 | 59.00 | www. | | 17947 | 1 | 2016-01-05 | 30.00 | www. | | 18256 | 2 | 2016-06-01 | 58.00 | www. | | 19308 | 1 | 2016-01-01 | 22.00 | www. | | 20156 | 2 | 2016-09-01 | 48.00 | www. | | 26547 | 2 | 2016-11-01 | 36.00 | www. | +--------+-----------+------------+-------+----------+ whisky +-----------+----------------+---------+ | whisky_id | whisky_name | url_img | +-----------+----------------+---------+ | 1 | Aberfledy 20yo | www. | | 2 | Grouse 5yo | www. | +-----------+----------------+---------+

When searching the whisky table for the entered name you don't need the DISTINCT. All names in that table should be unique. The one part that may give you problems is the min and max prices and the dates they occurred. This will find those for you SELECT w.whisky_name , w.url_img , lowprice , lowdate , hiprice , hidate , avprice FROM whisky w INNER JOIN ( SELECT whisky_id , AVG(price) as avprice FROM test_db GROUP BY whisky_id ) ap USING (whisky_id) INNER JOIN ( SELECT whisky_id , t.date as lowdate , t.price as lowprice FROM test_db t JOIN ( SELECT whisky_id , MIN(price) as price FROM test_db GROUP BY whisky_id ) min_p USING (whisky_id, price) ) min_d USING (whisky_id) INNER JOIN ( SELECT whisky_id , t.date as hidate , t.price as hiprice FROM test_db t JOIN ( SELECT whisky_id , MAX(price) as price FROM test_db GROUP BY whisky_id ) max_p USING (whisky_id, price) ) max_d USING (whisky_id) WHERE w.whisky_id = 1; results +----------------+---------+----------+------------+---------+------------+-----------+ | whisky_name | url_img | lowprice | lowdate | hiprice | hidate | avprice | +----------------+---------+----------+------------+---------+------------+-----------+ | Aberfledy 20yo | www. | 20.00 | 2016-05-01 | 30.00 | 2016-01-05 | 24.250000 | +----------------+---------+----------+------------+---------+------------+-----------+

So something like this? List the whiskies from the whisky table When user clicks on a name, pass the whisky_id in the link to next page. On next page, (see suggested sample attached) Show image, name and historic prices List all previous sales of that whisky (most recent first) Note: it occurred to me that the url_image should also be in the whisky table and not repeated in every sale record (just like the name).

You can't put the customer email as the "From" in the email. The from address must be from the same domain as the server. Put the customer email in a "Reply-To" header. This is all you need <?php $sql = "SELECT rEmail , eMail , c.fullname FROM realtors r LEFT JOIN customers c ON pow(clatitude-rlatitude, 2) + pow((clongitude-rlongitude)*cos(radians(rlatitude)), 2) < pow(100/110.25, 2) ORDER BY rid, id"; $realCust = $db->query($sql); foreach ($realCust as $email) { $to = $email['rEmail']; $subject = "Test"; $message = "Hello, I am {$email['fullname']}"; $headers = "Reply-To: {$email['eMail']}\r\n"; // mail($to, $subject, $message, $headers); echo "To : $to<br>$subject<br>$message<br>$headers<hr>"; } ?>

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"