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 all I am getting echoed is a £. I seem to have lost the name and url being echoed and not getting the name url_img or Average price ( which i am trying to calculate. But my focus is to get the average price to show.
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;
}
?>
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