PDO way of call a DB value

[joon1]

<?php

declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1'); 
$urlTime = microtime(true);   

 $myDB = "myDB"; 

$options = [
    PDO:: ATTR_ERRMODE                                        => PDO:: ERRMODE_EXCEPTION,
    PDO:: ATTR_DEFAULT_FETCH_MODE          => PDO:: FETCH_ASSOC,
    PDO:: ATTR_EMULATE_PREPARES                 => false,
]; 

try { 

$dbc = new PDO("mysql:host=localhost;dbname=$myDB;charset=utf8", "root", "*********", $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 

$stmt = $dbc->prepare('SELECT myNumeric FROM myTable WHERE id = ?');
$stmt->execute([1]);
$myNumeric = $stmt->fetch();

echo $myNumeric;

I made the code above for get a value with SELECT SQL.
but it produces the following.

Notice : Array to string conversion in test.php on line 27
Array

I guess the last part of the code “echo $myNumeric;” is not correct.
I don’t know the correct PDO way.
How can I fix it?

[chorn]

@joon1

echo $myNumeric;

look at it and decide what to do: var_dump($myNumeric);

[droopsnoot]

@joon1

I don’t know the correct PDO way.

It’s nothing to do with PDO - as far as I know (from limited usage) mysqli would behave the same way, as would the old mysql_ functions.

I suspect what may be confusing you is that you only requested one column from the query, but it still comes back as an array when you use fetch(). You could use fetchColumn() when you’re only retrieving a single value, and then it wouldn’t return an array.

@joon1

I guess the last part of the code “echo $myNumeric;” is not correct.

There’s no need to guess. The error message tells you what line the error is on, so you can be pretty sure that is the line causing the problem. The message “Array to string conversion” makes it pretty clear that it’s having to convert an array to a string to do whatever you are doing on that line, which in this case is just trying to echo it. The next line “Array” is what you get when you try to echo an array in PHP - PHP just converts your array (for display purposes) to a string containing the word “array”, so you do at least get something out of your echo.

[ahundiak]

This is a bit off-topic but once you have figured out how to get your numeric value then it might be worthwhile to run the query again with ATTR_EMULATE_PREPARES set to true or even just commented out. There is a subtle but important distinction between the values returned.

[spaceshiptrooper]

It has very little to do with PDO. @droopsnoot and @chorn are right. It’s an array conversion problem. The thing I like about PHP’s errors and warnings is that PHP makes it extremely obvious as to why you’re getting those errors. This typically means it’s a developer problem. Just like in any other language, there are strict rules to fiddling with data types. Even though a lot of people think PHP is a loosely typed language, it still has certain restrictions on what you can and can’t do.

That being said, if you want to properly output each individual result, you have to run it through a loop or else use some kind of output function that accepts arrays. Both echo and print accepts only strings. var_dump and print_r and various other functions will allow you to pass in arrays, but will output the entire result as an array list.

[SamA74]

@joon1

PDO:: ATTR_DEFAULT_FETCH_MODE => PDO:: FETCH_ASSOC,

PHP: PDOStatement::fetch - Manual

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

You are trying to echo an array…

[joon1]

@droopsnoot

You could use fetchColumn() when you’re only retrieving a single value

The code2 which is modified by your suggestion produces my target result2 correctly.

code2

$stmt = $dbc->prepare('SELECT myNumeric FROM myTable WHERE id = ?');
$stmt->execute([1]);
$myNumeric = $stmt-> fetchColumn();
echo $myNumeric;

target result2
1111

The code3 below produces my target result3 correctly.
code3

$stmt = $dbc->prepare('SELECT myString FROM myTable WHERE id = ?');
$stmt->execute([1]);
$myString = $stmt-> fetchColumn();
echo $myString;

target result3
aaaa

The code4 below produces the result4 instead of my target result4.
code4

$stmt = $dbc->prepare('SELECT myNumeric, myString FROM myTable WHERE id = ?');
$stmt->execute([1]);
$myNumeric = $stmt-> fetchColumn();
$myString = $stmt-> fetchColumn();

echo $myNumeric. ' ' .$myString;

the restult
1111

target result4
1111 aaaa

How can I get my target result4?

The code5 below is one of my trials for getting my target result4.
code5

$stmt = $dbc->prepare('SELECT myNumeric, myString FROM myTable WHERE id = ?');
$stmt->execute([1]);
$myNumeric = $stmt-> fetchColumn(myNumeric);
$myString = $stmt-> fetchColumn(myString);

But it produces Warning and Fatal error.
The code6 below is another trials for getting my target result.
code6

$stmt = $dbc->prepare('SELECT myNumeric, myString FROM myTable WHERE id = ?');
$stmt->execute([1]);
$myNumeric = $stmt-> fetchColumn(1);
$myString = $stmt-> fetchColumn(2);

result6
aaaa

target result
1111 aaaa

How can I write the code for getting my target result,?

[ahundiak]

You seem intent on making this so much harder than it has to be.

$stmt = $dbc->prepare('SELECT myNumeric, myString FROM myTable WHERE id = ?');
$stmt->execute([1]);

$row = $stmt->fetch();
$myNumeric = $row['myNumeric'];
$myString = $row['myString'];

[John_Betong]

When developing I use the following function to display the returned results of PHP functions and class Methods:

//========================================
function vd
(
	$val =	'NO $val ???', 
	string $title = "Yes we have NO title"
)
:string 
{
	$style= <<< ____TMP
		<dl style=" 
			width: 88%; 
			margin: 2em auto; 
			background-color: #ffe; color:#00a; 
			padding: 0.88em;
			border: dotted 2px red;
			text-align: left;
			">
			<dt> $title </dt>
			<dd>
____TMP;

	echo $style;
		echo '<pre>'; var_dump($val); echo '</pre>';
	echo '</dd></dl>';	

	return ''; // NOT USED	
}

// USAGE:
$ok = $stmt->execute([1]);
vd($ok); 
vd($stmt); 

The function has it’s own file and called with require ‘vd.php’;

BEWARE:

If on the off-chance you are not familiar with PHP print_r() and var_dump() functions they have the advantage of displaying every type of variable whereas echo FAILS when trying to output arrays and objects.

[joon1]

@ahundiak

You seem intent on making this so much harder than it has to be.

Thank you. It works fine.

[droopsnoot]

@joon1

The code4 below produces the result4 instead of my target result4.

If you read this

@droopsnoot

You could use fetchColumn() when you’re only retrieving a single value, and then it wouldn’t return an array.

I’ve highlighted the significant part.

In your example code4, you are retrieving two columns (that is, your query will return two columns), so you clearly cannot use the function that is only useful to retrieve a single column. When you have more than one column, you retrieve them as an array, as you were originally doing, and then use the contents of that array.