Jump to content

Echo Averge Price from a database


Barny74

Recommended Posts

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 by Barny74
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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";
Link to comment
Share on other sites

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 by Barny74
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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'];
Link to comment
Share on other sites

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


}

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.