Show single listing from Search - PHP SQL

Barny74 · January 21, 2017

Hi again,

Hopefully the last question as I am not 100% how to solve this one.

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.

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

Edited January 21, 2017 by Barny74

Barny74 · January 21, 2017

Do I group by name ?

Barand · January 21, 2017

GROUP BY is for aggregation (SUM, COUNT, MIN, MAX etc).

If you just want one of each, use DISTINCT and do not (ever) use SELECT * (except for testing). Always specify the columns you want to select.

So to select one of each name you would

SELECT DISTINCT name FROM test_db...

Note that you cannot select the lot_id with DISTINCT as that would give every record.

Barny74 · January 21, 2017

I tried using name instead of lot_id and it basically knock everything off , as I suspected you meant. Ok so I am stuck again.

Edited January 21, 2017 by Barny74

Barand · January 21, 2017

You need to clarify exactly what you want to search for. If you are searching for a type of whisky then you shouldn't searching test_db, which is a transaction table. You should be listing the separate table which I (and others) have already mentioned

whisky (whisky_id, whisky_name)

whisky_id should then be stored in test_db instead of the name.

If you are searching for a particular transaction then use test_db.

Barny74 · January 21, 2017

Ok, really confused now. Sorry. Must be frustrating for you. So do I create a new table called whiskies and then store the name and id in that. But keep all the info in test_db table?. Use the new table to do the initial search and then the test_db for the other two pages which drill down a bit more int teh details ?

Barand · January 21, 2017

Whatever you are wanting to do, there should be that new table.

test_db                       whisky
--------------                ----------
lot_id               +----    whisky_id
date                 |        whisky_name 
whisky_id        >---+
price
url_img
url_sale

It would now help if you described precisely what you are trying to achieve. The business objective, not the coded solution.

Barny74 · January 21, 2017

Ok, So I thought I may have sused it our , but not so sure now. new table "whiskies" just has name and lot_id. My objective is for people to see what they may get for there whisky at auction. I am not going to sell aything form it , its just a search and see website. Hence I get 10's of thousands of whisky sales and every auction could have 50 of the same whiskes sold , and its over many different auction houses. So basically there is one whisky name but may 50 sales that month.

$stmt = $conn->prepare("SELECT DISTINCT name , lot_id FROM whiskies 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";
	}

	?>

</html>

Edited January 21, 2017 by Barny74

Barny74 · January 21, 2017

Ok, So creating a new db now with whisky_id and also adding that to the test_td , will give it a go.

Barny74 · January 22, 2017

So as it stands here is what I have , it is all working apart from I am still getting all the results on the search page showing up , every entry for the whisky name I search for.

search.php

$stmt = $conn->prepare("SELECT DISTINCT name , whisky_id FROM whiskies 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['whisky_id']}' >{$row['name']}</a>";
					echo"<br>";
			echo "</div>";
	echo "</div>";
	} 

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

	?>

deatils1.php

<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	include ('connect.php');

	if (isset($_GET['id'])) {
    $sql = "SELECT name , price, url_img , lot_id, whisky_id , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db GROUP BY name) avcalc USING (name)
    WHERE whisky_id = :id";

    $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 "Average price based on all sales" ;
        echo"<br><br>";
        echo "<div class= 'price'>";
        echo " £";
        echo floor ($row ['avprice']);
        echo "</div>";
        echo "<br>";
        echo "<div class='img_border'>";
        echo "<img src='".$row ['url_img']."' /><br />";
        echo "</div>";
        echo "<br>";
        echo "<a href='records.php?id={$row['name']}' >Click here for more detialed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>

2 x Databases, (wont allow me to post images here)

test_db

Name , whisky_id , date , price , lot_id , url_sale , url_img

whiskies

name , whisky_id (which is the same id as the whisky_id in the test db one)

Edited January 22, 2017 by Barny74

Barny74 · January 22, 2017

Cant figure out what i am missing. Frustrating.

Barand · January 22, 2017

It would now help if you described precisely what you are trying to achieve. The business objective, not the coded solution.

I see you couldn't resist attempting to go straight to a coding "solution".

If you cannot describe what you are trying to achieve, how do you expect to program it?

My objective is for people to see what they may get for there whisky at auction.

So it seems your test_db table is actually a "lot" table, showing what is to be auctioned and when. It seems you also have need of an "auction" entity

auction (auction_id, auction_date, venue).

The date in the lot record would be replaced by the auction_id (as the date would belong to the auction).

So far you have only mentioned historical data, like the average price that they sold at. It now seems you need to know

which auctions are coming up
what will be available (lots) at each auction (is a lot always a single bottle?)

and, alternatively,

what whiskies as coming up for auction
at which auction/s will they be sold

From either list

click on a whisky for historical data (min, max, avg prices ?)
click on auction for details of the venue

All this is assumption and guesswork. Am I close?

Barny74 · January 22, 2017

Hi. I am purely looking at historical data and not future auctions. I do not auction anything myself. For example a number of companies hold whisky auctions and after the auction is finished i look at the websites and see which whiskies were sold and for how much. I then extract that data and put it in a CSV, So the purpose of my site is to purely do a search on a whisky and see the historical sale price. So it would be name , dates of sale and price sold for. Thats all i intend to offer.

Hope this makes sense

Thanks

Barny

Barand · January 22, 2017

So something like this?

List the whiskies from the whisky table
When user clicks on a name, pass the whisky_id in the link to next page.

On next page, (see suggested sample attached)

Show image, name and historic prices
List all previous sales of that whisky (most recent first)

Note: it occurred to me that the url_image should also be in the whisky table and not repeated in every sale record (just like the name).

Edited January 22, 2017 by Barand

Barny74 · January 22, 2017

Hi ,

Yes thats correct. But just set out a bit differently. Page1 - Search , Page 2 Results , Page 3 Deatails , Page 4 More details

The Details page shows name , img , avg price and a link to more details.

The more details page shows the info as you have rightly stated , Hi price , low price , avg and date. The reason I dont have this on page 3 is I may make this a subscription only page some day. I realise the dates all show 00000 , but I am working on that as its a CSV problem that popped up. I have been trying to attach images of the site but it wont let me..

Edited January 22, 2017 by Barny74

Barny74 · January 22, 2017

http://imgur.com/a/vDy0Q

I have uploaded some snap shots of how it currently is , but I do plan to make the results tabular once I can get the info correct first

Edited January 22, 2017 by Barny74

Barny74 · January 22, 2017

Here is my code as it stands

Index.php

<?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 DISTINCT name , whisky_id FROM whiskies 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['whisky_id']}' >{$row['name']}</a>";
					echo"<br>";
			echo "</div>";
	echo "</div>";
	} 

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

	?>

Details1.php

<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	include ('connect.php');

	if (isset($_GET['id'])) {
    $sql = "SELECT name , price, url_img , lot_id, whisky_id , avprice FROM test_db JOIN (SELECT name, AVG(price) as avprice FROM test_db GROUP BY name) avcalc USING (name)
    WHERE whisky_id = :id";

    $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 "Average price based on all sales" ;
        echo"<br><br>";
        echo "<div class= 'price'>";
        echo " £";
        echo floor ($row ['avprice']);
        echo "</div>";
        echo "<br>";
        echo "<div class='img_border'>";
        echo "<img src='".$row ['url_img']."' /><br />";
        echo "</div>";
        echo "<br>";
        echo "<a href='records.php?id={$row['name']}' >Click here for more detialed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>

records.php

<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	include ('connect.php');

	if (isset($_GET['id'])) {
    $sql = "SELECT date , name , price  FROM test_db WHERE name = :id ORDER BY date DESC";
   
    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    while( $row = $stmt->fetch() ) {
    echo "<div class='records'>";
        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 · January 22, 2017

I really appreciate all the help I am getting here and would be very lost without it.

Barand · January 22, 2017

When searching the whisky table for the entered name you don't need the DISTINCT. All names in that table should be unique.

The one part that may give you problems is the min and max prices and the dates they occurred. This will find those for you

SELECT 
  w.whisky_name
  , w.url_img
  , lowprice
  , lowdate
  , hiprice
  , hidate
  , avprice
FROM
    whisky w 
    
    INNER JOIN
    (
    SELECT whisky_id
      , AVG(price) as avprice
    FROM test_db
    GROUP BY whisky_id
    ) ap USING (whisky_id)
    
    INNER JOIN
    (
    SELECT whisky_id
    , t.date as lowdate
    , t.price as lowprice
    FROM test_db t 
        JOIN
        (
        SELECT whisky_id
        , MIN(price) as price
        FROM test_db
        GROUP BY whisky_id
        ) min_p USING (whisky_id, price)
    ) min_d USING (whisky_id)
    
    INNER JOIN
    (
    SELECT whisky_id
    , t.date as hidate
    , t.price as hiprice
    FROM test_db t 
        JOIN
        (
        SELECT whisky_id
        , MAX(price) as price
        FROM test_db
        GROUP BY whisky_id
        ) max_p USING (whisky_id, price)
    ) max_d USING (whisky_id)
    
WHERE w.whisky_id = 1;

results

+----------------+---------+----------+------------+---------+------------+-----------+
| whisky_name    | url_img | lowprice | lowdate    | hiprice | hidate     | avprice   |
+----------------+---------+----------+------------+---------+------------+-----------+
| Aberfledy 20yo | www.    |    20.00 | 2016-05-01 |   30.00 | 2016-01-05 | 24.250000 |
+----------------+---------+----------+------------+---------+------------+-----------+

Barny74 · January 22, 2017

Thank you , a little confued. So do the names in the whiskies table need to be unique then ? , as at the moment I have all the names and whiskie_id in there , basically the save CSV from the test_db , but without the other info.

Barand · January 22, 2017

"Aberfeldy 20yo" may appear in test_db many dozens of times but will only be stored once in the whisky table, with its id. So you'd have

test_db
+--------+-----------+------------+-------+----------+
| lot_id | whisky_id | date       | price | url_sale |
+--------+-----------+------------+-------+----------+
|  12346 |         1 | 2016-05-01 | 20.00 | www.     |
|  13325 |         1 | 2016-07-01 | 25.00 | www.     |
|  17389 |         2 | 2016-07-01 | 59.00 | www.     |
|  17947 |         1 | 2016-01-05 | 30.00 | www.     |
|  18256 |         2 | 2016-06-01 | 58.00 | www.     |
|  19308 |         1 | 2016-01-01 | 22.00 | www.     |
|  20156 |         2 | 2016-09-01 | 48.00 | www.     |
|  26547 |         2 | 2016-11-01 | 36.00 | www.     |
+--------+-----------+------------+-------+----------+

whisky
+-----------+----------------+---------+
| whisky_id | whisky_name    | url_img |
+-----------+----------------+---------+
|         1 | Aberfledy 20yo | www.    |
|         2 | Grouse 5yo     | www.    |
+-----------+----------------+---------+

Barny74 · January 22, 2017

Ok I see what you are getting at. I have a CSV for my test_db with all whisky sales in it. What I did was just add a colomn with whisky_id and started at number one throught to 50k ish , then opened another CSV and copied in the name and id , but I did not give each exact whisky the same number. Do I have to do this in my CSV manually or is there a way to do this through Myphmadmin. I hope I am being clear. I have added a link to an image to give a better idea.

http://imgur.com/a/bex86

Barand · January 22, 2017

The whole point of a database is that each item of information, like a name, is stored once. The only things that appear more than once are the ids which are used to link the tables.

1 ) start with the test_db you had originally and add a new column (type INT) called "whisky_id".

2 ) Create the table "whisky".

CREATE TABLE `whisky` (
  `whisky_id` int(11) NOT NULL AUTO_INCREMENT,
  `whisky_name` varchar(40) DEFAULT NULL,
  `url_img` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`whisky_id`)
) ENGINE=InnoDB;

3 ) populate the whisky table with the unique name from test_db. The whisky ids will be auto generated.

INSERT INTO whisky (whisky_name)
    SELECT DISTINCT name
    FROM test_db
    ORDER BY name;

4 ) Now you need to put those ids from the whisky table into the new column in test_db

UPDATE test_db t
	JOIN whisky w ON t.name=w.whisky_name
SET t.whisky_id = w.whisky_id;

5 ) Similarly (but in opposite direction) transfer url_img from test_db to the matching whisky table record.

UPDATE test_db t
	JOIN whisky w USING (whisky_id)
SET w.url_img = t.url_img;

Job done. Now you can drop the redundant name and url_img columns from the test_db table.

Barny74 · January 22, 2017

Thanks and again really appreciate it,

One step 3 , I am getting an error , as per screen shot

http://imgur.com/a/w8Im9

Not sure what to do with this,

Barand · January 22, 2017

works for me

mysql> INSERT INTO whisky (whisky_name)
    ->     SELECT DISTINCT name
    ->     FROM test_db
    ->     ORDER BY name;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

Sign In

Show single listing from Search - PHP SQL

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information