Hi all,

I’m having some trouble finding example of using JOINs with PDO in the manual and google.

Here’s my code:

$query = "SELECT id, url, thirdparty, lc.category FROM links ";
$query .= "LEFT JOIN link_categories as lc ON lc.id = links.category_fk ";
$query .= "ORDER BY thirdparty ASC ";
$sql = $dbh->prepare($query);
$numResults = $sql->rowCount();
$max = 'LIMIT ' .$totaloffset.',' .PERPAGE;
$query .= $max;
$stmt = $dbh->query($query);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);//This line throws the error

I get this:

Fatal error: Call to a member function fetchAll() on a non-object in Filename on line 38

If I take out the JOIN, it works fine. Could I get some help please?

Thanks in advance!

Got it working.

$query = "SELECT l.id, l.url, l.thirdparty, lc.category FROM links as l ";
$query .= "LEFT JOIN link_categories AS lc ON lc.id=l.category_fk ";
$query .= "ORDER BY $order $sort ";
$sql = $dbh->prepare($query);
$numResults = $sql->rowCount();
$max = 'LIMIT ' .$totaloffset.',' .PERPAGE;
$query .= $max;
$stmt = $dbh->query($query);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

I guess adding an alias to the first table and adding the prefix to each field name is needed? (apologies if the lingo is off there, any corrections are welcome)

If links and link_categories both had an ‘id’ field then the sql statement would not have worked - but that would clearly be an sql problem and not a PDO problem.

As you built up your query in a variable called $query then you could have echoed $query onto the page somewhere and posted it into PhpMyAdmin which means you would have discovered that sooner.

You’re executing the query twice.

query() executes a raw sql query.
executes() executes a prepared statement.

You don’t need to use both.

You might also consider using the heredoc format:

    $query = <<<EOT
SELECT l.id, l.url, l.thirdparty, lc.category FROM links as l
LEFT JOIN link_categories AS lc ON lc.id=l.category_fk
ORDER BY $order, $sort

I’m using a paginator class to page results…the first query is to find total number of records, the second is what’s used for outputting the data to the page.

Is there a better way?

Ah yes…duh. :wink: The really simple solutions escape me sometimes.

So even if I’m not asking for the id field of the link_categories table, it fails? I’ve never had that problem when working with the normal mysql functions.

Thanks for the replies guys!!

You could issue a select which only retrieves a COUNT(*), as currently you’re making the database actually grab all the rows. Also, see


It’s probably much more efficient, but I’m not sure how portable it is.