PHP PDO MYSQL JOIN

Question

I have a table called cms_page in which each page has an ID. I want to pull all the information from this table and all the information from cms_page_part table where page_id is equal to the ID i send to the page...confusing i know but here is my attempt:

require '../../config.php';
$conn = new PDO(DB_DSN, DB_USER, DB_PASS);

$id = (int)$_GET['id'];
//$q = $conn->query("SELECT * FROM cms_page WHERE id=$id"); 
$q = $conn->query("SELECT cms_page.id, cms_page.title, cms_page.slug, cms_page_part.* FROM cms_page LEFT JOIN cms_page_part ON cms_page_part.page_id=cms_page.id WHERE cms_page.id = $id"); 

$project = $q->fetch(PDO::FETCH_ASSOC);

Any help figuring out how to join these would be appreciated.

William · Accepted Answer · 2011-06-28 16:31:55Z

2

How to JOIN the two tables:

SELECT id, title, slug, cms_page_part.* FROM cms_page JOIN cms_page_part ON cms_page.id = cms_page_part.page_id WHERE cms_page.id=$id

Example of what I said in my comments:

// Connect to database
$conn = new PDO(DB_DSN, DB_USER, DB_PASS);

// Page ID
$id = (int) $_GET['id'];

// Fetch page
$pageStmt = $conn->query(sprintf('SELECT * FROM cms_page WHERE id=%d', $id));
if ($pageStmt->fetchColumn() != 0) {
    $page = $pageStmt->fetch(PDO::FETCH_ASSOC);

    // Fetch parts
    $partsStmt = $conn->query(sprintf('SELECT * FROM cms_page_parts WHERE page_id=%d', $id));
    while ($part = $partsStmt->fetch(PDO::FETCH_ASSOC)) {
        // ...
    }
    $partsStmt->closeCursor();
}
$pageStmt->closeCursor();

Example (with prepared statements):

// Connect to database
$conn = new PDO(DB_DSN, DB_USER, DB_PASS);

// Page ID
$id = (int) $_GET['id'];

// Fetch page
$pageStmt = $conn->query('SELECT * FROM cms_page WHERE id=:id');
if ($pageStmt->execute(array(':id' => $id))) {
    if ($pageStmt->fetchColumn() != 0) {
        $page = $pageStmt->fetch(PDO::FETCH_ASSOC);

        // Fetch parts
        $partsStmt = $conn->query('SELECT * FROM cms_page_parts WHERE page_id=:id');
        if ($partsStmt->execute(array(':id' => $id))) {
            while ($part = $partsStmt->fetch(PDO::FETCH_ASSOC)) {
                // ...
            }
            $partsStmt->closeCursor();
        }
    }
    $pageStmt->closeCursor();
}

edited Jun 28, 2011 at 16:31

answered Nov 26, 2009 at 21:24

William

15.6k8 gold badges39 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Andy Over a year ago

the one i slashed out brings me the title nicely but the errors are supressed on my page and i dont know how to turn them back on :(

William Over a year ago

Just so I got this right, both tables has a field named page_id, correct? And the page ID you're testing this out on, has a row in both tables with the same page_id?

Andy Over a year ago

no sorry...in cms_page its "id" and in cms_page_part its "page_id"

Andy Over a year ago

I just updated my original post. THis works :@) But i have another issue. in cms_page_part there are different "parts" with the same page id. Do you know how i can access each one specifically? Thanks for your help.

William Over a year ago

Johan: I updated the answer with a PDO example. I also added a prepared statements as I feel you're getting PDO and prepared statements confused.

|

Johan · Accepted Answer · 2011-06-28 15:12:04Z

0

$q = $conn->query("SELECT cms_page.id
                    , cms_page.title
                    , cms_page.slug
                    , cms_page_part.* 
                   FROM cms_page 
                   LEFT JOIN cms_page_part ON cms_page_part.page_id = cms_page.id 
                   WHERE cms_page.id = '$id'");

edited Jun 28, 2011 at 15:12

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Nov 26, 2009 at 23:07

Andy

3,0319 gold badges45 silver badges64 bronze badges

1 Comment

Shaokan Over a year ago

@Johan this is actually PDO, it's just not written properly. PDO has indeed a method called query. php.net/manual/en/pdo.query.php

Collectives™ on Stack Overflow

PHP PDO MYSQL JOIN

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related