0

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; 
}
user3746428
  • 11,047
  • 20
  • 81
  • 137

4 Answers4

4

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
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • Unfortunately, I'd tried that and I've still got the same issue. – user3746428 Apr 22 '16 at 11:38
  • You don't think join work according to fields `course` is table `JOIN cm ON (course = cm.course) `' – urfusion Apr 22 '16 at 11:39
  • 1
    Yup I spotted that @urfusion that will be another issue that probably got missed when OP was messing with the LIMIT positioning – RiggsFolly Apr 22 '16 at 11:40
  • @user3746428 See amended answer, with corrected JOIN. You were probably getting an error like `ambiguous column name` – RiggsFolly Apr 22 '16 at 11:41
  • @RiggsFolly Am I wrong in my answer about JOIN missing a word to the left of it? I thought you could only have LEFT JOIN, RIGHT JOIN, INNER JOIN, and FULL JOIN, and not JOIN by itself? – Webeng Apr 22 '16 at 11:41
  • @Webeng Afraid so. just `JOIN` is legal syntax – RiggsFolly Apr 22 '16 at 11:42
  • @RiggsFolly I see, and which of the 4 options does `JOIN` by itself do? or does it do an action different to the other 4? – Webeng Apr 22 '16 at 11:43
  • 1
    @Webeng See this post http://stackoverflow.com/questions/9770366/difference-in-mysql-join-vs-left-join I had to look it up myself – RiggsFolly Apr 22 '16 at 11:49
  • The different joins are: `[INNER] JOIN`, `LEFT [OUTER] JOIN`, `RIGHT [OUTER] JOIN`, `FULL [OUTER] JOIN` and `CROSS JOIN`. – jarlh Apr 22 '16 at 11:57
1

You have (course = cm.course) in your code. course is a table, and you want to join two columns. so it should be something like (course.itsColumn = cm.course)

Let me know if that helped!

Webeng
  • 7,050
  • 4
  • 31
  • 59
  • We really don't know if `course` is just a table or if `course` is also a column in the table called `course`. The question is very unclear. – apokryfos Apr 22 '16 at 11:45
  • 1
    @apokryfos I agree, though since it was a TYPO of his, the answer should be enough help him notice it, hence solve his problem :) – Webeng Apr 22 '16 at 11:46
0

Try to add an alias. It's recommended that every table in a join is referenced via a alias.

SELECT * 
FROM course AS c
   LEFT JOIN cm AS cm ON (c.course = cm.course) 
WHERE title LIKE '%$searchTerm%' 
LIMIT $limit 
ORDER BY cm.num
cb0
  • 8,415
  • 9
  • 52
  • 80
  • I'm a bit confused.. JOIN does what exactly? I know what INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN do, but JOIN by itself? – Webeng Apr 22 '16 at 11:48
  • Just copied the query and put the alias in there. You are of course right. It should be a `LEFT JOIN`. – cb0 Apr 22 '16 at 11:49
  • 1
    Can you really do LIMIT before ORDER BY like this in MySQL? (Other products have ORDER BY then LIMIT.) – jarlh Apr 22 '16 at 11:50
  • @jarlh No that was the original problem that cb0 missed – RiggsFolly Apr 22 '16 at 11:53
  • @cb0 Why **should** it have been a LEFT JOIN? JOIN is valid syntax and defaults to `INNER JOIN` but on its own is valid syntax – RiggsFolly Apr 22 '16 at 11:54
  • @RiggsFolly Looks like he is trying to get all course material `cm` for all courses `c`. I don't think he is searching for course material with has no associated course. You "could" do other type joins of course. An Inner join would work too. – cb0 Apr 22 '16 at 11:57
0

You are using self join in your code. So can you please check with the following code :

SELECT * FROM course c 
JOIN course cm ON (c.course = cm.course) 
WHERE c.title LIKE '%$searchTerm%' 
ORDER BY cm.num LIMIT $limit
Saloni Uppal
  • 89
  • 4