PHP search (basic level)

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\”>
<article class=\“entry\”>
<header>
<h3 class=\“entry-title\”><a href=‘’$VAR for email or weblink from author table">$VAR for author name from author table</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\”>$VAR for date uploaded in joke table</abbr>
<p>$VAR for joke category from jokecategory table</p>
</footer>
</article>
</li>”;
}
?>
//////////////////////////// MAIN CODE ///////////////////////

<html>
<body>
.
<header></header>
.
<section id=“search”>

&lt;?php

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

if (!$dbcnx) {
	exit('&lt;p&gt;Unable to connect to the ' . 'database server at this time.&lt;/p&gt;');
}

if (!@mysql_select_db('ijdb')) { 
	exit('&lt;p&gt;Unable to locate the joke ' . 'database at this time.&lt;/p&gt;');
}

$authors = @mysql_query('SELECT id, name FROM author'); 
if (!$authors) {
	exit('&lt;p&gt;Unable to obtain author list from the database.&lt;/p&gt;');
}

$cats = @mysql_query('SELECT id, name FROM category'); 
if (!$cats) {
	exit( '&lt;p&gt;Unable to obtain category list from the database.&lt;/p&gt;');
} 

$themes = @mysql_query('SELECT id, name FROM theme'); 
if (!$themes) {
	exit( '&lt;p&gt;Unable to obtain category list from the database.&lt;/p&gt;');
}

$geofoci = @mysql_query('SELECT id, name FROM geofocus'); 
if (!$geofoci) {
	exit( '&lt;p&gt;Unable to obtain category list from the database.&lt;/p&gt;');
}

?&gt;

<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>
“;
}
?>
</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>
“;
}
?>
</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>
“;
}
?>
</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>
“;
}
?>
</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('&lt;p&gt;Unable to connect to the ' . 'database server at this time.&lt;/p&gt;');
}

if (!@mysql_select_db('ijdb')) { 
	exit('&lt;p&gt;Unable to locate the joke ' . 'database at this time.&lt;/p&gt;');
	}

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

?&gt;

&lt;ol id="results-list"&gt;
	
[COLOR="#FF0000"]	&lt;?php 
	$jokes = @mysql_query($select . $from . $where); 
	if (!$jokes) {
		echo '&lt;/table&gt;'; exit('&lt;p&gt;Error retrieving jokes from database!&lt;br /&gt;'.
		'Error: ' . mysql_error() . '&lt;/p&gt;');
	}

	while ($joke = mysql_fetch_array($jokes)) { 
		$id = $joke['id'];
		$joketext = htmlspecialchars($joke['joketext']);
		echo "&lt;li id=\\"jump\\"&gt;
				&lt;article class=\\"entry\\"&gt;
					&lt;header&gt;
						&lt;h3 class=\\"entry-title\\"&gt;&lt;a href=''&gt;variable title&lt;/a&gt;&lt;/h3&gt;
					&lt;/header&gt;
					&lt;div class=\\"entry-content\\"&gt;
						&lt;p&gt;$joketext&lt;/p&gt;&lt;/div&gt;
					&lt;footer class=\\"entry-info\\"&gt;
        				&lt;abbr class=\\"published\\" title=\\"2011-09-22T14:07:00-07:00\\"&gt;Sept. 22, 2011&lt;/abbr&gt;
        			&lt;/footer&gt;
				&lt;/article&gt;
			&lt;/li&gt;";
	}[/COLOR]

?>

&lt;/ol&gt;

</section>
.
<footer></footer>
.
.
</body>
</html>

You’ll have to make your changes in this part of the code:


// 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'";
}


Add the extra columns you want to select to the basic $select.
Add the tables where these columns are found to the basic $from, and the appropriate join conditions to the basic $where
(I won’t get into using INNER JOIN … ON … right now because it might complicate things too much).

If any of the tables you’ll add to the basic $from are being added in the IF’s, then you’ll have to eliminate them from those IF’s, and the join condition from the $where as well, leaving only the filter condition. But I don’t think that’s the case.

good call

you would think that in one of the recent revisions to this book, since it gets revised regularly, the author would have updated this antiquated and deprecated code

maybe someone could whisper in his ear, yeah?

:cool:

Off Topic:

If it’s gonna get updated, it might as well use PDO for database interaction.

Hi guido2004,

Thanks for your help. I’ve been changing the code as you suggested, but I have the feeling I’m still missing something(s). Say I only want to add the author’s name and the category of the joke, apart from the joke text itself. Based on what I understood from your explanation, I would change the basic SELECT to:

$select = 'SELECT DISTINCT joke.id, joke.joketext, author.id, author.name, jokecategory.jokeid, jokecategory.categoryid, category.id, category.name'; 
$from = ' FROM joke, author, jokecategory, category'; 
$where = ' joke.authorid = author.id, joke.id = jokecategory.jokeid, jokecategory.categoryid = category.id';

Is that kind of what you meant? If so, how then do I change the last part of the code? (I pasted it again at the end of this message.)

I tried the new SELECT statement without changing anything else just to see if I “broke anything” and it results in an error “Error retrieving jokes from database! Error: Not unique table/alias: ‘joke’”.

Thanks again for your help and to be fair about the book, there is an updated version (4th ed), I just don’t know if the improvements you guys are talking about were made.

<?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>”;
}

?>

Actually, I just noticed I forgot the WHERE in $where, but the error that comes up now is

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ joke.id = jokecategory.jokeid, jokecategory.categoryid = category.id AND jokete’ at line 1

And I still haven’t touched the output part of the code…

the error is caused by the commas in the WHERE clause

those conditions have to be ANDed

:slight_smile:

Oops, thanks… I swear I’m trying to read up on PHP while I do this, but I’m still making a lot of silly mistakes!

Ok, sorry to be a pain, but I need a little more help. The adjusted SELECT statement seems to be working now, but I would like to display the data from the different tables in my echo. I tried assigning the author name to the variable $aname, but when I use it in the echo it outputs the category name instead.

This might be due to the fact that I use “name” and “id” in various tables, but I would like to keep these names since it makes following the rest of the book (Yank, DB driven website) much easier.

What am I doing wrong? How do I specify which “name” I want to use?

Thank you!

while ($joke = mysql_fetch_array($jokes)) {
$id = $joke[‘id’];
$joketext = htmlspecialchars($joke[‘joketext’]);
$aname = htmlspecialchars($joke[‘name’]);
echo “<li id=\“jump\”>
<article class=\“entry\”>
<header>
<h3 class=\“entry-title\”><a href=‘’>$aname</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>”;
}

i don’t do php but my understanding is that php will access the last column of several identically-named columns if you reference by name

i think php can also access query results by column position… of course you’d have to know which position the identically-named columns were in…

far easier is to assign column aliases in the query itself, so that all result column names are distinct, and reference those

SELECT foo.id AS foo_id
     , foo.name AS foo_name
     , bar.id AS bar_id
     , bar.foo_id AS bar_foo_id
     , bar.name AS bar_name
  FROM foo
INNER
  JOIN bar
    ON bar.foo_id = foo.id

Thanks for the tip! It seems to be working (I can display the specific contents from the right table and column, but I think I’m missing something when renaming the “category” elements. My search based on categories no longer works and returns the following error:

Error: Not unique table/alias: ‘jokecategory’

Does it have to do with the names/variables on my search form?

Revised Select statement:
$select = ‘SELECT DISTINCT joke.id, joke.joketext, author.id AS author_id, author.name AS author_name, jokecategory.jokeid AS cat_jokeid, jokecategory.categoryid AS joke_catid, category.id AS cat_id, category.name as cat_name’;

$from = ’ FROM joke, author, jokecategory, category’;

$where = ’ WHERE joke.authorid = author.id AND joke.id = jokecategory.jokeid AND jokecategory.categoryid = category.id’;

My search form with drop down for categories:

&lt;label&gt;&lt;select name="cid" size="1"&gt;
			&lt;option selected value=""&gt;Any Category&lt;/option&gt; 
		&lt;?php
		while ($cat = mysql_fetch_array($cats)) { 
			$cid = $cat['id']; 
			$cname = htmlspecialchars($cat['name']); 
			echo "&lt;option value='$cid'&gt;$cname&lt;/option&gt;\

";
}
?>
</select></label>

It might also have to do with the part following the Select statement:

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

it might indeed, and yes, there it is, you’ve appended the same table to the from clause a second time –

$from .= ', jokecategory'

Hm… so it’s repeated. Should I remove it from the $from in the basic select statement? I tried and now only the category filter works for the search…

sorry, i can’t help you with the php logic, i know this is the php forum but i really only dropped in to help you with the mysql part

I just removed it from the second $from as you pointed out and left it blank. Didn’t know I could do that. Seems to be working alright now!

Thank you so much for your patience, I really appreciate it!

P.S. I might be back…

We’ll be waiting! :wink: