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_arr ay($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchar s($joke['joketext']);
echo "<li id=\"jump\">
<article class=\"entry\" >
<header>
<h3 class=\"entry-title\"><a href=''>variabl e title</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p></div>
<footer class=\"entry-info\">
<abbr class=\"publish ed\" 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('S ELECT id, name FROM author');
if (!$authors) {
exit('<p>Unable to obtain author list from the database.</p>');
}
$cats = @mysql_query('S ELECT id, name FROM category');
if (!$cats) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
$themes = @mysql_query('S ELECT id, name FROM theme');
if (!$themes) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
$geofoci = @mysql_query('S ELECT id, name FROM geofocus');
if (!$geofoci) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
?>
<form class="searchFi eld" name="input" action="main_se arch.php#jump" method="post">
<input type="text" name="searchtex t">
<input type="submit" value="Search">
<ul>
<li>
<label><selec t name="aid" size="1">
<option selected value="">Any Author</option>
<?php
while ($author = mysql_fetch_arr ay($authors)) {
$aid = $author['id'];
$aname = htmlspecialchar s($author['name']);
echo "<option value='$aid'>$a name</option>\n";
}
?>
</select></label>
</li>
<li>
<label><selec t name="cid" size="1">
<option selected value="">Any Category</option>
<?php
while ($cat = mysql_fetch_arr ay($cats)) {
$cid = $cat['id'];
$cname = htmlspecialchar s($cat['name']);
echo "<option value='$cid'>$c name</option>\n";
}
?>
</select></label>
</li>
<li>
<label><selec t name="tid" size="1">
<option selected value="">Any Theme</option>
<?php
while ($theme = mysql_fetch_arr ay($themes)) {
$tid = $theme['id'];
$tname = htmlspecialchar s($theme['name']);
echo "<option value='$tid'>$t name</option>\n";
}
?>
</select></label>
</li>
<li>
<label><selec t name="gfid" size="1">
<option selected value="">Any Region</option>
<?php
while ($geofocus = mysql_fetch_arr ay($geofoci)) {
$gfid = $geofocus['id'];
$gfname = htmlspecialchar s($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=jokecat egory.jokeid AND categoryid='$ci d'";
}
$tid = $_POST['tid'];
if ($tid != '') { // A theme is selected
$from .= ', joketheme';
$where .= " AND joke.id=jokethe me.jokeid AND themeid='$tid'" ;
}
$gfid = $_POST['gfid'];
if ($gfid != '') { // A region is selected
$from .= ', jokegeofocus';
$where .= " AND joke.id=jokegeo focus.jokeid AND geofocusid='$gf id'";
}
$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($s elect . $from . $where);
if (!$jokes) {
echo '</table>'; exit('<p>Error retrieving jokes from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
while ($joke = mysql_fetch_arr ay($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchar s($joke['joketext']);
echo "<li id=\"jump\">
<article class=\"entry\" >
<header>
<h3 class=\"entry-title\"><a href=''>variabl e title</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p></div>
<footer class=\"entry-info\">
<abbr class=\"publish ed\" title=\"2011-09-22T14:07:00-07:00\">Sept. 22, 2011</abbr>
</footer>
</article>
</li>";
}
?>
[/COLOR]
</ol>
</section>
.
<footer></footer>
.
.
</body>
</html>
</code>
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_arr ay($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchar s($joke['joketext']);
echo "<li id=\"jump\">
<article class=\"entry\" >
<header>
<h3 class=\"entry-title\"><a href=''>variabl e title</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p></div>
<footer class=\"entry-info\">
<abbr class=\"publish ed\" 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('S ELECT id, name FROM author');
if (!$authors) {
exit('<p>Unable to obtain author list from the database.</p>');
}
$cats = @mysql_query('S ELECT id, name FROM category');
if (!$cats) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
$themes = @mysql_query('S ELECT id, name FROM theme');
if (!$themes) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
$geofoci = @mysql_query('S ELECT id, name FROM geofocus');
if (!$geofoci) {
exit( '<p>Unable to obtain category list from the database.</p>');
}
?>
<form class="searchFi eld" name="input" action="main_se arch.php#jump" method="post">
<input type="text" name="searchtex t">
<input type="submit" value="Search">
<ul>
<li>
<label><selec t name="aid" size="1">
<option selected value="">Any Author</option>
<?php
while ($author = mysql_fetch_arr ay($authors)) {
$aid = $author['id'];
$aname = htmlspecialchar s($author['name']);
echo "<option value='$aid'>$a name</option>\n";
}
?>
</select></label>
</li>
<li>
<label><selec t name="cid" size="1">
<option selected value="">Any Category</option>
<?php
while ($cat = mysql_fetch_arr ay($cats)) {
$cid = $cat['id'];
$cname = htmlspecialchar s($cat['name']);
echo "<option value='$cid'>$c name</option>\n";
}
?>
</select></label>
</li>
<li>
<label><selec t name="tid" size="1">
<option selected value="">Any Theme</option>
<?php
while ($theme = mysql_fetch_arr ay($themes)) {
$tid = $theme['id'];
$tname = htmlspecialchar s($theme['name']);
echo "<option value='$tid'>$t name</option>\n";
}
?>
</select></label>
</li>
<li>
<label><selec t name="gfid" size="1">
<option selected value="">Any Region</option>
<?php
while ($geofocus = mysql_fetch_arr ay($geofoci)) {
$gfid = $geofocus['id'];
$gfname = htmlspecialchar s($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=jokecat egory.jokeid AND categoryid='$ci d'";
}
$tid = $_POST['tid'];
if ($tid != '') { // A theme is selected
$from .= ', joketheme';
$where .= " AND joke.id=jokethe me.jokeid AND themeid='$tid'" ;
}
$gfid = $_POST['gfid'];
if ($gfid != '') { // A region is selected
$from .= ', jokegeofocus';
$where .= " AND joke.id=jokegeo focus.jokeid AND geofocusid='$gf id'";
}
$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($s elect . $from . $where);
if (!$jokes) {
echo '</table>'; exit('<p>Error retrieving jokes from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
while ($joke = mysql_fetch_arr ay($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchar s($joke['joketext']);
echo "<li id=\"jump\">
<article class=\"entry\" >
<header>
<h3 class=\"entry-title\"><a href=''>variabl e title</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p></div>
<footer class=\"entry-info\">
<abbr class=\"publish ed\" title=\"2011-09-22T14:07:00-07:00\">Sept. 22, 2011</abbr>
</footer>
</article>
</li>";
}
?>
[/COLOR]
</ol>
</section>
.
<footer></footer>
.
.
</body>
</html>
</code>