I am having some difficulty getting this script to display results properly. I am sure I need to specify a table join in the select statement, but I am a complete newbie and have no idea how to do this. In the end what I want is a select statement that grabs all the records in the tip_summary table and then sorts the results by the author name which is contained in the authors table (authors.name). The tip_summary also has a column called name (tip_summary.name), but I want that to appear in a separate column in the html table that is output by the script. The script currently outputs a straight cross-join. There are multiple repetitions of each record. How do I use a join?
Here is the script:
any help will save me a lot of frustration and earn my eternal gratitude.PHP Code:<!-- user_tiplist.php -->
<html>
<head>
<title> Teaching Tips </title>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body>
<h1>Teaching Tips</h1>
<p><a href="searchtips.php">Try a new search...</a></p>
<?php
$dbcnx = mysql_connect("localhost", "root", "password");
mysql_select_db("teachingfish");
// The basic SELECT statement
$select = "SELECT DISTINCT tip_summary.ID,
tip_summary.name, authors_ID, LEFT(short_text,100),
date, tip_summary.tip_categories_ID";
$from = " FROM tip_summary, authors";
$where = " WHERE tip_summary.ID >= 0";
if ($aid != "") { // An author is selected
$where .= " AND authors_ID=$aid";
}
if ($cid != "") { // A category is selected
$from .= ", tip_categories_lookup";
$where .= " AND tip_summary.ID=tip_categories_lookup.tip_summary_ID AND tip_categories_lookup.tip_categories_ID=$cid";
}
if ($searchtext != "") { // Some search text was specified
$where .= " AND long_text LIKE '%$searchtext%'";
}
if ($sort != "") { // a sort order was specified
$where .= " ORDER BY $sort";
}else{
$where .= " ORDER BY authors.name";
}
?>
<table border="0">
<tr><th><a href="<?=$PHP_SELF?>?sort=name">Tip Name</a></th>
<th><a href="<?=$PHP_SELF?>?sort=short_text">Description</a></th>
<th><a href="<?=$PHP_SELF?>?sort=authors_ID">Author</a></th>
<th><a href="<?=$PHP_SELF?>?sort=date">Date</a></th><th>Options</th></tr>
<?php
$tips = mysql_query($select . $from . $where);
if (!$tips) {
echo("</table>");
echo("<p>Error retrieving tips from database!<br />".
"Error: " . mysql_error() . "</p>");
exit();
}
while ($tip = mysql_fetch_array($tips)) {
// table row colors alternate
if ( $i%2==0 ) {
$bgcolor = "'#C0E0FF'";
} else {
$bgcolor = "'#EFEFEF'";
}
echo("<tr bgcolor =$bgcolor valign='top'>\n");
$id = $tip["ID"];
// If the tip text is 100 characters long, add "..." to the end of it
// to indicate that it is actually longer. strlen() returns string length!
$tiptext = $tip["LEFT(short_text,100)"];
if (strlen($tiptext)) $tiptext .= "...";
//$tiptext = htmlspecialchars($tip["short_text"]);
$tipdate = $tip["date"];
$tipname = $tip["name"];
$authorID = ($tip["authors_ID"]);
$authorname = mysql_query("SELECT name from authors WHERE ID=$authorID");
$authorname = mysql_fetch_array($authorname);
$aname = $authorname['name'];
echo("<td>$tipname</td>\n");
echo("<td><a href='tip.php?id=$id'>$tiptext</td>\n");
echo("<td>$aname</td>\n");
echo("<td>$tipdate</td>\n");
echo("<td >[<a href='edittip.php?id=$id'>Edit</a>|".
"<a href='deletetip.php?id=$id'>Delete</a>]</td>\n");
echo("</tr>\n");
$i++;
}
?>
</table>
</body>
</html>
p
Bookmarks