Show single listing from Search - PHP SQL

[mac_gyver]

i would look at row 55982 in your test_db table and see what length the name is (or just run a query to get the maximum length of the data in that column) and make the length of the whisky_name column longer than you expect the names to ever be.

[Barand]

Better eyes than me. I could barely make out the last line of that screenshot.

[Barny74]

Ha ha , good spot, going crossed eyes here. It worked thanks. onto the next step

[Barny74]

With 50k+ recrods in my test_bd would the below take a while , seems to have said "loading" for a while now. I am guessing it is still working on it but just wanted an opinion on it. In case it has stalled , but not sure. I can clock on the "whisky" table , but when I click on the "test-db" is just has the yellow loading box in the middle.

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

[Barand]

It's a one-off operation so slowness isn't a real issue.

[Barny74]

Ok, its into 30 mins now , so will hang on I guess.

[Barny74]

Ok , thats run now and thank you so much. I have been runnig though the code to update it to the new names etc. I get the single listings on the main search which is great. When I click on the name it does not pull anything down from teh DB , I am a wee bit confused on which DB I am selecting from now. Code as below.

 

Search.php , which I think is ok now.

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 whisky_name , whisky_id FROM whisky WHERE whisky_name LIKE :name ORDER BY whisky_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['whisky_name']}</a>";
					echo"<br>";
			echo "</div>";
	echo "</div>";
	} 

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

	?>

details.php

<?php

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

	if (isset($_GET['id'])) {
    $sql = "SELECT 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['whisky_id']}' >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 whisky_id , date , name , price  FROM test_db WHERE whisky_id = :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>";
}
}
	?>

databases.

 

http://imgur.com/a/iwf1p

 

Thanks again

[Barand]

If you haven't already, add an index to test_db on the whisky_id column.

CREATE INDEX `idx_test_db_whisky_id`  ON `test_db` (whisky_id);

[Barny74]

Hi , thank yes I have done that thanks. Now we have two tables I am struggling to pull the info down as only ever really worked with one in the past.

Edited January 22, 2017 by Barny74

[Barny74]

I can pull down info as per the image   - http://imgur.com/a/4vskU

 

using

details1.php

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

    $sql = "SELECT * FROM whisky WHERE whisky_id = :id";

Edited January 22, 2017 by Barny74

[Barand]

In records.php, for example, if you want the whisky name then you need to join the test_db table to the whisky table

SELECT whisky_id 
, date 
, whisky_name as name
, price  
FROM test_db
 INNER JOIN whisky USING (whisky_id) -- required to get the name
WHERE t.whisky_id = :id 
ORDER BY date DESC

[Barand]

I can pull down info as per the image   - http://imgur.com/a/4vskU

Is the average price the only thing you want to show as a result of the query on that page?

[Barny74]

I was hoping to show the avg price , last price / date sold. Then the rest on the next page , which at some point will be restricted to memebers.only

[Barand]

Then

SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id
    , AVG(price) as avprice 
    FROM test_db 
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
WHERE w.whisky_id = 1
ORDER BY date DESC 
LIMIT 1;

[Barny74]

Ok thank you , I have update the code but now it just goes back to a page with no database content, I will keep the dates etc for later andif I can just get the name ,avg price that would be great , and I keep the code in for the dates last sold and price last sold so I can add that in later. really sorry about this and really appreciat the help

 

http://imgur.com/a/DPSr4

 

details1.php

<?php

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

	if (isset($_GET['id'])) {
    $sql = "SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id,
    , AVG(price) as avprice 
    FROM test_db 
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
WHERE w.whisky_id = 1
ORDER BY date DESC 
LIMIT 1";


    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    $row = $stmt->fetch();
    echo "<div class='details'>";
        echo "<br>";
        echo $row['whisky_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['whisky_id']}' >Click here for more detailed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>

Edited January 22, 2017 by Barny74

[Barand]

You have left the id hard coded instead of " = :id ". This version below may also be more efficient

SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN 
	whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id
    , AVG(price) as avprice 
    FROM test_db 
    WHERE whisky_id = :id
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
ORDER BY date DESC 
LIMIT 1;

[Barny74]

Ah ok, so much to take in I did not relaise that. Thanks. So do I need to also connect to the whisky table to pull the img down, also have an error on whisky_id , as per pic,

 

http://imgur.com/a/grgaZ

if (isset($_GET['id'])) {
    $sql = "SELECT w.whisky_name
, price
, date
, avprice 
FROM test_db t
JOIN 
    whisky w USING (whisky_id)
JOIN (
    SELECT whisky_id
    , AVG(price) as avprice 
    FROM test_db 
    WHERE whisky_id = :id
    GROUP BY whisky_id
    ) avcalc USING (whisky_id)
ORDER BY date DESC 
LIMIT 1";

    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    $row = $stmt->fetch();
    echo "<div class='details'>";
        echo "<br>";
        echo $row['whisky_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['whisky_id']}' >Click here for more detailed results</a>";
        echo "<br>";
    echo "</div>";
}
	?>

 

[Barand]

url_img and whsky_id are not in the fields selected in the query.

[Barny74]

AH ok. I was a bit confused as i was querying test_db and there is not url_img or whsky_id in this db. But I have added them and thats all good now.

 

So once agian thanks so much and I really appreciate you taking the time to assit me with this.

 

I will work on the final page now and hopefully work it out now I have a good base to work from, but If it is ok I will ask if I have any problems

 

Thnanks again.

[Barny74]

Ha ha , back again...I know I need to add a WHILE loop to get this going , but how would I get all results showing for the specific whisky. Just not sure on the WHILE loop. Only one result showing at the moment.

 

http://imgur.com/a/qilCS

if (isset($_GET['id'])) {

        $sql = "SELECT w.whisky_name
        , price
        , date
        , avprice 
        ,url_img 
        ,whisky_id
        FROM test_db t
        JOIN 
            whisky w USING (whisky_id)
        JOIN (
            SELECT whisky_id
            , AVG(price) as avprice 
            FROM test_db 
            WHERE whisky_id = :id
            GROUP BY whisky_id
            ) avcalc USING (whisky_id)
        ORDER BY date DESC 
        LIMIT 1";
           
    $stmt = $conn->prepare($sql);
    $stmt->execute( [ 'id' => $_GET['id'] ] );
    while( $row = $stmt->fetch() ) {
    echo "<div class='records'>";
        echo "<br>";
        echo $row['whisky_name'];
        echo "<br><br>";
        echo $row ['date'];
        echo"<br><br>";
        echo " £";
        echo floor ($row ['price']);
        echo "<br>";
        echo "<br>";
        echo "<br>";
    echo "</div>";
}
}
	?>

[Barny74]

Scrath that , it was the limit by 1 I had to remove. All sorted , thanks

[Barand]

 

I was hoping to show the avg price , last price / date sold

That is what that query was designed to do - just the latest record with price and date plus the average price.

 

When you list all the results for a whisky, what do you want to show?

[Barny74]

Hi. I have it sorted I think. I am showing all the results of that whisky showing name , date , and all past prices. I just need ot work out how to convet the date to ddmmyy when I

echo $row ['date'];

as it currntly shows uo as yyyymmdd

[Barny74]

Forgot i had already asked this in a different question. Sorry.

 

I do have a query. When i have a completly new whisky to add into the DB or add in a new auction sale , Do I do it via CSV or myphpadmin ?and if i did would i need to run the code that you kindly sent me to earlier. I.e populatimg the whisky table with the unique name (#3) and then #4 and #5

[Barand]

Rerunning #3, #4, #5 is a definite no-no. You could end up with completely different set of ids. That was, as previously stated, a one-off exercise.

 

1. How do you create the csv at present?
2. How many test_db records do you typically add at a time?
3. How often do you have new whiskies?