Can't we use other than fetch method we set as default in ATTR_DEFAULT_FETCH_MODE?

Question

I Set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, as default in connection and now I am having problem with fetchAll problem.

The problem I am having is :

Notice: Undefined offset: 0 in C:\wamp\www\a\test.php on line 59

And line 59 is :

if($sql->fetchAll()[0][0] !== '0' && $level !== 0){

Here is my connection type:

$host = 'localhost';
$db   = 'demo';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
);
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

And Here is the query I am trying : if I remove ATTR_DEFAULT_FETCH_MODE it works fine, is there a way to use both ?

function bootstrap_menu($pdo, $parent_id, $level = null) {
    $stmt = $pdo->prepare("SELECT * FROM categories WHERE parent_id =:parent_id");
    $stmt->bindParam(":parent_id", $parent_id, PDO::PARAM_INT);
    $stmt->execute();
    foreach ($stmt->fetchAll() as $row) {
        $sql = $pdo->prepare("SELECT count(*) FROM categories WHERE parent_id =:cat_id");
        $sql->bindParam(":cat_id", $row['cat_id'], PDO::PARAM_INT);
        $sql->execute();
        if($sql->fetchColumn()[0][0] !== '0' && $level !== 0){  
            echo "<li class=\"nav-item dropdown\">\n";
            echo "<a class=\"nav-link dropdown-toggle\" href=\"".htmlspecialchars($row['seo_url'])."\" id=\"navbarDropdown\" role=\"button\" data-toggle=\"dropdown\"
                    aria-haspopup=\"true\" aria-expanded=\"false\">\n";
            echo $row['cat_name'];
            echo "</a>\n";
            echo "<ul class=\"dropdown-menu\" aria-labelledby=\"navbarDropdown\">\n";
            bootstrap_menu($pdo, $row[0], $level - 1);
            echo "</ul>\n";
            echo "</li>\n";
        }elseif($level == 0){
            $my_class = htmlspecialchars($row['cat_name']);         
            if($my_class == 'Ana Sayfa'){
                echo "<li class=\"nav-item active\">\n";
                echo "<a href=\"".htmlspecialchars($row['seo_url'])."\" class=\"nav-link \">\n";
                echo htmlspecialchars($row['cat_name']);
                echo "</a>\n";
                echo "</li>\n";
            }else{
                echo "<li class=\"nav-item\">\n";
                echo "<a href=\"".htmlspecialchars($row['seo_url'])."\" class=\"nav-link\">\n";
                echo htmlspecialchars($row['cat_name']);
                echo "</a>\n";
                echo "</li>\n";
            }
        }
        else { 
            echo "<li class=\"dropdown-item\">";
            echo "<a href=\"".htmlspecialchars($row['seo_url'])."\" class=\"dropdown-item\">\n";
            echo htmlspecialchars($row['cat_name']);
            echo "</a>\n";
            echo "</li>\n";
        }
        unset($sql);
    }
    unset($stmt);
}

Did you consider the possibility to use a different fetch method, other than fetchAll()? — Your Common Sense, Nov 02 '19 at 11:52
Besides, instead of two queries it should be a single query with a GROUP BY clause — Your Common Sense, Nov 02 '19 at 11:53
You are not making any attempt to override the default fetch mode set in the constructor, that explains why it isn't happening (see [Dharman's answer](https://stackoverflow.com/a/58671171/13508)) for that. You're also assuming that the secondary query can never return zero rows (i.e., that all first level categories necessarily have second level subcategories, if I understood correctly). — Álvaro González, Nov 02 '19 at 12:46
sorry I was away I tried `if($sql->fetchColumn()[0][0] !== '0' && $level !== 0){` is well then it gives another error **Notice: Undefined offset: 0 in line 66** `bootstrap_menu($pdo, $row[0], $level - 1);` — , Nov 02 '19 at 13:17
As I said in my answer `$sql->fetchColumn()[0][0]` is wrong. You changed the code in the question and now I do not know where you are seeing what error. — Dharman, Nov 02 '19 at 14:26
No I didnt change the code, its the same code but I added full code in question, and the problem is the same, as I answered your question about `$row[0]` too in your coment for your answer. your answer is right for general usage pdo fetchColumn, but not an answer for my question, this is the original post same as mine https://stackoverflow.com/questions/40365952/dynamic-menu-php-bootstrap-mysql — , Nov 02 '19 at 15:04

Dharman · Accepted Answer · 2019-11-02T13:25:28.453

1

$sql->fetchAll()[0][0] means simply first column of the first row. There already is a method for that in PDO fetchColumn().

You just need:

if($sql->fetchColumn() !== '0' && $level !== 0)

or even better

if($sql->fetchColumn() && $level !== 0)

If for whatever reason you still want to use fetchAll() then you can pass the fetch style as an argument. e.g. $sql->fetchAll(\PDO::FETCH_BOTH)

Also in the outer loop foreach ($stmt->fetchAll() as $row) is not really needed. Simply foreach ($stmt as $row)

edited Nov 02 '19 at 13:25

answered Nov 02 '19 at 12:36

Dharman

30,962
25
85
135

Sorry I was away fetchAll part is not in foreach its in an if statement I tried `if($sql->fetchColumn()[0][0] !== '0' && $level !== 0){` is well then it gives another error **Notice: Undefined offset: 0 in line 66** `bootstrap_menu($pdo, $row[0], $level - 1);` – Nov 02 '19 at 13:20
You don't need `[0][0]`. Just `if($sql->fetchColumn() !== '0'` or even better `if($sql->fetchColumn() && $level !== 0` – Dharman Nov 02 '19 at 13:21
tought that is checking for parent id ? let me try that way – Nov 02 '19 at 13:23
I tried both solutions in your comment and having error with both solutions for child cats : Notice: Undefined offset: 0 in line 66. Which is `bootstrap_menu($pdo, $row[0], $level - 1);` – Nov 02 '19 at 13:33
What is `$row[0]`? – Dharman Nov 02 '19 at 13:34
The item is a parent, in case we call the Menu function with the item id `$row[0]` as a parent and `$level - 1` to make sure to stop when reach the end of levels. – Dharman – Nov 02 '19 at 14:10

Can't we use other than fetch method we set as default in ATTR_DEFAULT_FETCH_MODE?

1 Answers1