What am I doing wrong with this search query

I'm using PDO and I'm still wet behind the ears so, here we go:

Here's my search query:

<table width=100% align="center">
					<?php
					include("carter_pdo.inc");
					//Prepare a select statment with 'named' parameters
					$stmt = $db->prepare("SELECT * FROM registration where stage_name LIKE :search");
							
					//setup the data to be passed as the named parameter
					$data = array(
						'search' => trim($_POST['search'])
					);
					//execute the query along with the data
					$stmt->execute($data);

					//if a result was returned (i.e there was a match) then assign that record to $row
					if ($row = $stmt->fetch(PDO::FETCH_OBJ)) 
					{
					?>
					<tr>
					<td class="body"><b>Contestants</b>
					</td>
					</tr>
					<?php
					while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) 
					{
					?>
					<tr>
					<td class="body">
					<A HREF="contestant.php?id=<?php echo $row['id']; ?>" target="_blank" class="body"><?php echo stripslashes($row['stage_name']); ?></a>
					</td>
					</tr>
					<?php
					}
					}
					?>
					</table>	

Open in new window


No errors, but no results.

In other words, there's a row in the database that corresponds to my search query, so I'm getting the table and the "Contestants" header, but no name and no contestant id.

What am I doing wrong?
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
You've already fetched the recordset you don't do it again here
while ($row=$stmt->fetch(PDO::FETCH_ASSOC))


Just do a foreach on the $row (which now contains the recordset)
0
Ray PaseurCommented:
You don't need the if() statement that fetches the row.  The while() iterator will find anything or nothing depending on the number of rows in the results set.  Try modeling your PDO queries after the examples in this article.  It shows how to get the number of rows from SELECT without removing any rows from the results set.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
brucegustPHP DeveloperAuthor Commented:
Yo, Gary!

I changed this:

while ($row=$stmt->fetch(PDO::FETCH_ASSOC))
{

to this:

foreach($row)
{

and I got a parse error on that line. What do I need to do differently?
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

GaryCommented:
				foreach($row as $item) {
					?>
					<tr>
					<td class="body">
					<A HREF="contestant.php?id=<?=$item['id']; ?>" target="_blank" class="body"><?=stripslashes($item['stage_name']); ?></a>
					</td>
					</tr>
					<?php
					}

Open in new window

0
Mark BradyPrincipal Data EngineerCommented:
That is because the variable $row does not exist yet (it has not been set). In a foreach() function you need to have something to loop through (an array) and you assign the values to something.

For example
<?php
$my_aaray = array(
    'name' => 'John Smith', 
    'email' => 'jsmith@example.com', 
    'age' => 34);

// iterate through the array

foreach ($my_array as $key => $value) {
    echo $key .' = ' . $value . '<br />';
}

?>

Open in new window



If your values were themselves arrays you could do this

<?php
$my_aaray = array(
    'name' => 'John Smith', 
    'email' => 'jsmith@example.com', 
    'age' => 34,
    'extra_info' => array(
                               'favorite_color' => 'red',
                               'favorite_number' => 7
                           )
);

// iterate through the array

foreach ($my_array as $key => $value) {
    
if (is_array($value) {
        foreach ($value as $sub_key => $sub_value) {
            echo $sub_key .' = ' . $sub_value . '<br />';
        }
    } else {
        echo $key .' = ' . $value . '<br />';
    }
}

?>

Open in new window

0
Ray PaseurCommented:
Really?  Here is the man page that describes foreach().
http://php.net/manual/en/control-structures.foreach.php

Please stop what you're doing for just a moment and read this article for understanding!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

If you're confused by the PDO examples, please post back here and I'll clarify it for you and I'll clarify the article.  This is not rocket science; there are predictable and dependable code patterns that will work well for you every time.  All you gotta do is learn them and use them!
0
Chris StanyonWebDevCommented:
There's are a few issues with your code. Firstly, get out of the habit of using SELECT *. Specify and retrieve only the columns you need:

$stmt = $db->prepare("SELECT id, stage_name FROM registration where stage_name LIKE :search");

Open in new window

A mySQL LIKE clause needs to be wrapped in % markers, so you need to include that when creating your data:

$data = array(
     'search' => '%' . trim($_POST['search']) . '%'
);

Open in new window

Because you're wanting to retrieve several records, you need the fetch() in a loop. Before that you can check the rowCount to make sure some records were returned. This is the code you need after the execute() call:

echo "<table>";

if ($stmt->rowCount()) :
	//we have some data
	echo "<tr><td class='body'><b>Contestants</b></td></tr>";
	
	//lets loop through them
	while ($row = $stmt->fetch(PDO::FETCH_OBJ)) :

$output = <<<EOT
<tr>
	<td class="body">
		<a href="contestant.php?id=$row->id" target="_blank" class="body">$row->stage_name</a>
	</td>
</tr>
EOT;

	echo $output;

	endwhile;

endif;

echo "</table>";

Open in new window

You'll see we're fetching the records as objects (PDO::FETCH_OBJ) so we access the column as object properties:

$row->id
$row->stage_name

We're also using the HEREDOC syntax because it's easier to create longer, mulit-line strings :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brucegustPHP DeveloperAuthor Commented:
First off, everybody, thanks for your feedback. Online tutorials are a huge resource, but having access to guys who can give specific answers to specific questions is invaluable!

Chris, I went with your solution, although I couldn't get the HEREDOC thing to work. But rather than try to figure that out, I went with your model and put a $stmt->execute($data);       just before the "while" on line 8 and I began weeping tears of joy as I saw my results listed on my page.

Thanks!

And, Ray... I'm reading your articles / tutorials and I'm trying to make you proud, friend.
0
Chris StanyonWebDevCommented:
Bruce - please you got it working...

If you come across something that 'doesn't work', a good approach to debugging is to make sure you have all errors being reported properly by PHP, use print_r() and var_dump() to see what your script is doing, and keep the php.net website open, so you can look up the definitive page for any given function. In your case, if you don't get any errors then var_dump($output) to see if the html was assigned to the variable.

Here's the link to HEREDOC - it's part of the 'strings' page

http://php.net/manual/en/language.types.string.php

You'll see a huge warning in the HEREDOC section about the closing identifier. It must be on it's own line, with no indents!

Good luck with it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.