Jump to content

Show single listing from Search - PHP SQL


Barny74

Recommended Posts

  • Replies 54
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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

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

Link to comment
Share on other sites

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

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

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

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



 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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

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.