Echo Averge Price from a database

[Barny74]

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;

}

	?>

Edited January 20, 2017 by Barny74

[Barny74]

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

[ginerjm]

You probably mean to say $row['avg'].

 

But you also may have to do this: AVG(price) as avg

[Barand]

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";

[Barny74]

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.

 

 

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.

	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

Edited January 20, 2017 by Barny74

[Barny74]

This is a great forum by the way.

[Jacques1]

You keep trying to select columns that don't exist. There is no “avg” in the result set, just “AVG(price)”. If you want “avg”, you need to create an alias:

AVG(price) AS avg

[Barny74]

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";

[Barny74]

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

[Barand]

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']

[Barny74]

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'];

[Barand]

You don't have a $row['price']. Your result columns are the same as those selected

 

SELECT name , AVG(price) , url_img , avprice

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']}";


}

[Jacques1]

By the way: When you have so much trouble understanding the result set, print it. Do you have phpmyadmin or some other administration tool? Execute the query with an example ID and look at the result.

[Barny74]

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

[Barand]

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

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 |
+------------+

Edited January 21, 2017 by Barand

[Barny74]

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