How to get numeric types from MySQL using PDO?

Viewed 78 times

I'm using PDO and MySQL, for some reason when getting values from the database that are int type, the PDOStatement is returning a string representation of the number and not a value of numeric type. How do I prevent this from happening?

I noticed there is a attribute of the PDO class: PDO::ATTR_STRINGIFY_FETCHES that is supposed to take care of this but, when trying to modify it, it throws an error saying the attribute is not valid for MySQL driver.

Is it normal to get strings instead of numbers when consulting a database?

Answers

I don't think having "numbers" can be done in PHP 5.2 :-(

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

Well, after more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

It says this (quote) :

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not old libmysql)), and seems to only be the case for prepared statements :-(

Sorry...

A solution would be to have, on the PHP-side, a mapping-system (like an ORM -- see Doctrine ; just as an example of ORM : I don't know if it does what you're asking) to convert results coming from the DB to PHP datatypes...

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

Wednesday, August 24, 2022

dzm-de9043e912e0

You need to paramaterize the imageid value and bind the parameter to PDO::PARAM_LOB:

$sql = "SELECT image FROM image WHERE imageid=:id";
$query = $db_conn->prepare($sql);
$query->execute(array(':id' => $image_id));

$query->bindColumn(1, $image, PDO::PARAM_LOB);
$query->fetch(PDO::FETCH_BOUND);
header("Content-Type: image");
echo $image;

Of course, you'll also want to specify the complete, correct content type (e.g., image/png).

Monday, October 17, 2022

c._feenstra

The following works for me:

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_NULL);

$stmt->execute();

Pass in PHP's null, with type of PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.

Sunday, August 7, 2022

mohammad_reza_khahani

lastInsertId() is a method of the PDO class, not the PDOStatement class.

This should work:

$groupID = $dbo->lastInsertId();

Friday, November 18, 2022

jon_rimmer

SELECT DISTINCT fieldName FROM tableName;

The following query will only select distinct 'zip' field.

SELECT DISTINCT zip FROM student;

SELECT * FROM tableName GROUP BY fieldName;

The following query will select all fields along with distinct zip field.

SELECT * FROM student GROUP BY zip;

Sunday, October 23, 2022

mjbommar

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

pdo

How to get numeric types from MySQL using PDO?

Answers

php,mysql,pdo