PDO connection fetching from DB on user input

Beginners - Learning PHP
#1

Hi

I’m trying to change my script from mysql connection to PDO and I managed to fetch data but there’s an input box and based on the input fetched data is supposed to change but the results just disappear for me when i click ‘search the table’
[php]

<?php $sql = "SELECT * FROM apps "; try { $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch ( PDOException $e ) { echo "I'm sorry there is a problem with your operation.."; file_put_contents( 'dbErrors.txt', $e->getMessage(), FILE_APPEND ); } if (isset($_POST['search'])) { $stmt->bindParam(':search_term', $_POST['search_box']); $sql = "WHERE app_name = :search_term OR category = :search_term"; $stmt = $con->prepare( $sql ); $stmt->setFetchMode(PDO::FETCH_ASSOC); $stmt->execute(); } else { $stmt = $con->prepare( $sql ); $stmt->setFetchMode(PDO::FETCH_ASSOC); $stmt->execute(); } ?> 
<form name="search_form" method="POST" action="apps.php">
Search: <input type="text" name="search_box" value="" />
<input type="submit" name="search" value="Search the table" >
</form>
			<table width="90%" cellpadding="5" cellspace="5">
				<tr>
					<td>Name</td>
					<td>Category</td>
					<td>Details</td>
				</tr>
<?php while( $row = $stmt->fetch()) { ?> 
				<tr>
					<td><?php echo $row['app_name']; ?></td>
					<td><?php echo $row['category']; ?></td>
					<td><a href="template.php?id<?php echo $row['appID']; ?>">Details</a></td>
				</tr>
<?php } ?>

[/php]

#2

You need to move the bind param down to where you have a $stmt variable instantiated. If you show all errors/warnings/notices (as you should when developing) it should show up

Prepare
Bind params
Execute

#3

right, thx for reply

so i’ve changed the code and enabled errors in my config file
[php]

<?php $sql = "SELECT * FROM apps "; try { $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch (PDOException $e) { echo $e->getMessage(); //catch and show the error } if (isset($_POST['search'])) { $sql = "WHERE app_name = :search_term OR category = :search_term"; $stmt = $con->prepare( $sql ); $stmt->bindParam(':search_term', $_POST['search_box']); $stmt->setFetchMode(PDO::FETCH_ASSOC); $stmt->execute(); } else { $stmt = $con->prepare( $sql ); $stmt->setFetchMode(PDO::FETCH_ASSOC); $stmt->execute(); } ?>

[/php]
still shows nothing on input submission

#4

How does the code where you actually fetch the data look?

#5

ok fixed it, stupid mistake with the sql variable

Thx for help :wink:

#6

ok but there’s another one xD

As you can see in my previous code there’s a link with parametised URL that should lead to template page and pass the id which it does but it doesn’t display anything once again and no errors are displayed

[php]<?php

try {
$con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

} catch (PDOException $e) {
echo $e->getMessage(); //catch and show the error
}

$stmt = $con->prepare( "SELECT * FROM apps WHERE appID = :id" );
$stmt->bindParam(":id", $_GET["id"]);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();

?>

<?php while( $row = $stmt->fetch()) { ?> 
			<h2><?php echo $row['app_name']; ?></h2>
			<img src="<?php echo $row['picture']; ?>" alt="app image" />
			<p>Category:<?php echo $row['category']; ?></p>
			<p id="description"><?php echo $row['description']; ?></p>
			<a href="<?php echo $row['link']; ?>">Download</a>
<?php } ?>[/php]
Sponsor our Newsletter | Privacy Policy | Terms of Service