PHP search (basic level)

system · October 26, 2011, 4:24pm

Hi everyone,
I’m very new to PHP and have been following Kevin Yank’s “How to build a db driven website using php & mysql” 3rd ed. and am working through Chapter 6, where a basic search page is created. What I would like to be able to do is, in the results, display not only the joketext (as done in the book), but also the author’s name (which is located in a different table in the db), as well as the date it was submitted (in the same db as joketext), possibly a link to another site and extra info like the joke category. In general, I would like to be able to understand how to adjust the php code to add data related to the results of any given search. The information is already related through the database, how do I use it in the results of a search?

I’ve used the same names of variables and tables as used in the book.

Thank you in advance for any help you can offer! I really appreciate it.

Below is the code for the part of the code I would like to change (highlighted parts) followed by the code for whole page (2 main sections “search” and “results”. In the main code, the excerpt below is located a few lines from the bottom (in “results”):

Note: the only variable currently displaying in the results in $joketext (also highlighted). Ok, enough explaining!

/////////// EXCERPT : PART I WANT TO DISPLAY DIFFERENTLY //////////////

<?php echo "

$VAR for author name from author table

$joketext

$VAR for date uploaded in joke table

$VAR for joke category from jokecategory table

"; } ?>

//////////////////////////// MAIN CODE ///////////////////////

. . <?php $dbcnx = @mysql_connect('localhost', 'root', 'password'); if (!$dbcnx) { exit('

Unable to connect to the ' . 'database server at this time.

'); } if (!@mysql_select_db('ijdb')) { exit('

Unable to locate the joke ' . 'database at this time.

'); } $authors = @mysql_query('SELECT id, name FROM author'); if (!$authors) { exit('

Unable to obtain author list from the database.

'); } $cats = @mysql_query('SELECT id, name FROM category'); if (!$cats) { exit( '

Unable to obtain category list from the database.

'); } $themes = @mysql_query('SELECT id, name FROM theme'); if (!$themes) { exit( '

Unable to obtain category list from the database.

'); } $geofoci = @mysql_query('SELECT id, name FROM geofocus'); if (!$geofoci) { exit( '

Unable to obtain category list from the database.

'); } ?>

Any Author <?php while ($author = mysql_fetch_array($authors)) { $aid = $author['id']; $aname = htmlspecialchars($author['name']); echo "$aname\n"; } ?>
Any Category <?php while ($cat = mysql_fetch_array($cats)) { $cid = $cat['id']; $cname = htmlspecialchars($cat['name']); echo "$cname\n"; } ?>
Any Theme <?php while ($theme = mysql_fetch_array($themes)) { $tid = $theme['id']; $tname = htmlspecialchars($theme['name']); echo "$tname\n"; } ?>
Any Region <?php while ($geofocus = mysql_fetch_array($geofoci)) { $gfid = $geofocus['id']; $gfname = htmlspecialchars($geofocus['name']); echo "$gfname\n"; } ?>
Closing Date

<?php

$dbcnx = @mysql_connect(‘localhost’, ‘root’, ‘password’);

if (!$dbcnx) {
exit('

Unable to connect to the ’ . ‘database server at this time.

’);
}

if (!@mysql_select_db(‘ijdb’)) {
exit('

Unable to locate the joke ’ . ‘database at this time.

’);
}

// The basic SELECT statement

$select = ‘SELECT DISTINCT id, joketext’;
$from = ’ FROM joke’;
$where = ’ WHERE 1=1’;

$aid = $_POST[‘aid’];
if ($aid != ‘’) { // An author is selected
$where .= " AND authorid=’$aid’";
}

$cid = $_POST[‘cid’];
if ($cid != ‘’) { // A category is selected
$from .= ‘, jokecategory’;
$where .= " AND joke.id=jokecategory.jokeid AND categoryid=’$cid’";
}

$tid = $_POST[‘tid’];
if ($tid != ‘’) { // A theme is selected
$from .= ‘, joketheme’;
$where .= " AND joke.id=joketheme.jokeid AND themeid=’$tid’";
}

$gfid = $_POST[‘gfid’];
if ($gfid != ‘’) { // A region is selected
$from .= ‘, jokegeofocus’;
$where .= " AND joke.id=jokegeofocus.jokeid AND geofocusid=’$gfid’";
}

$searchtext = $_POST[‘searchtext’];
if ($searchtext != ‘’) { // Some search text was specified
$where .= " AND joketext LIKE ‘%$searchtext%’";
}

?>

Error retrieving jokes from database!
'. 'Error: ' . mysql_error() . '

variable title

$joketext

Sept. 22, 2011

. . .

PHP search (basic level)

$VAR for author name from author table

variable title