Barny74

Its ok I sorted it. while( $row = $stmt->fetch() ) { echo "<div class='details'>"; 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>"; }

I am trying to show all the whiskies from my database with the dates alongside it , from the URL. I have several whiskies with the same names and want to show the price changes over the years. My current code is as below , but at the moment it is just showing one date , name and price. I am happy to show the one name at the top, but I would like to show all the prices and dates. if (isset($_GET['id'])) { $sql = "SELECT date , name , price FROM test_db WHERE name = :id ORDER BY name ASC"; $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 $row ['date']; echo"<br><br>"; echo " £"; echo floor ($row ['price']); echo "<br>"; echo "<br>"; echo "<br>"; echo "</div>";

Brilliant thanks. I will have a good go at it later. It seems to be comimg together nicely. Any advice on a good learning resource for SQL for absolute numpties.? I am picking it up slowly but surely , but somethimgs still totally bambusel me. Thanks

Ah ok. Thanks for that. I had a quick go before i had to go out and it produced some good reaults. Will hace a proper look when i am back again. So does this code look at all the results with the same name that i clicked on , on my search.php page after initiating a search.?. In fact i may just put it up on a live site incase i have any orher problems. And yes i do have myphpadmin and i am testing with that also. Thanks again for the help

Thanks , still a bit puzzled. Here is my code as it stands , And I am only getting a £ echoed out. if (isset($_GET['id'])) { $sql = "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"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo $row['name']; echo '<br>'; echo '£'; echo $row['price']; echo '<br>'; echo "<img src='".$row ['url_img']."' /><br />"; echo $row['avprice'];

Thanks Jacques , that seemed to sneak in there somehow. Will give it a go. It gets quite confusing.

Do you this may work. Cant actually get to my PC until later so trying to figure it out without being able to try it. $sql = "SELECT name , t1.avgprice , url_img , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db t1 GROUP BY name) avcalc USING (name) WHERE lot_id = :id";

This is a great forum by the way.

Hi , Ok thanks for that. I am a tad confused. Here is my new code , and at the moment all I get when I click on a result it a £ being echoed. I was not sure what you meant by the below. All the whiskes are stored in test_db. I seem to have lost the name and the url image from the results. if (isset($_GET['id'])) { $sql = "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"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo $row['name']; echo '<br>'; echo '£'; echo $row['price']; echo '<br>'; echo "<img src='".$row ['url_img']."' /><br />"; echo $row['avg']; } ?> Really appreciate the help, Thanks

Sample databse database sample name date price url_sale url_img lot_id Aberfledy 20yo 2016/05/01 20 www. www. 12346 Aberfledy 20yo 2016/07/01 25 www. www. 13325 Aberfledy 20yo 2016/01/05 30 www. www. 17947 Aberfledy 20yo 2016/01/01 22 www. www. 19308 Grouse 5yo 2016/07/01 59 www. www. 17389 Grouse 5yo 2016/09/01 48 www. www. 20156 Grouse 5yo 2016/11/01 36 www. www. 26547 Grouse 5yo 2016/06/01 58 www. www. 18256

Hi I have a databse of whiskies with sale prices over a period of time. In my database I have Name,Price Url and Id. I have quite a few whiskies with the same name, but sale prices are different. Currently when someone does a search it takes them to the search results page that shows the product they have searched for. What I am trying to do is when someone clicks the link to see the full details, I would like to be able to show the average price for all the whiskies that come under that name, but I am struggling as you can probably see. Its the ressult.php I am having the problem with as I am getting an error that I have a undefined AVG variable. I thought I had covered it. Notice: Undefined variable: avg in C:\wamp\www\search1\details1.php on line 28 Search.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 "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> 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 "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> </html> Details.php if (isset($_GET['id'])) { $sql = "SELECT name , AVG(price) , url_img FROM test_db WHERE lot_id = :id"; $stmt = $conn->prepare($sql); $stmt->execute( [ 'id' => $_GET['id'] ] ); $row = $stmt->fetch(); echo $row['name']; echo '<br>'; echo '£'; echo $row['price']; echo '<br>'; echo "<img src='".$row ['url_img']."' /><br />"; echo $avg; } ?>

Ha ha , well I may just do that once I get this site up and running. Thanka again

Outstanding , thank you so much for your help. I really appreciate it. My next step is going to be the difficult one. I may post again soon. I have several whiskies with the same name in my database and need to work out how to find and echo an averge price , work out what the last date one was sold and also what that price was. I have the databse filled with all sale prices and date etc. So may be back soon, Thanks again and appreciate your help tonight. Barny

Ok, Starting to make a bit more sence , So my code stands as belwo , but getting a error now, Fatal error: Call to undefined method PDO::execute() in C:\wamp\www\search1\details.php on line 20 Line 20 is the $conn->execute( [ 'id' => $_GET['id'] ] ); <?php $page='details'; include('header.php'); include ('navbar.php'); include ('connect.php'); if (isset($_GET['id'])) { $sql = "SELECT name FROM test_db WHERE lot_id = :id"; $conn->prepare($sql); $conn->execute( [ 'id' => $_GET['id'] ] ); echo ['name']; }

I am a tad confused. So would the code form my search reulsts need changing $stmt = $conn->prepare("SELECT * FROM test_db WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching or would I need to add this to the details.php. I know this must be frustrating for you but I really appreciate the help. Its a steep learning curve and have basically gone from knowing nothing about web design to this in about 3 weeks.

Hi , Thanks for this and really appreciate the time helping me. I have had a go with the code and get the below error on teh setails.php Parse error: syntax error, unexpected '$_GET' (T_VARIABLE) in C:\wamp\www\search1\details.php on line 17 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='details.php?id={$row['lot_id']}' >{$row['name']}</a>;"; 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'); (in $_GET['lot_id'] ); echo $row['name']; ?> </html>

Thanks, So here is my code as I have worked out how to get a link to a details.php I am just not sure what I need to do on my details.php page. Really appreciate the help. 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=\"details.php\">" . $row['name'] . "</a>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> details.php <?php $page='details'; include('header.php'); include ('navbar.php'); echo "<br>"; include ('connect.php'); ?>

Sorry, How can I edit my original question ? , or delete it and start again, Thanks

Hi. I have coded a simple search bar which shows results based on my databse. Its based on Whiskie. Nice... So this is part one of a two part question, Part two to come later. Basically what I want to do is have the search results show the name , then have the name be a link to more details. The search results currently show a box with the name , last price paid and the date. Really appreciate the help. Here is my code <?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 $row['name']; echo "</div>"; echo "<div class= 'results_body'>"; echo "<div class= 'result_lowprice'>"; echo "Price Paid: £"; echo $row['price']; echo "</div>"; echo "<div class= 'last_date'>"; echo "Last date sold: "; echo $row['date']; echo "</div>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> </div> </html>

Anyone know how to get my database to recognise the date as DDMMYYYY , cant upload date as DDMMYYYY as it wants everything as YYYYMMDD.

Hi. Thank you so much dor this answer. I have had so many convoluted answers amd this was the simplest and it worked. Thanks for being so forward and down to earth. Very much appreciated

Hi. Thanks. This is my problem , and i have not had time to look at the previous suggestion due to being at work. Its seems to be one peice of code that produces all the results so if i use tables all i will get is a stack of tables. I can use anything , i just need to find out how to get the results side by side. Baring in mind its just one peice of php code that produces all results.

So in the code at the momemt i have a <div> called "results" , i use that to style the boxes aroumd the actual results. If i add float amd width to that ot should actually float them horizontally. I was umder the impression that i would jave to change some actual PHP code. I am away from my PC for a few hours now but will check it out later. I may have been looking at this to hard and not seen that its a CSS code ratjer than CSS. Fingers crossed and thanks for the reply.

Hi , New to PHP and really appreciate any help I can get. I have written dome code for a search bar , as below. I have styles the results into boxes , but cant figure out how to get the results side by side rather than one above the other. I would like to have maybe 3 or 4 in a horizontal row , then the same below , until the search ends. Really hope someone can help, Thanks Search.php if (isset ($_POST['search'])) { $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 } $stmt = $conn->prepare("SELECT * FROM whisky_results 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 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<br>"; echo "<div class=\"results\">"; echo "<div class=\"result_name\">"; echo "<b>Whisky Name:</b><br>"; echo $row['name']; echo "</div>"; //echo "<div class= \"result_description\">"; //echo "Whisky Description: "; //echo $row['description']; //echo "</div>"; echo "<div class= \"result_highprice\">"; echo "Highest Price Paid: £"; echo $row['highest_price']; echo "</div>"; echo "<div class= \"result_lowprice\">"; echo "Lowest Price Paid: £"; echo $row['lowest_price']; echo "</div>"; echo "<div class= \"average_price\">"; echo "Average Price Paid: £"; echo $row['average_price']; echo "</div>"; echo "<div class= \"last_price\">"; echo "Last Price Paid: £"; echo $row['last_price']; echo "</div>"; echo "<div class= \"last_date\">"; echo "Last date sold: "; echo $row['last_date']; echo "</div>"; echo "</div>"; echo "<br>"; } } else { echo " Sorry no records were found"; }