Select query with WHERE clause doesn't work any longer

Select query with WHERE clause worked once

[Ann_G]

I have a table in MYSQL that I'm trying to set up a search query on (so it can be searched for records by an author's name). I did it several weeks ago and then it worked, but now when I try to run the search it returns no records although I know they are there. To my knowledge nothing has changed in my PHP script. I checked everyhting repeatedly but must be blind to the error. It's driving my crazy since I know it worked fine at one time.

If anyone has the energy to take a look and offer their suggestions, it would be greatly appreciated. Here is my script. PS. I can add a record and view all the records through other scripts with no problem.

<?php # Script 11.6 - md_seaauthor.php
// This page searches titles by author.

// Set the page title and include the HTML header.
$page_title = 'Search Mother Daughter Bookclub by Author';
include_once ('header4.html');

require_once ('Connections/mysql_connect.php');
// Connect to the database.

$query = "SELECT * FROM mdbookclub
WHERE author ='$author'";

$result = mysql_query ($query);

while ($row = mysql_fetch_array($result)) {

$md = $row['md_id'];
$title = stripslashes($row['title']);
$author = stripslashes($row['author']);
$f_author = stripslashes($row['f_author']);
$pub_year = stripslashes($row['pub_year']);
$call_number = ($row['call_number']);

$display_block .= "<p><strong>Author:</strong> $author <strong>Title:</strong> $title<br />
<strong>Publication year:</strong> $pub_year <br />
<strong>Call number:</strong> $call_number </p>";
}

?>

[Lord Majestic]

I checked everyhting repeatedly but must be blind to the error.

Have you tried printing $query on screen and then executing it manually in whatever frontend you use to access MySQL -- did it show rows you expected to show or not?

[Zipper]

everything seems ok, except for the missing bits of code.. my question would be, how and where u define $author?

to rule out some common issues, as LM pointed out make sure mysql returns a dataset for the query generated by your search page. then replace $author by a raw value to see if it does any good. use mysql_num_rows() to see how many records are being returned

echo mysql_num_rows($result);

also try to add mysql_error() to the query function to see whether anything pops out.

$result = mysql_query ($query) or die(mysql_error()); 

[Ann_G]

Thank you so very much. Your suggestions really helped. The question "where and how do I define author"
was the key. I discovered that I didn't. I had separated this script from another script that did define that term (that's why the search worked in the past) and didn't realize it was left essential.
Once I put:
// Check for an author's last name.
if (isset ($_POST['author']))
{
$author = ($_POST['author']);
} else {
$author = '';
}
The search returned the right results.

Thanks to both of you. I learnt some more important PHP stuff, such as testing it in my PHPMyAdmin.

I think this forum is the best.
Every time I've posted a question, I got such prompt and wise replies.