I'm working on the 'riskyjob's example website. I've gotten to the point on page 546 where it asks you to incorporate the changes from page 544 and 545.
I've added the generate_sort_links to the search.php file:
// Generate the search result headings
function generate_sort_links($user_search, $sort) {
$sort_links = '';
switch ($sort) {
case 1:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=2">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=3">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=5">Date Posted</a></td>';
break;
case 3:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=1">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=4">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=5">Date Posted</a></td>';
break;
case 5:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=1">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=3">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=6">Date Posted</a></td>';
default:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=1">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=3">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=5">Date Posted</a></td>';
}
return $sort_links;
}
And made the necessary changes to build_query():
function build_query($user_search, $sort) {
$search_query = "SELECT * FROM riskyjobs";
// Extract the search keywords into an array
$clean_search = str_replace(',', ' ', $user_search);
$search_words = explode(' ', $clean_search);
$final_search_words = array();
if (count($search_words) > 0) {
foreach ($search_words as $word) {
if (!empty($word)) {
$final_search_words[] = $word;
}
}
}
// Generate a WHERE clause using all of the search keywords
$where_list = array();
if (count($final_search_words) > 0) {
foreach($final_search_words as $word) {
$where_list[] = "description LIKE '%$word%'";
}
}
$where_clause = implode(' OR ', $where_list);
// Add the keyword WHERE clause to the search query
if (!empty($where_clause)) {
$search_query .= " WHERE $where_clause";
}
// Sort the search query using the sort setting
switch ($sort) {
// Ascending by job title
case 1:
$search_query .= " ORDER BY title";
break;
//Descending by job title
case 2:
$search_query .= " ORDER BY title DESC";
break;
//Ascending by state
case 3:
$search_query .= " ORDER BY state";
break;
//Descending by state
case 4:
$search_query .= " ORDER BY state DESC";
break;
//Ascending by date posted (oldest first)
case 5:
$search_query .= " ORDER BY date_posted";
break;
//Descending by date posted (newest first)
case 6:
$search_query .= " ORDER BY date_posted DESC";
break;
default:
// No sort setting provided, so don't sort the query
}
return $search_query;
}
And so far I've had luck getting the Job Title and State columns to sort correctly in both forward and reverse direction.
But for some reason if I sort by date a second time to reverse the order the text becomes disjointed as I show in the second image.
I've pored over the code and can't quite seem to nail what could be causing this. I'd certainly appreciate any advice you could provide.
Here's the complete code from search.php in case that might help:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Risky Jobs - Search</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<img src="riskyjobs_title.gif" alt="Risky Jobs" />
<img src="riskyjobs_fireman.jpg" alt="Risky Jobs" style="float:right" />
<center><h3>Risky Jobs - Search Results</h3></center>
<?php
// Connect to the database
require_once('connectvars.php');
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
or die(DB_CONNECT_ERR . mysqli_connect_error() . DB_CONNECT_ERR_NO . mysqli_connect_errno());
// Grab the sort setting and search keywords from the URL using GET
$sort = $_GET['sort'];
$user_search = $_GET['usersearch'];
function build_query($user_search, $sort) {
$search_query = "SELECT * FROM riskyjobs";
// Extract the search keywords into an array
$clean_search = str_replace(',', ' ', $user_search);
$search_words = explode(' ', $clean_search);
$final_search_words = array();
if (count($search_words) > 0) {
foreach ($search_words as $word) {
if (!empty($word)) {
$final_search_words[] = $word;
}
}
}
// Generate a WHERE clause using all of the search keywords
$where_list = array();
if (count($final_search_words) > 0) {
foreach($final_search_words as $word) {
$where_list[] = "description LIKE '%$word%'";
}
}
$where_clause = implode(' OR ', $where_list);
// Add the keyword WHERE clause to the search query
if (!empty($where_clause)) {
$search_query .= " WHERE $where_clause";
}
// Sort the search query using the sort setting
switch ($sort) {
// Ascending by job title
case 1:
$search_query .= " ORDER BY title";
break;
//Descending by job title
case 2:
$search_query .= " ORDER BY title DESC";
break;
//Ascending by state
case 3:
$search_query .= " ORDER BY state";
break;
//Descending by state
case 4:
$search_query .= " ORDER BY state DESC";
break;
//Ascending by date posted (oldest first)
case 5:
$search_query .= " ORDER BY date_posted";
break;
//Descending by date posted (newest first)
case 6:
$search_query .= " ORDER BY date_posted DESC";
break;
default:
// No sort setting provided, so don't sort the query
}
return $search_query;
}
$search_query = build_query($user_search, $sort);
// Generate the search result headings
function generate_sort_links($user_search, $sort) {
$sort_links = '';
switch ($sort) {
case 1:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=2">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=3">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=5">Date Posted</a></td>';
break;
case 3:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=1">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=4">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=5">Date Posted</a></td>';
break;
case 5:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=1">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=3">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=6">Date Posted</a></td>';
default:
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=1">Job Title</a></td><td>Description</td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=3">State</a></td>';
$sort_links .= '<td><a href = "' . $_SERVER['PHP_SELF'] . '?usersearch=' . $user_search .
'&sort=5">Date Posted</a></td>';
}
return $sort_links;
}
$links = generate_sort_links($user_search, $sort);
// Start generating the table of results
echo '<table border="0" cellpadding="2">';
echo $links;
$result = mysqli_query($dbc, $search_query)
or die( SQL_SELECT_ERR . mysqli_error($dbc) . SQL_ERR_NO . mysqli_errno($dbc) . QUERY_USED . $search_query);
while ($row = mysqli_fetch_array($result)) {
echo '<tr class="results">';
echo '<td valign="top" width="20%">' . $row['title'] . '</td>';
echo '<td valign="top" width="50%">' . substr($row['description'], 0, 100) . '...</td>';
echo '<td valign="top" width="10%">' . $row['state'] . '</td>';
echo '<td valign="top" width="20%">' . substr($row['date_posted'], 0, 10) . '</td>';
echo '</tr>';
}
echo '</table>';
echo '<br /><center>Back to <a href=' . 'search.html' . '>Search</a></center><br />';
mysqli_close($dbc);
?>
</body>
</html>
Thank you
