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 believe I need to change followed by the code for whole page (2 main sections "search" and "results").
Note: the only variable currently displaying in the results in $joketext.
/////////// EXCERPT : PARTS I THINK I NEED TO CHANGE //////////////
<code>
PART 1 // The basic SELECT statement
$select = 'SELECT DISTINCT id, joketext';
$from = ' FROM joke';
$where = ' WHERE 1=1';
PART 2 / results section
while ($joke = mysql_fetch_array($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchars($joke['joketext']);
echo "<li id=\"jump\">
<article class=\"entry\">
<header>
<h3 class=\"entry-title\"><a href=''>variable title</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p></div>
<footer class=\"entry-info\">
<abbr class=\"published\" title=\"2011-09-22T14:07:00-07:00\">Sept. 22, 2011</abbr>
</footer>
</article>
</li>";
</code>
//////////////////////////// MAIN CODE ///////////////////////
<code>
<html>
<body>
.
<header></header>
.
<section id="search">
<?php
$dbcnx = @mysql_connect('localhost', 'root', 'password');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' . 'database server at this time.</p>');
}
if (!@mysql_select_db('ijdb')) {
exit('<p>Unable to locate the joke ' . 'database at this time.</p>');
}
$authors = @mysql_query('SELECT id, name FROM author');
if (!$authors) {
exit('<p>Unable to obtain author list from the database.</p>');
}
$cats = @mysql_query('SELECT id, name FROM category');
if (!$cats) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
$themes = @mysql_query('SELECT id, name FROM theme');
if (!$themes) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
$geofoci = @mysql_query('SELECT id, name FROM geofocus');
if (!$geofoci) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
?>
<form class="searchField" name="input" action="main_search.php#jump" method="post">
<input type="text" name="searchtext">
<input type="submit" value="Search">
<ul>
<li>
<label><select name="aid" size="1">
<option selected value="">Any Author</option>
<?php
while ($author = mysql_fetch_array($authors)) {
$aid = $author['id'];
$aname = htmlspecialchars($author['name']);
echo "<option value='$aid'>$aname</option>\n";
}
?>
</select></label>
</li>
<li>
<label><select name="cid" size="1">
<option selected value="">Any Category</option>
<?php
while ($cat = mysql_fetch_array($cats)) {
$cid = $cat['id'];
$cname = htmlspecialchars($cat['name']);
echo "<option value='$cid'>$cname</option>\n";
}
?>
</select></label>
</li>
<li>
<label><select name="tid" size="1">
<option selected value="">Any Theme</option>
<?php
while ($theme = mysql_fetch_array($themes)) {
$tid = $theme['id'];
$tname = htmlspecialchars($theme['name']);
echo "<option value='$tid'>$tname</option>\n";
}
?>
</select></label>
</li>
<li>
<label><select name="gfid" size="1">
<option selected value="">Any Region</option>
<?php
while ($geofocus = mysql_fetch_array($geofoci)) {
$gfid = $geofocus['id'];
$gfname = htmlspecialchars($geofocus['name']);
echo "<option value='$gfid'>$gfname</option>\n";
}
?>
</select></label>
</li>
<li><a href="">Closing Date</a></li>
</ul>
</form>
</section>
<section id="results">
<?php
$dbcnx = @mysql_connect('localhost', 'root', 'password');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' . 'database server at this time.</p>');
}
if (!@mysql_select_db('ijdb')) {
exit('<p>Unable to locate the joke ' . 'database at this time.</p>');
}
// 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%'";
}
?>
<ol id="results-list">
[COLOR="#FF0000"]
<?php
$jokes = @mysql_query($select . $from . $where);
if (!$jokes) {
echo '</table>'; exit('<p>Error retrieving jokes from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
while ($joke = mysql_fetch_array($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchars($joke['joketext']);
echo "<li id=\"jump\">
<article class=\"entry\">
<header>
<h3 class=\"entry-title\"><a href=''>variable title</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p></div>
<footer class=\"entry-info\">
<abbr class=\"published\" title=\"2011-09-22T14:07:00-07:00\">Sept. 22, 2011</abbr>
</footer>
</article>
</li>";
}
?>
[/COLOR]
</ol>
</section>
.
<footer></footer>
.
.
</body>
</html>
</code>