CODEWITHSUNDEEP
phpmysqljoinpdo
Walrus
Walrus

Reputation: 20444

MySQL Join PHP PDO as sub object

Looking for a way to output a PHP object as intended with one database call rather than two.

First table is pages second meta.

I want to apply the meta of each page to the pages object. Currently I can do this either by performing an additional database call per pages record or with some very long winded for each function in PHP.

The output I'm after is $pages->meta->{contains all the meta for that page} . Is there a way to do this with one database call and output the data in this format.

Included is my fetchAll PHP function:

function fetchAll($table,$fields,$where,$order){

    global $db;

    $sql = "SELECT $fields FROM $table";

    if($where){
        $sql .= " WHERE $where";
    }

    if($order){
        $sql .= " ORDER BY $order";
    }

    $stmt = $db->prepare($sql);

    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_OBJ);

}

Then

 $pages = fetchAll("pages","*","type = 'example'");

 foreach($pages as $page){

     $page->meta = fetchAll("meta","text,lang,tag", "pages_ID = '$page->ID'");
 }

Table Structure

pages

ID | type | uri |

meta

ID | pages_ID | type | tag | lang | text

Upvotes: 0

Views: 893

Answers (1)

user7941334
user7941334

Reputation:

  • Pass db as argument where it's needed, don't use it as global.
  • Pass to fetch functions only the db connection, the sql statement and, eventually, a bindings array containing values to bind to the statement parameters. See PDOStatement::bindValue.
  • Keep operations simple. If you use them like you did, then you are going in the query builder direction. Because you don't have one, then I recommend to build sql statement beforehand and pass it to the needed functions - in your case here fetchAll(). In all PHP MVC projects/frameworks, you'll see that sql statement are built with query builders and passed as a whole to all CRUD functions.

Here is the fetchAll() php function:

/*
* Don't use any globals in your codes.
* Open your db connection before calling fetchAll()
* and pass it as argument, for example.
*/
function fetchAll($db, $sql, $bindings = array()){
    $stmt = $db->prepare($sql);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

And here is your further php code:

// Get all pages (to which meta data exists) and,
// for each of them, the meta details.
$sql = 'SELECT
            pg.*, 
            mt.text,
            mt.lang,
            mt.tag
        FROM pages AS pg
        LEFT JOIN meta AS mt ON mt.pages_ID = pg.ID
        WHERE 
            pg.type = "example"
            AND mt.ID IS NOT NULL';

// Fetch all meta data from db.
$pagesAndMeta = fetchAll($db, $sql);

Feel free to ask further. Good luck!

EDIT 1 (final):

I tested on my db and I decided to give you only one (final) version of the sql statement to use (I deleted the other one). I also included the further filtering in the WHERE clause. It should work smoothly now.

And I also changed $page->meta to $pagesAndMeta.

And, since the db call totally changed, then i sugest you to build $pages and $page->meta objects from the new db call results, how you like, through looping.

Upvotes: 2

Related Questions