CODEWITHSUNDEEP
phpmysqlpdofetch
denski
denski

Reputation: 2040

Array instead of a single item in PHP

This is giving me an array with two items though the query output should be a single cell, it's possibly obvious, but not to a beginner like me

$db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 

function dbFetch($db,$query)
    {
        $stmt = $db->prepare($query); 
        $stmt->execute(); 
        return $stmt->fetch();
    }

$query = " 
SELECT
Name
FROM Players WHERE PlayerID='21'"; 
$rows = dbFetch($db,$query);
var_dump($rows);

Output:

array(2) { ["Name"]=> string(6) "PLAYERNAME" [0]=> string(6) "PLAYERNAME" }

It's repeating the output first as associate and secondly as a numeric.

Upvotes: 1

Views: 71

Answers (2)

miken32
miken32

Reputation: 42699

Try using PDOStatement::fetchColumn() instead. It will return the first column from the first record.

function dbFetch($db,$query)
{
    $stmt = $db->prepare($query); 
    $stmt->execute(); 
    return $stmt->fetchColumn(0);
}

Also, I'd be willing to bet you aren't searching for "21" but rather a variable. In that case you'd be much better off using prepared statements, and modifying your code like so:

$db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 
function dbFetch($db,$query,$params=[])
{
    $stmt = $db->prepare($query); 
    $stmt->execute($params); 
    return $stmt->fetch();
}

$query = " 
SELECT
Name
FROM Players WHERE PlayerID=?";
$params = array("your user id"); 
$rows = dbFetch($db,$query,$params);
var_dump($rows);

Upvotes: 1

TopCheese
TopCheese

Reputation: 220

Probably due to the fetch style.

As per php manual:

public mixed PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] ) Fetches a row from a result set associated with a PDOStatement object. The fetch_style parameter determines how PDO returns the row.

fetch_style Controls how the next row will be returned to the caller. This value must be one of the PDO::FETCH_* constants, defaulting to value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH).

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method

PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class, and calling the constructor afterwards, unless PDO::FETCH_PROPS_LATE is also given. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.

PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class

PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed

PDO::FETCH_NAMED: returns an array with the same form as PDO::FETCH_ASSOC, except that if there are multiple columns with the same name, the value referred to by that key will be an array of all the values in the row that had that column name

PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

PDO::FETCH_PROPS_LATE: when used with PDO::FETCH_CLASS, the constructor of the class is called before the properties are assigned from the respective column values.

Upvotes: 1

Related Questions