Can't get my head around this, this morning

[Paul-D]

function GetAllData() 
{
    $pdo = connectDB();
    $sqlLottoList = "SELECT * FROM EuroMillions ORDER BY Draw DESC";
    $stmt = $pdo->prepare($sqlLottoList);
    $stmt->execute();
    $stmt->fetch();     
    echo $stmt['N1'];     // Test      line 22          
    exit;
    return $stmt;    
}

Fatal error: Uncaught Error: Cannot use object of type PDOStatement as array in /vhost/d/e/s/desmond-otoole.co.uk/www/Lottery2/secure/LotteryFunctionsPDO.php:22

 

Should return tinyint(1) value 16 /* first row in table. */

Edited July 15 by Paul-D

[Phi11W]

What does the fetch() function return? 

I think you need something more like this:

$row = $stmt -> fetch(); 
echo( $row[ 'N1' ] ); 

 

Also, avoid using "select *" in Application code.  
Whilst you might not have a lot of columns in that table [yet], databases are inherently shared entities and you never know when someone [else] might add a dozen columns full of gigabytes of stuff that this query simply doesn't care about.  Always select just the columns that you specifically need. 

Regards,   Phill W. 

[Paul-D]

Okay thanks. I am the sole user of this and the table is relativly tiny. Holds all the euros from Feb 2004. Thanks though.

[Barand]

Your function is ony interested in the latest draw so why query for every draw since 2004, just LIMIT the query to the 1 row you need instead of fetchong all then discarding the 2,079 that are surplus to requirements. The speed of a query is proportional to the amount of data returned so keep it to the minimum required columns and rows.

SELECT N1
FROM EuroMillions 
ORDER BY Draw DESC
LIMIT 1;

 

[gizmola]

You got some great advice, as well as the answer, but to be very specific, here is your error:

Fatal error: Uncaught Error: Cannot use object of type PDOStatement as array in /vhost/d/e/s/desmond-otoole.co.uk/www/Lottery2/secure/LotteryFunctionsPDO.php:22

So just reading the error, should tell you -- where is it you are trying to use anything in that code as an array?

echo $stmt['N1'];  

So clearly, the problem is that, at runtime, $stmt was a PDOStatement Object.  And you can not use that object like it is an array in PHP (unlike javascript).

As Phi11w posted, that should tell you to look at the manual and determine what you might have done wrong.'

Here is the fetch function you utilized:  https://www.php.net/manual/en/pdostatement.fetch.php

If you read the manual page you want to concentrate on what is returned:  

The return value of this function on success depends on the fetch type. In all cases, false is returned on failure or if there are no more rows.

Looking at the manual, you'll note that because you passed no mode parameter:  PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set.

So you can make this code more efficient, by passing the parameter that tells PDO to only return an array with the keys being the column names.

While we are fixing this up, you should take the time to make this function actually usable for more than one query, by making the SQL statement a parameter.

function GetAllData($sql) 
{
    $pdo = connectDB();
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result;
}

And it should be obvious then that your function call will be:

$row = GetAllData('SELECT * FROM EuroMillions ORDER BY Draw DESC');

if ($row) {
   // Do something with your result arrray 
} else {
   // Result set was invalid
}

 

Going further, PHP has some commonly accepted code standards you've violated here.  It also appears you have hit upon the old adage that:

Quote

the two most difficult tasks in development are naming and cache invalidation.

 

So what I see here is that you have a function you called GetAllData, but it does not get all data.  At best it gets one row.  

The other issue, in terms of php coding standards is that you have violated PSR-1 by naming your function GetAllData which is in "Pascal case" where the first Letter of the function name is capitalized.

PSR-1 was extended in PSR-12 if you want to really embrace the standards used by the majority of professional PHP developers.

So... to complete the fixes to your function....

function getOneRow($sql) 
{
    $pdo = connectDB();
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result;
}

If you actually wanted a similar function to return all the rows/data:

function getAllRows($sql) 
{
    $pdo = connectDB();
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

This would return you an array of 0..n associated arrays.