Showing Results - PHP / SQL

[Barny74]

Hi.

 

I have coded a simple search bar which shows results based on my databse. Its based on Whiskie. Nice... So this is part one of a two part question, Part two to come later. Basically what I want to do is have the search results show the name , then have the name be a link to more details. The search results currently show a box with the name , last price paid and the date. Really appreciate the help.

 

Here is my code

<?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 $row['name'];
            echo "</div>";

            echo "<div class= 'results_body'>";
                    
            echo "<div class= 'result_lowprice'>";
                    echo "Price Paid: £";
                    echo $row['price'];
            echo "</div>";

            echo "<div class= 'last_date'>";
                    echo "Last date sold: ";
                    echo $row['date'];
            echo "</div>";
        echo "</div>";
    echo "</div>";

    }

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

    ?>
</div>
</html>

Edited January 19, 2017 by Barand

[Barand]

It really is time that you learnt to use the <> button in the toolbar for your code.

[Barny74]

Sorry, How can I edit my original question ? , or delete it and start again,

 

Thanks

[Barand]

I added the code tags for you this time.

 

Is it the linking to the whisky details you are having a problem with?

[Barny74]

Thanks, So here is my code as I have worked out how to get a link to a details.php I am just not sure what I need to do on my details.php page. Really appreciate the help.

 

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=\"details.php\">" . $row['name'] . "</a>";
			echo "</div>";
			echo "</div>";
	} 

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

	?>

details.php

	<?php

	$page='details';
	include('header.php');
	include ('navbar.php'); 
	echo "<br>";
	include ('connect.php');
	
	?>

[Barand]

In your link, include the id of the whisky record in the querystring

echo "<a href='whisky_details.php?id={$row['id']}' >{$row['name']}</a>;";

In the detail page, query the table for the record with that id (in $_GET['id'] ) then display the details.

Edited January 19, 2017 by Barand

[Barny74]

Hi , Thanks for this and really appreciate the time helping me. I have had a go with the code and get the below error on teh setails.php

 

 

Parse error: syntax error, unexpected '$_GET' (T_VARIABLE) in C:\wamp\www\search1\details.php on line 17

 

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

	(in $_GET['lot_id'] );

	echo $row['name'];
	
	?>
</html>

[Barand]

Your syntax makes no sense. You need something like

if (isset($_GET['id'])) {
    $sql = "SELECT name, etc FROM test_db WHERE lot_id = :id";
    $conn->prepare($sql);
    $conn->execute( [  'id' => $_GET['id'] ] );

    /// display results
}

Edited January 19, 2017 by Barand

[Barny74]

I am a tad confused. So would the code form my search reulsts need changing

$stmt = $conn->prepare("SELECT * FROM test_db WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching

or would I need to add this to the details.php. I know this must be frustrating for you but I really appreciate the help. Its a steep learning curve and have basically gone from knowing nothing about web design to this in about 3 weeks.

[Barand]

No. The search stays as it is.

 

That lists all the products matching the search criteria.

 

The user then clicks on one of the links you just added.

 

This takes them to detail.php which queries the db for that selected product and displays the full details (for that one product).

Edited January 19, 2017 by Barand

[Barny74]

Ok, Starting to make a bit more sence ,

 

So my code stands as belwo , but getting a error now,

 

Fatal error: Call to undefined method PDO::execute() in C:\wamp\www\search1\details.php on line 20

 

Line 20 is the $conn->execute( [  'id' => $_GET['id'] ] );

<?php

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

	if (isset($_GET['id'])) {
    $sql = "SELECT name FROM test_db WHERE lot_id = :id";
    $conn->prepare($sql);
    $conn->execute( [  'id' => $_GET['id'] ] );

    echo ['name'];
}

[Barand]

Sorry, my fault. I was torn between writing pseudocode and actual code. Should be

 

$stmt = $conn->prepare($sql);
$stmt->execute( [ 'id' => $_GET['id'] ] );
$row = $stmt->fetch();
echo $row['name'];

[Barny74]

Outstanding , thank you so much for your help. I really appreciate it. My next step is going to be the difficult one. I may post again soon. I have several whiskies with the same name in my database and need to work out how to find and echo an averge price , work out what the last date one was sold and also what that price was. I have the databse filled with all sale prices and date etc. So may be back soon,

 

Thanks again and appreciate your help tonight.

 

Barny

[Barand]

 

I have several whiskies with the same name

 

You could always send me one

[Barny74]

Ha ha , well I may just do that once I get this site up and running. Thanka again