user3746428 user3746428 - 7 months ago 17
SQL Question

ORDER BY on joined table

I am attempting to join two tables, and also order the query results by one of the columns within the table I am joining. Everything works great until I add

ORDER BY cm.num
, at which point I get this error:

Call to a member function fetch_assoc() on boolean


Here is my full query:

SELECT *
FROM course
JOIN cm ON (course = cm.course)
WHERE title LIKE '%$searchTerm%'
LIMIT $limit
ORDER BY cm.num


Anyone have any suggestions?

EDIT:

Full code as requested:

function getCourses($searchTerm) {
$mysqli = new mysqli('localhost', 'scott', 'tiger', 'courses');
if ($mysqli->connect_errno) {
header("HTTP/1.1 500 Internal Server Error");
die("Failed to connect to MySQL: {$mysqli->connect_error}");
}
$mysqli->set_charset('utf8');

$courses = [];
$limit = $searchTerm == '' ? 1000 : 10;
$res = $mysqli->query("SELECT * FROM course JOIN cm ON (course = cm.course) WHERE title LIKE '%$searchTerm%' ORDER BY cm.num LIMIT $limit");
while($row = $res->fetch_assoc()) {
$course = new Course(
$row['id'],
$row['title'],
$row['href'],
$row['level'],
$row['award'],
$row['summary'],
$row['dept'],
$row['subject'],
$row['overview'],
$row['wyl'],
$row['careers']
);

array_push($courses, $course);
}

return $courses;
}

Answer

Put the limit after the order by and specify the columns specifically

i.e. JOIN cm ON (course.course = cm.course)

SELECT * 
FROM course 
   JOIN cm ON (course.course = cm.course) 
WHERE title LIKE '%$searchTerm%' 
ORDER BY cm.num
LIMIT $limit 
Comments