Translating search function used in CH. 7 BYODDWS

Community
1

I am working through BYO Database Driven Web Site but am converting the code to the site I am working on. I’m in chapter 7 now and have been able to add and edit but have not been able to get the search function to work. (I have not included a delete at this time. I don’t think it will be needed.) I get the “error fetching books” error when I hit the search button.

My database, at this point, consists of 3 tables:
poets: id, firstname, lastname, poetpage, website.
books: id, booktitle, publisherid, poetid, year.
publishers: id, publisher.

I placed the section of code pertaining to the search function immediately after the editform section of code. Here it is:

if (isset($_GET['action']) and $_GET['action'] == 'search')
{
include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';
// The basic SELECT statement
$select = 'SELECT id, booktitle';
$from = 'FROM books';
$where = 'WHERE TRUE';

$poetid = mysqli_real_escape_string($link, $_GET['poet']);
if ($poetid !='') // A poet is selected
{
	$where .= " AND poetid='$poetid'";
}

$result = mysqli_query($link, $select . $from . $where);
if (!$result)
{
$error = 'Error fetching books.';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$books[] = array('id' => $row['id'], 'booktitle' => $row['booktitle']);
}
include 'books.html.php';
exit();
}

//Display Search Form
include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

$result = mysqli_query($link, 'SELECT id, lastname, firstname FROM poets');
if (!$result)
{$error = 'Error fetching poets from database!';
include 'error.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$poets[] = array('id' => $row['id'], 'lastname' => $row['lastname'], 'firstname' => $row['firstname']);
}
include 'searchform.html.php';

?>

the form portion of the search form:

<form action="" method="get">
<div>

<label for="poet">View Books from this Poet:&emsp;</label>
<select name="poet" id="poet">
<option value="">Any poet</option>
<?php foreach ($poets as $poet): ?>
<option value="<?php htmlout($poet['id']); ?>">
	<?php 
	htmlout($poet['lastname']); echo ", "; htmlout($poet['firstname']); ?></option>
	<?php endforeach; ?>
        </select><br /><br />
</div>
<div>
<input type="hidden" name="action"  value="search" />
<input type="submit" value="Search" />
</div>
</form>

the php for books.html.php:

<?php if (isset($books)): ?>
<table>
<tr><th>Book Titles</th><th>Options</th></tr>

<?php foreach ($books as $book): ?>
<tr valign="top">
<td><?php htmlout($book['booktitle']); ?></td>

<td>
<form action="?" method="post">
<div>

<input type="hidden" name="id" value="<?php htmlout($book['id']); ?>"/>
<input type="submit" name="action" value="Edit"/>
</div>
</form>
</td></tr>
<?php endforeach; ?>

</table>
<?php endif; ?>

I feel like it is something simple, but since I am new to php, I don’t see it.

2

You’re welcome :slight_smile:

As a hint for future reference, when a query isn’t working, echo it to see what it actually says.

E.g. :


echo $select . ' '. $from .' '. $where;
$result = mysqli_query($link, $select . ' '. $from .' '. $where);

shows the query on the web page. Of course you need to remove that before you go live, but it’s an easy way to see where things might go wrong.

Besides echo you may also want to take a look at [fphp]print_r[/fphp] and [fphp]var_dump[/fphp]

3

so simple! I had been looking for days. Thanks. It works!

4

The problem is here:


$select = 'SELECT id, booktitle';
$from = 'FROM books';
$where = 'WHERE TRUE';

// [...]

$result = mysqli_query($link, $select . $from . $where);

The query is missing spaces. Right now the query you’re performing is:


SELECT id,booktitleFROM booksWHERE TRUE
                   ^         ^            <-- There should be spaces there

To overcome this change the query line as follows:


$select = 'SELECT id, booktitle';
$from = 'FROM books';
$where = 'WHERE TRUE';

// [...]

$result = mysqli_query($link, $select .' '. $from .' '. $where);

:slight_smile: