Array instead of a single item in PHP

Question

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.

You're getting both an associative and a numeric index. Just use one with $stmt->fetch(PDO::FETCH_ASSOC); — Jeff Puckett
– Jeff Puckett, Commented Feb 23, 2017 at 23:22

Community · Accepted Answer · 2023-11-17 19:22:09Z

1

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);

edited Nov 17, 2023 at 19:22

CommunityBot

11 silver badge

answered Feb 23, 2017 at 23:21

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

denski Over a year ago

Would you have an insight into why it creates two items?

Jeff Puckett Over a year ago

@denski the default fetch_style is to fetch both numeric and associative. PDO::FETCH_BOTH

denski Over a year ago

Hey, I am using prepared statements, just wanted to keep this as light as possible. many thanks.

denski Over a year ago

so in a function $params=[ ] makes the default [ ] if $params has no value? cool!

TopCheese · Accepted Answer · 2017-02-23 23:23:19Z

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.

Collectives™ on Stack Overflow

Array instead of a single item in PHP

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related