[RESOLVED] Undefined offset: 0

[cluelessPHP]

I'm trying this but getting the error message Undefined offset: 0

<?php 

try{
    	   $db = new PDO("mysql:host=localhost;dbname=alphabet", 'root', '');
    	   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }  catch(PDOException $e)
    {
    		echo $e->getMessage();
    		die();
    }


function select(PDO $db)
{

    $alphabet = range("A", "Z");
    foreach ($alphabet as $letter)
    {
            $selector = $db->prepare("SELECT name FROM title", array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
            $selector->execute();   
            $fectching = $selector->fetch(PDO::FETCH_ASSOC);
            echo "<h3>$letter</h3>" . "<br>";


            for($i = 0; $i < count($fectching); $i++)
            {
                echo $fectching[$i]["name"] . "<br>";
            }
    }        
}    

select($db);

[NogDog]

Maybe the query returned zero rows?

[cluelessPHP]

Oh maybe it should be like this? It returns rows at least

function select(PDO $db)
{

    $alphabet = range("A", "Z");
    foreach ($alphabet as $letter)
    {
            $selector = $db->prepare("SELECT name FROM title", array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
            $selector->execute();   
            $fectching = $selector->fetchAll();
            echo "<h3>$letter</h3>" . "<br>";

            for($i = 0; $i < count($fectching); $i++)
            {
                echo $fectching[$i]["name"] . "<br>";
            }
    }        
}    

[pbismad]

Your (original) fetch statement is only fetching a single row and it is in the form of an associative array. There is no zeroth element in it.

Next, your program logic is preparing and executing the same exact query each pass through the loop. This makes no sense. There's almost never a case where you should run a SELECT query inside of a loop, because it is measurably slow due to the communications required for each query, you would instead run a single query that gets the data you want in the order that you want it, and if you do have a case to do this, you would prepare the query once, before the start of the loop, and only cause it to be executed inside the loop.

BTW - the default is to use buffered queries, so there's no reason to set that PDO attribute to true.

What exactly are you trying to accomplish?

[cluelessPHP]

Mostly to learn, if bad coding practice I won't do it

It was a variation on this, to see if I could to it another way

<?php 

try{
           $db = new PDO("mysql:host=localhost;dbname=alphabet", 'root', '');
           $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }  catch(PDOException $e)
    {
            echo $e->getMessage();
            die();
    }


function select(PDO $db)
{

    $alphabet = range("A", "Z");
    foreach ($alphabet as $letter)
    {
        try
        {
            $selector = $db->prepare("SELECT name FROM title", array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
            $selector->execute();   
            $fectching = $selector->fetchAll();
         echo "<h3>$letter</h3>" . "<br>";
        }
        catch(Exception $e)
        {
            echo "Message" . $e->getMessage();
        }
          foreach ($fectching as $selected)
            { 
                if(substr(ucfirst($selected["name"]), 0, 1) == $letter)
                {
                    echo $selected["name"] . "<br>";
                }
            }
    }        
}    

select($db);

[pbismad]

Whoever wrote the original logic in reply #5 was off their rocker. This code is preparing and executing the exact same query, that gets all the rows of data, 26 times. Then, for each pass through the outer loop, it loops over all the rows of data, which is the same set of data each time, and displays only the ones having the first letter that matches the current letter.

This is a bunch of verbose and resource wasteful logic that can be accomplished by adding ORDER BY name to one single query.

[cluelessPHP]

pbismad;11063037 wrote:

Whoever wrote the original logic in reply #5 was off their rocker.

Yep, I've been told that many times 😉

pbismad;11063037 wrote:

This code is preparing and executing the exact same query, that gets all the rows of data, 26 times. Then, for each pass through the outer loop, it loops over all the rows of data, which is the same set of data each time, and displays only the ones having the first letter that matches the current letter.

It won't run outside of the loop, I tried.

pbismad;11063037 wrote:

This is a bunch of verbose and resource wasteful logic that can be accomplished by adding ORDER BY name to one single query.

ORDER BY name
Will sort them alphabetically sure, but will it pair them to the corresponding heading letter?

[pbismad]

Will sort them alphabetically sure, but will it pair them to the corresponding heading letter?

You would do that when you display the results.

The easiest way would be to index/pivot the data using the first letter when you retrieve the data, storing the rows with the same 1st letter into a sub-array using the 1st letter as the main array index. Then, if you want to display all the heading letters, even if there is no data for it, loop through the letters and access the retrieved data, if any, for that letter. If you only want to display heading letters where there is data, just loop over the retrieved data.

To index/pivot the data, after you execute the single query -

$result = []; // define an array to hold the data
while($row =  $selector->fetch(PDO::FETCH_ASSOC))
// if you set the default fetch mode when you make the connection, you don't need to specify it each time
{
    $index = ucfirst($row["name"][0]); // get the 1st letter and upper case it
    $result[$index][] = $row;
}

To display all letters, even if no data -

foreach(range('A','Z') as $letter)
{
    echo "<h3>$letter</h3>" . "<br>";
    if(isset($result[$letter]))
    {
        foreach($result[$letter] as $row)
        {
            echo $row["name"] . "<br>";
        }
    }
}

To display only the data that exists -

foreach($result as $letter=>$rows)
{
    echo "<h3>$letter</h3>" . "<br>";
    foreach($rows as $row)
    {
        echo $row["name"] . "<br>";
    }
}

[cluelessPHP]

Ah, thanks I'll sit and go over this tonight and try different things with it, I'm not great with arrays which is why I'm focusing on them more now

[NogDog]

Not saying this is by any means the best way, but probably closer to how I might approach it:

function select(PDO $db)
{
    try
    {
        $selector = $db->prepare("SELECT name FROM title", array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
        $selector->execute();
        $data = array();
        while($row = $selector->fetch(PDO::FETCH_ASSOC)) {
            // strings can be treated sort of like arrays of characters in PHP
            $data[strtoupper(trim($row['name'][0]))][] = $row; // I hope those parens are right
        }
        $text = ''; // I don't like functions that directly output text, so we'll return it instead

    // now we have our data organized by first letter of name, so...
    foreach (range('A', 'B') as $letter)  // don't really need to store range as variable
    {
        $text .= "<h3>$letter</h3>" . "<br>";
        if(!empty($data[$letter]))
        {
            $text .= "<ul>\n";
            foreach($data[$letter] as $row)
            {
                $text .= "<li>{$row['name']}</li>\n";
            }
            $text .= "</ul>\n";
        }
    }
    return $text;
}
catch(Exception $e)
{
    echo "Message" . $e->getMessage();
}
}

[cluelessPHP]

Ah this is what's tripping me up, well that and one other thing

 $data = array(); 
        while($row = $selector->fetch(PDO::FETCH_ASSOC)) { 
            // strings can be treated sort of like arrays of characters in PHP 
            $data[strtoupper(trim($row['name'][0]))][] = $row; // I hope those parens are right 
        } 
        $text = ''; // I don't like functions that directly output text, so we'll return it instead