PHP search (basic level)

tenzijth · October 26, 2011

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 "<li id=\"jump\">

<h3 class=\"entry-title\"><a href=''$VAR for email or weblink from author table">$VAR for author name from author table</a></h3>

</header>

<p>$joketext</p></div>

<abbr class=\"published\" title=\"2011-09-22T14:07:00-07:00\">$VAR for date uploaded in joke table</abbr>

<p>$VAR for joke category from jokecategory table</p>

</footer>

</article>

</li>";

}

?>

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

<html>

<body>

.

.

<?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>');

}

?>

<ul>

<li>

<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>

<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>

<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>

<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>

<?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%'";

}

?>

<?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\">

<h3 class=\"entry-title\"><a href=''>variable title</a></h3>

</header>

<p>$joketext</p></div>

</footer>

</article>

</li>";

}

?>

</ol>

</section>

.

.

</body>

</html>

Sign In

PHP search (basic level)

Recommended Posts

tenzijth

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information