SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: PDO MySQL JOIN

  1. #1
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO MySQL JOIN

    Hi all,

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

    Here's my code:

    PHP 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!
    No, I REALLY dislike having to use Joomla.

  2. #2
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it working.

    PHP Code:
    $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)
    Last edited by crowden; Jun 9, 2009 at 11:03.
    No, I REALLY dislike having to use Joomla.

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You might also consider using the heredoc format:
    PHP Code:
        $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. #6
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    You're executing the query twice.

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

    You don't need to use both.
    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?
    No, I REALLY dislike having to use Joomla.

  7. #7
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    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.
    Ah yes...duh. 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!!
    No, I REALLY dislike having to use Joomla.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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/...ion_found-rows

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •