Barny74

I can pull down info as per the image - http://imgur.com/a/4vskU using details1.php if (isset($_GET['id'])) { $sql = "SELECT whisky_id , price , avprice FROM test_db JOIN (SELECT whisky_id, AVG(price) as avprice FROM test_db GROUP BY whisky_id) avcalc USING (whisky_id) WHERE whisky_id = :id"; $sql = "SELECT * FROM whisky WHERE whisky_id = :id";

Hi , thank yes I have done that thanks. Now we have two tables I am struggling to pull the info down as only ever really worked with one in the past.

Ok , thats run now and thank you so much. I have been runnig though the code to update it to the new names etc. I get the single listings on the main search which is great. When I click on the name it does not pull anything down from teh DB , I am a wee bit confused on which DB I am selecting from now. Code as below. Search.php , which I think is ok now. if (isset ($_POST['search'])) { //the 'search' refers to the 'search' name=search on the index page and makes does something when the search is pushed. $search = $_POST['search']; $search = "%" . $search . "%"; // MySQL wildcard % either side of search to get partially matching results // No wildcard if you want results to match fully } else { header ('location: index.php'); } $stmt = $conn->prepare("SELECT whisky_name , whisky_id FROM whisky WHERE whisky_name LIKE :name ORDER BY whisky_name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['whisky_id']}' >{$row['whisky_name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> details.php <?php $page='details'; include('header.php'); include ('navbar.php'); include ('connect.php'); if (isset($_GET['id'])) { $sql = "SELECT price, url_img , lot_id, whisky_id , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db GROUP BY name) avcalc USING (name) WHERE whisky_id = :id"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo "<div class='details'>"; echo "<br>"; echo $row['name']; echo "<br><br>"; echo "Average price based on all sales" ; echo"<br><br>"; echo "<div class= 'price'>"; echo " £"; echo floor ($row ['avprice']); echo "</div>"; echo "<br>"; echo "<div class='img_border'>"; echo "<img src='".$row ['url_img']."' /><br />"; echo "</div>"; echo "<br>"; echo "<a href='records.php?id={$row['whisky_id']}' >Click here for more detialed results</a>"; echo "<br>"; echo "</div>"; } ?> records.php <?php $page='details'; include('header.php'); include ('navbar.php'); include ('connect.php'); if (isset($_GET['id'])) { $sql = "SELECT whisky_id , date , name , price FROM test_db WHERE whisky_id = :id ORDER BY date DESC"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); while( $row = $stmt->fetch() ) { echo "<div class='records'>"; echo "<br>"; echo $row['name']; echo "<br><br>"; echo $row ['date']; echo"<br><br>"; echo " £"; echo floor ($row ['price']); echo "<br>"; echo "<br>"; echo "<br>"; echo "</div>"; } } ?> databases. http://imgur.com/a/iwf1p Thanks again

Ok, its into 30 mins now , so will hang on I guess.

With 50k+ recrods in my test_bd would the below take a while , seems to have said "loading" for a while now. I am guessing it is still working on it but just wanted an opinion on it. In case it has stalled , but not sure. I can clock on the "whisky" table , but when I click on the "test-db" is just has the yellow loading box in the middle. UPDATE test_db t JOIN whisky w ON t.name=w.whisky_name SET t.whisky_id = w.whisky_id;

Ha ha , good spot, going crossed eyes here. It worked thanks. onto the next step

Thanks and again really appreciate it, One step 3 , I am getting an error , as per screen shot http://imgur.com/a/w8Im9 Not sure what to do with this,

Ok I see what you are getting at. I have a CSV for my test_db with all whisky sales in it. What I did was just add a colomn with whisky_id and started at number one throught to 50k ish , then opened another CSV and copied in the name and id , but I did not give each exact whisky the same number. Do I have to do this in my CSV manually or is there a way to do this through Myphmadmin. I hope I am being clear. I have added a link to an image to give a better idea. http://imgur.com/a/bex86

Thank you , a little confued. So do the names in the whiskies table need to be unique then ? , as at the moment I have all the names and whiskie_id in there , basically the save CSV from the test_db , but without the other info.

I really appreciate all the help I am getting here and would be very lost without it.

Here is my code as it stands Index.php <?php $page='index'; include('header.php'); include('navbar.php'); ?> <script type="text/javascript"> function active(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == 'Search for your whisky here'){ search_bar.value='' search_bar.placeholder= 'Search for your whisky here' } } function inactive(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == ''){ search_bar.value='Search for your whisky here' search_bar.placeholder= '' } } </script> <body> <div class="third_bar"> <div class="background_image"> </div> <div class="form"><form action= "search.php" method="post"> <input type="text" name="search" id="search_bar" placeholder="" value="Search for your whisky here" max length="30" autocomplete="off" onMouseDown="active();" onBlur="inactive();"/><input type="submit" id="search_button" value="Go!"/> </form> </div> </div> </body> </div> <?php include ('footer.php'); ?> Search.php <?php $page='search'; include('header.php'); include ('navbar.php'); echo "<br>"; include ('connect.php'); if (isset ($_POST['search'])) { //the 'search' refers to the 'search' name=search on the index page and makes does something when the search is pushed. $search = $_POST['search']; $search = "%" . $search . "%"; // MySQL wildcard % either side of search to get partially matching results // No wildcard if you want results to match fully } else { header ('location: index.php'); } $stmt = $conn->prepare("SELECT DISTINCT name , whisky_id FROM whiskies WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['whisky_id']}' >{$row['name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> Details1.php <?php $page='details'; include('header.php'); include ('navbar.php'); include ('connect.php'); if (isset($_GET['id'])) { $sql = "SELECT name , price, url_img , lot_id, whisky_id , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db GROUP BY name) avcalc USING (name) WHERE whisky_id = :id"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo "<div class='details'>"; echo "<br>"; echo $row['name']; echo "<br><br>"; echo "Average price based on all sales" ; echo"<br><br>"; echo "<div class= 'price'>"; echo " £"; echo floor ($row ['avprice']); echo "</div>"; echo "<br>"; echo "<div class='img_border'>"; echo "<img src='".$row ['url_img']."' /><br />"; echo "</div>"; echo "<br>"; echo "<a href='records.php?id={$row['name']}' >Click here for more detialed results</a>"; echo "<br>"; echo "</div>"; } ?> records.php <?php $page='details'; include('header.php'); include ('navbar.php'); include ('connect.php'); if (isset($_GET['id'])) { $sql = "SELECT date , name , price FROM test_db WHERE name = :id ORDER BY date DESC"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); while( $row = $stmt->fetch() ) { echo "<div class='records'>"; echo "<br>"; echo $row['name']; echo "<br><br>"; echo $row ['date']; echo"<br><br>"; echo " £"; echo floor ($row ['price']); echo "<br>"; echo "<br>"; echo "<br>"; echo "</div>"; } } ?>

http://imgur.com/a/vDy0Q I have uploaded some snap shots of how it currently is , but I do plan to make the results tabular once I can get the info correct first

Hi , Yes thats correct. But just set out a bit differently. Page1 - Search , Page 2 Results , Page 3 Deatails , Page 4 More details The Details page shows name , img , avg price and a link to more details. The more details page shows the info as you have rightly stated , Hi price , low price , avg and date. The reason I dont have this on page 3 is I may make this a subscription only page some day. I realise the dates all show 00000 , but I am working on that as its a CSV problem that popped up. I have been trying to attach images of the site but it wont let me..

Hi. I am purely looking at historical data and not future auctions. I do not auction anything myself. For example a number of companies hold whisky auctions and after the auction is finished i look at the websites and see which whiskies were sold and for how much. I then extract that data and put it in a CSV, So the purpose of my site is to purely do a search on a whisky and see the historical sale price. So it would be name , dates of sale and price sold for. Thats all i intend to offer. Hope this makes sense Thanks Barny

Cant figure out what i am missing. Frustrating.

Thanks gingerjm. I habv another question running with regards to the search results page and trying show only one name for all the same whiskies. I have created two tables within the database now. Still learning this steep curve. Everyome has been so helpful and i am learning so much. Holefully i will get my next question resloved. Thanks

So as it stands here is what I have , it is all working apart from I am still getting all the results on the search page showing up , every entry for the whisky name I search for. search.php $stmt = $conn->prepare("SELECT DISTINCT name , whisky_id FROM whiskies WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['whisky_id']}' >{$row['name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> deatils1.php <?php $page='details'; include('header.php'); include ('navbar.php'); include ('connect.php'); if (isset($_GET['id'])) { $sql = "SELECT name , price, url_img , lot_id, whisky_id , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db GROUP BY name) avcalc USING (name) WHERE whisky_id = :id"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo "<div class='details'>"; echo "<br>"; echo $row['name']; echo "<br><br>"; echo "Average price based on all sales" ; echo"<br><br>"; echo "<div class= 'price'>"; echo " £"; echo floor ($row ['avprice']); echo "</div>"; echo "<br>"; echo "<div class='img_border'>"; echo "<img src='".$row ['url_img']."' /><br />"; echo "</div>"; echo "<br>"; echo "<a href='records.php?id={$row['name']}' >Click here for more detialed results</a>"; echo "<br>"; echo "</div>"; } ?> 2 x Databases, (wont allow me to post images here) test_db Name , whisky_id , date , price , lot_id , url_sale , url_img whiskies name , whisky_id (which is the same id as the whisky_id in the test db one)

Ok, So creating a new db now with whisky_id and also adding that to the test_td , will give it a go.

Ok, So I thought I may have sused it our , but not so sure now. new table "whiskies" just has name and lot_id. My objective is for people to see what they may get for there whisky at auction. I am not going to sell aything form it , its just a search and see website. Hence I get 10's of thousands of whisky sales and every auction could have 50 of the same whiskes sold , and its over many different auction houses. So basically there is one whisky name but may 50 sales that month. $stmt = $conn->prepare("SELECT DISTINCT name , lot_id FROM whiskies WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['lot_id']}' >{$row['name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> </html>

Ok, really confused now. Sorry. Must be frustrating for you. So do I create a new table called whiskies and then store the name and id in that. But keep all the info in test_db table?. Use the new table to do the initial search and then the test_db for the other two pages which drill down a bit more int teh details ?

I tried using name instead of lot_id and it basically knock everything off , as I suspected you meant. Ok so I am stuck again.

Do I group by name ?

Hi again, Hopefully the last question as I am not 100% how to solve this one. So on my website someone carried out a search from a search bar using the 'POST' method , teh search results show all whiskies in the databse. I have a number of whiskies with the same name but with different dates and prices. I would like it just to show one of each type that was searched for rather than all of them. I have attahced a clip of the databse. Thanks Index.php </head> <?php $page='index'; include('header.php'); include('navbar.php'); ?> <script type="text/javascript"> function active(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == 'Search for your whisky here'){ search_bar.value='' search_bar.placeholder= 'Search for your whisky here' } } function inactive(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == ''){ search_bar.value='Search for your whisky here' search_bar.placeholder= '' } } </script> <body> <div class="third_bar"> <div class="background_image"> </div> <div class="form"><form action= "search.php" method="post"> <input type="text" name="search" id="search_bar" placeholder="" value="Search for your whisky here" max length="30" autocomplete="off" onMouseDown="active();" onBlur="inactive();"/><input type="submit" id="search_button" value="Go!"/> </form> </div> </div> </body> </div> <?php include ('footer.php'); ?> Search.php <?php $page='search'; include('header.php'); include ('navbar.php'); echo "<br>"; include ('connect.php'); if (isset ($_POST['search'])) { //the 'search' refers to the 'search' name=search on the index page and makes does something when the search is pushed. $search = $_POST['search']; $search = "%" . $search . "%"; // MySQL wildcard % either side of search to get partially matching results // No wildcard if you want results to match fully } else { header ('location: index.php'); } $stmt = $conn->prepare("SELECT * FROM test_db WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['lot_id']}' >{$row['name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> </htm

Hi. I actually did change it to order by date after I got the code correct. But thanks anyway. I will owrk on the pricing tonight. Appreciate that. I have just posted (hopefully) the final SQL Question.

Thank you for the response and appreciarte it. I am very new to databases and learning as I go along. I will take your comments onboard and see if I can do anything to make it moreuser firendly for the future, Thanks again,