Show single listing from Search - PHP SQL

Question

Hopefully the last question as I am not 100% sure how to solve this one. I did see a similar question , but it does not really reflect my question and the other question is quite difficult to follow , so please see this as a original question and not a duplicate..

So on my website someone carried out a search from a search bar using the 'POST' method , teh search results show all whiskies in the databse. I have a number of whiskies with the same name but with different dates and prices. I would like it just to show one of each type that was searched for rather than all of them. I have attahced a clip of the databse. Really appreciate the help

Thanks

Index.php

 </head>

<?php 
$page='index';
include('header.php');
include('navbar.php');
?>


<script type="text/javascript">
    function active(){
        var search_bar= document.getElementById('search_bar');
    if(search_bar.value == 'Search for your whisky here'){
    search_bar.value=''
    search_bar.placeholder= 'Search for your whisky here'
    }   
}

    function inactive(){
        var search_bar= document.getElementById('search_bar');
    if(search_bar.value == ''){
    search_bar.value='Search for your whisky here'
    search_bar.placeholder= ''
    }   
}

</script>
<body>
    <div class="third_bar"> 
            <div class="background_image"> 
            </div> 
                <div class="form"><form action= "search.php" method="post"> 
                <input type="text" name="search" id="search_bar" placeholder="" value="Search for your whisky here" max length="30" autocomplete="off" onMouseDown="active();" onBlur="inactive();"/><input type="submit" id="search_button" value="Go!"/> 
                </form>  
    </div> </div>
</body>
    </div> 

    <?php include ('footer.php');
    ?>

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='details1.php?id={$row['lot_id']}' >{$row['name']}</a>";
                    echo"<br>";
            echo "</div>";
    echo "</div>";
    } 

    } else {
          echo " Sorry no records were found";
    }

    ?>

</htm

Possible duplicate of [MySQL - SELECT all columns WHERE one column is DISTINCT](http://stackoverflow.com/questions/11641270/mysql-select-all-columns-where-one-column-is-distinct) — random_user_name, Jan 21 '17 at 22:15
Please refer to the linked question and answer, which addresses your problem directly. Short answer: You need to use `GROUP BY name` in your query to make ONLY the "unique names" appear. — random_user_name, Jan 21 '17 at 22:16
I am not entirely sure where to add the GROUP BY name. I have tried it through php admin and cant get it to work. If I add in GROUP BY name after ORDE BY , it just throws up zero results — Jason, Jan 21 '17 at 22:29
Did you read the link I provided above? It gives you the pattern: SELECT [fields] FROM [tables] [WHERE] [GROUP BY] [ORDER BY]. So, in your case, you want `SELECT * FROM test_db WHERE name LIKE :name GROUP BY name ORDER BY name`. Note that depending on your mysql configuration, this may NOT work properly, because sometimes the configuration requires all fields selected to be present in the GROUP BY. If that query doesn't work, then you can get started with `SELECT name FROM test_db WHERE name LIKE :name GROUP BY name ORDER BY name`. — random_user_name, Jan 21 '17 at 22:32
I think the problem is I am using lot_it , so this is throwing up issued now. — Jason, Jan 21 '17 at 22:59

Show single listing from Search - PHP SQL

0 Answers0