PHP match subject table and page table

Question

I am relatively new to php. I have a function to get all subjects by tableName. I also have a function where I am trying to get all pages where the subject_id (from pages table) matches the subject tables id.

Here are the error's and output:

PDOStatement Object ( [queryString] => SELECT * FROM subjects ORDER BY id ASC )

Array ( [0] => Array ( [id] => 1 [0] => 1 [menu_name] => About Us [1] => About Us [position] => 1 [2] => 1 [visible] => 1 [3] => 1 ) [1] => Array ( [id] => 2 [0] => 2 [menu_name] => Products [1] => Products [position] => 2 etc etc...

Notice: Undefined index: id in /Users/aaronhappe/Sites/php/CMS/includes/db.php on line 39

Fatal error: Call to a member function rowCount() on a non-object in /Users/aaronhappe/Sites/php/CMS/includes/db.php on line 41

And here is the code:

<?php

// controller file

include '../admin-head-includes.php';

$subjects = getAll('subjects', $conn);

$subjectId = $subjects->fetchAll();

print_r($subjects);
echo "<br>";
echo "<br>";
print_r($subjectId);

$pages = getAllLinkedId('pages', $subjectId, $conn);

view('manage-content', array(
    'subjects' => $subjects,
    'pages'    => $pages,
));

// from my db functions file

function getAllLinkedId($tableName, $tableName2, $conn) {

try {
$result = $conn->query("SELECT * FROM $tableName WHERE subject_id = {$tableName2['id']} ORDER BY id   ASC");

    return ($result->rowCount() > 0)
    ? $result
    : false;

} catch (Exception $e) {
    return false;
}

<!-- standard functions file -->

<?php function view($path, $data = null) {
    if ($data) {
        extract($data);
    }
    $path = $path . '.views.php';
    include "views/$path";
}?>

If I pass $subjects[0] into my getAllLinkedId function, it solves the problem of the Array wrapping other arrays. However, what I am wanting is to spit out, for each subject, the corresponding page. Not just the pages which correspond to one single subject, but which respond to all subjects.

This is my view:

<div class="subjects">
    <ul>
<?php

foreach ($subjects as $subject) {?>
<li>
<?=$subject['menu_name'];?>
</li>
    <ul>
    <?php foreach ($pages as $page) {?>
        <?=$page['menu_name']?>
    <?php }?>
    </ul>

<?php }?>
</ul>

</div>

Whatever `$conn->query()` is, it hasn't returned an object with that method. I'm guessing that's because `$subjectId` references an array of arrays, not an integer, so there's either a query syntax error (seemingly indicated by the Notice right above it), or there is no result. — Jared Farrish, Sep 21 '14 at 15:37
If you have a close look at your `$subjectId` array you will see that it is an array of arrays and only numerical keys. Probably you want to use one single value of `$subjectId` like `$subjectId[0]`. — VMai, Sep 21 '14 at 15:42
VMai - Yes, that is close to what I want. Only I want the "[0]" to be dymanic (something like $subject[i]) so that in my view I can spit out each page that corresponds to each subject. Passing in $subject[0] will only spit out pages that belong to $subject[0]. — AaronHappe, Sep 21 '14 at 16:00
The generated sql would then look like `WHERE subject_id IN (1,2,...)`. How it's done with PDOs parameterized prepared statements (it's a very good idea to use them), see http://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array — VMai, Sep 21 '14 at 16:26

score 0 · Answer 1 · answered Sep 21 '14 at 15:51

First problem:

Notice: Undefined index: id in ...

the index id in array $tableName2 is not set, check if is set

Second problem:

Fatal error: Call to a member function rowCount() on a non-object ..

$conn->query() on fails return a non-object, check if $result is a object

In your code:

function getAllLinkedId($tableName, $tableName2, $conn) {

    // check if index id is set
    if( !isset($tableName2['id']) )
        return false;

    try {
        $result = $conn->query("SELECT * FROM $tableName WHERE subject_id = {$tableName2['id']} ORDER BY id ASC");

        // check if $result is object
        if( !is_object($result) )
            return false;

        return ($result->rowCount() > 0) ? $result : false;

    } catch (Exception $e) {
        return false;
    }
}

score 0 · Accepted Answer · answered Sep 21 '14 at 18:47

I was way off base on this. But I have figured it out. I am posting my solution to close this.

<?php

// controller file

include '../admin-head-includes.php';

$subjects = getAll('subjects', $conn);
$pages    = getAll('pages', $conn);

$subjects = $subjects->fetchAll();
$pages    = $pages->fetchAll();

view('manage-content', array(
    'subjects' => $subjects,
    'pages'    => $pages,
));

<!-- what my view function does -->

<?php function view($path, $data = null) {
if ($data) {
    extract($data);
}
$path = $path . '.views.php';
include "views/$path";
}?>

<!-- view -->
<div class="subjects">
<ul>
<?php

foreach ($subjects as $subject) {?>
<li>
<?=$subject['menu_name'];?>
<ul>
<li>
<?php foreach ($pages as $page) {?>
<ul>
    <li>
<?php
if ($page['subject_id'] == $subject['id']) {
    echo $page['menu_name'];
}
    ?>
</li>
</ul>
<?php }?>
</li>
</ul>

<?php }?>
</li>
</div>
</ul>

PHP match subject table and page table

2 Answers2