[RESOLVED] Row 10 no displaying

[chrisguk]

I hopefully seem to be getting somewhere from my last post about creating a database script. But now I seem to be getting an error with the following:

Notice: Undefined offset: 10 in /var/www/mysite/include/config.php on line 126

So its this line 126 that the output will not produce:

 $price = $col[10];

I ran the query in phpmyadmin to make sure it was working:

SQL result

Host: localhost
Database: mydb
Generation Time: May 14, 2013 at 04:56 PM
Generated by: phpMyAdmin 3.5.8.1deb1 / MySQL 5.5.31-0ubuntu0.13.04.1
SQL query: SELECT * FROM packages WHERE service = 'books' LIMIT 1;
Rows: 1

service plan title author descr type language paperback currency price
books weekly a b c d e f g h

Here is the code:

$book_query = ("SELECT * FROM packages WHERE service = ? LIMIT 1");
if(!$book_stmt = $pdo->prepare($host_query)){
    // prepare failed
    echo "<pre>Prepare failed:\n";
    print_r($pdo->errorInfo());
    echo "</pre>";
} else {
    if(!$book_stmt->execute(array('books'))){
        // execute failed
        echo "<pre>Execute failed:\n";
        print_r($book_stmt->errorInfo());
        echo "</pre>";
    } else {
        // query ran without any errors, you can test if it returned any rows and use them here

}
}
// Database  

if ($book_stmt->rowCount() > 0) {
    while ($col = $book_stmt->fetch())
    {
    $title = $col[3];
    $author = $col[4];
    $descr = $col[5];
    $type = $col[6];
    $language = $col[7];
    $paperback = $col[8];
    $currency = $col[9];
    $price = $col[10];


}

} else {

$title = 'no data';
$author = 'no data';
$descr = 'no data';
$type = 'no data';
$language = 'no data';
$paperback = 'no data';
$currency = 'no data';
$price = 'no data';

}

What can I do to correct this?

[bradgrafelman]

Few things:

1. Your thread title is a little misleading; you're not having a problem accessing rows, it's the columns you're talking about.

2. It should make sense that $col[10] is undefined; this:

   chrisguk;11028131 wrote:

   service plan title author descr type language paperback currency price

   suggests that there are only 10 columns in the table.

3. Say you're browsing along in your code one day and you see yourself using $col[6]. Do you immediately know what that piece of data is? Probably not, unless you've also go your table schema handy and start counting columns. Plus, what happens if you or someone else changes the schema and inserts a column somewhere other than at the end?

   The point I'm getting at is: don't use the numerical indexes. Use the associative indexes so that it's clear what data you're referring to. In fact, at that point, it's probably so clear that there's no point in needlessly duplicating $col['title'] in a variable called $title.

4. You've got a LIMIT 1, so there's no need for a while() loop.

[NogDog]

It would seem to imply that your DB table being queried has less than 11 columns. (Remember that the first column would be index 0.)

This is one of several reasons I prefer to fetch results as either an associative array or an object, so that you specify elements in the retrieved row by name instead of number. This both avoids human errors (if we agree that programmers are humans), and changes to the order of the columns in the table definition won't affect your PHP code -- though if you always explicitly select the desired columns instead of using "*", this latter issue becomes less of a problem.

[chrisguk]

@ - you hit the nail on the head, I forgot it started from 0 therefore it is now working.

@ - your point about associative arrays make sense and it is easy to understand how that can make life easy. I just need to figure out how to implement that will my elementary knowledge based on my example.

Thank you to both of you with your continued help so far.

[NogDog]

To do an associative fetch in PDO:

while ($col = $book_stmt->fetch(PDO::FETCH_ASSOC)) {

[Bonesnap]

I learned while using numerical indexes, and then one day I was exposed to associative indexes and my life changed forever.