Before I start looking into that code, I just wanted you to know that I have the categories in a separate table and also the authors in a separate table.
There is also a lookup table (‘jokecategory’).
I don’t know if this makes any difference in your code. It probably does since it’s different tables.
Thanks
Bjorn
One joke can have different categories but only one author, and the authors id is a column in the joke table?
In that case, try something like this:
First sql-query
$query_count = "SELECT count(*)
FROM joke AS j
LEFT JOIN jokecategories AS jc
ON j.id = jc.joke
WHERE 1 $where";
Second sql-query
$query_count = "SELECT DISTINCT j.id, j.jokedate, j.joketitle, j.joketext
FROM joke AS j
LEFT JOIN jokecategories AS jc
ON j.id = jc.joke
WHERE 1 $where
ORDER BY j.joketitle
LIMIT $limitvalue, $limit";
If you use SQL_CALC_FOUND_ROWS, you would be able to join these two querys into one:
$sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT j.id, j.jokedate, j.joketitle, j.joketext
FROM joke AS j
LEFT JOIN jokecategories AS jc
ON j.id = jc.joke
WHERE 1 $where
ORDER BY j.joketitle
LIMIT $limitvalue, $limit";
$result = mysql_query($sql) or die("SQL: $sql<br />" . mysql_error());
$num_query = mysql_query("SELECT FOUND_ROWS();");
$totalrows = mysql_result($num_query,0); //Total number of rows.
I’m going to add the full search and display page so maybe it’s easier for you to fill in stuff.
Because all this is a bit too advanced for me I think and I would probably just mess things up by trying to add myself.
I’m going to give you the table names and field names and maybe that will give you a better overview. Maybe that’s easier for you to know what I have at the present time and what is needed to change it to work.
Table 1 = author
Fields = id, name, email
Table 2 = category
Fields = id, name
Table 3 = joke
Fields = id, joketext, jokedate, authorid
Table 4 = jokecategory
(this is a lookup table - jokeid and categoryid are primary key)
Fields = jokeid, categoryid
I’m adding the SEARCH page and DISPLAY page. If you could add your code to these two pages, I can paste and try and see if it works maybe?
Thanks
Bjorn
THE SEARCH PAGE
<?php
//REQUEST RESULT SETS FROM AUTHORS AND CATEGORIES
$authors = @mysql_query("SELECT id, name FROM author ORDER BY name");
if (!$authors){
exit('Error requesting authors' . mysql_error());
}
$cats = @mysql_query("SELECT id, name FROM category ORDER BY name");
if (!$cats){
exit('Error requesting categories' . mysql_error());
}
//NEXT COMES THE SELECT FORM FIELDS FOR SEARCHING WHERE YOU USE A WHILE LOOP IN THE OPTION FIELDS TO
//LIST AUTHORS AND CATEGORIES
?>
<table width="780"><tr><td valign="middle" class="gray"><div align="left"> <form method="post" action="jokelist3.php">
<p>Search by author:<br />
<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>
</p>
<p> Search by Category:<br />
<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>
</p>
<p> Search by text:<br />
<input type="text" name="searchtext" />
</p>
<p>
<input type="submit" value="Search" />
</p>
</form></div></td></tr></table></center>
DISPLAY PAGE
<form method="post" action="deletejoke3.php">
<input type="hidden" name="submit" value="1">
<table width="780" cellpadding="4" cellspacing="0"><tr><td class="gray2" align="right" width="680">With Selected</td><td class="gray2" align="center" width="100"><input type="submit" value="Delete"></td></tr></table>
<table width="780" cellpadding="4" cellspacing="0"><tr><th class="bottomborder" align="left" height="30" valign="middle">#</th><th width="190" class="bottomborder" align="left">Website Category</th><th class="bottomborder" align="left">Website Address</th><th class="bottomborder" align="left" width="100">Date</th><th class="bottomborder" align="left" width="100">Options</th></tr>
<?php
//BASIC SELECT STATEMENT AND BUILDING THE SQL QUERY
$select = 'SELECT DISTINCT id, jokedate, joketitle';
$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 id=jokeid AND categoryid='$cid'";
}
$searchtext = $_POST['searchtext'];
if($searchtext != ''){//TEXT HAS BEEN SELECTED
$where .= " AND joketext LIKE '%$searchtext%'";
}
$limit = 20;
$page = isset($_GET['page']) ? $_GET['page'] : 1;//or somewhere
$AddQuery = '&foo=bar&foo1=bar2';//CHANGE THIS, this adds the default query string paramaters into links
//get the total number of pages
// Sets what we want to pull from the database
$query_count = "SELECT count(*) FROM joke"; //CHANGE THIS TO YOUR ARRAY OF STUFF
$result_count = mysql_query($query_count);
if(!$result_count){
$error = mysql_error()."<br>$query_count";
} else {
$totalrows = mysql_result($result_count,0);
$totalpages = ceil($totalrows/$limit); //Total number of pages.
//just divide the total rows by the limit
$limitvalue = $page * $limit - ($limit);
// Ex: (2 * 25) - 25 = 25 <- data starts at 25
}
//START PAGINATION
if($page != 1){
$pageprev = $page-1;
$PaginationLinks = '<a href="'.$_SERVER['PHP_SELF'].'?page='.$pageprev.'&'.$AddQuery.'">PREV</a> ';
}else{
$PaginationLinks = 'PREV ';
}
$numofpages = $totalrows / $limit;
for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
$PaginationLinks .=$i.' ';
}else{
$PaginationLinks .= ' <a href="'.$_SERVER['PHP_SELF'].'?page='.$i.$AddQuery.'">'.$i.'</a> ';
}
}
if(($totalrows/$limit) != 0){
if($i == $page){
$PaginationLinks .= $i.' ';
}else{
$PaginationLinks .= '<a href="'.$_SERVER['PHP_SELF'].'?page='.$i.'&'.$AddQuery.'">'.$i.'</a> ';
}
}
if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page+1;
$PaginationLinks .= ' <a href="'.$_SERVER['PHP_SELF'].'?page='.$pagenext.'&'.$AddQuery.'>NEXT</a>' ;
}else{
$PaginationLinks .=' NEXT ';
}
//END PAGINATION
//__________________
//now get the page data
$query_count = "SELECT DISTINCT id, jokedate, joketitle, joketext FROM joke ORDER BY joketitle LIMIT $limitvalue, $limit"; //CHANGE THIS TO YOUR ARRAY OF STUFF
$result_count = mysql_query($query_count);
if(!$result_count){
$error = mysql_error()."<br>$query_count";
}
$row = $limitvalue;
while($joke = mysql_fetch_array($result_count)){
echo "<tr valign='top'>\
";
$id = $joke['id'];
$jokedate = $joke['jokedate'];
$joketitle = $joke['joketitle'];
$joketext = htmlspecialchars($joke['joketext']);
$row++;
echo "<td height='20' class='leftbottomborder' valign='middle'><div align='left' id='coolmenub'>$row</div></td>";
echo "<td height='20' class='leftbottomborder' valign='middle'><div align='left' id='coolmenub'><a href=\\"joke3.php?id=$id\\">$joketitle </a></div></td>";
echo "<td height='20' class='leftbottomborder' valign='middle'><div align='left' id='coolmenub'>$joketext</div></td>\
";
echo "<td height='20' class='leftbottomborder' valign='middle'><div align='left' id='coolmenub'>$jokedate</div></td>\
";
echo "<td class='rightbottomborder' valign='middle'><div align='middle'><a href='editjoke3.php?id=$id'>Edit</a> | Delete <input type=\\"checkbox\\" name=\\"variable[]\\" value=\\"$id\\" /></div></td>\
";
echo "</tr>\
";
}
//_________________________________________________________________
echo "<p><br>";
//DISPLAY PAGINATION
// This shows the user what page they are on, total pages and the rownumbers displayed
$firstRowOnPage = $limitvalue + 1;
$lastRowOnPage = ($limitvalue + $limit) > $totalrows ? $totalrows : ($limitvalue + $limit);
echo "<div>Page $page of $totalpages</div>";
echo "<div>Rows $firstRowOnPage-$lastRowOnPage of $totalrows</div>";
echo "<div>$PaginationLinks</div>";
//________________________________________________________________________
?>
</table><br /><hr noshade color="666666" size="1" width="780">
<table width="780" cellpadding="4" cellspacing="0"><tr><td class="gray" align="right" width="680">With Selected</td><td class="gray" align="center" width="100"><input type="submit" value="Delete"></td></tr></table>
</form></center>