PDO MySQL JOIN

PHP
1

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);
$sql->execute();
$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!

2

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);
$sql->execute();
$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)

3

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.

4

You’re executing the query twice.

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

You don’t need to use both.

5

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
EOT;
6

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?

7

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!!

8

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

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

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