PHP , SQL Show all whiskie dates and names as per name=:id an

[Barny74]

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

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

Edited January 21, 2017 by Barny74

[ginerjm]

Two things I noticed in your style.

 

You should use more meaningful names for your column names. 'name', 'date' and such are so vague. Even more so is your use of 'id' as the input value and then comparing it to a 'name' field in your where clause. Which is it - an id or a name that you are searching on?

 

PS - using a column name of 'date' might give you a problem in your query since date may be a reserved name. What is the date - a 'purchase date', a 'date added', a 'date distilled'? Plus using a name as the key field will be a real problem if indeed you have truly different whiskeys with the same name and you want to store data about them each and not make them entirely un-searchable. A simple sequential number assigned to each distinct label would be the way to go and then assign that id to every record pertaining to that label from that point on. Requires two tables of course, but that is how the db should be structured.

Edited January 21, 2017 by ginerjm

[Barny74]

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,

[ginerjm]

It's not a question of being 'user-friendly' but being more understandable to yourself and anyone else who reads your code, such as forum members trying to decipher it.

 

To elaborate on my suggestion about using an id as the key.

 

Create a 'header' table that defines each label and assigns an id number. You could use an auto-increment column in this to help keep it unique. Your process would add a record to this table and retrieve the newly assigned id. Then you would have a table with this id as the key and the rest of the columns being the attributes about this label. Be sure to design your columns to contain simple data types only - no combined values in a comma-delimited string because you have several of them. If you need to do something like that you create another table to keep multiple values in separate rows all having the same id. This would apply to purchase transactions perhaps where you would have a record of each one stored under the id all retrievable with a query on id.

 

When you want to find a whiskey you query the header table and let the user pick a name. You then use the corresponding id in your next query to retrieve al l the attributes from the 'details' table.

 

A brief description but an accurate representation of how a RDBMS works

[Barand]

You select records with same name but you then ORDER BY name - what is the point? If you want to see changes in price over time it would make more sense to ORDER BY date.

 

Your prices are all integers, so why use the floor() function?

 

My 0.02 worth.

[Barny74]

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.

[Barny74]

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