Asked  7 Months ago    Answers:  5   Viewed   28 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

47

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, March 31, 2021
 
sholsinger
answered 7 Months ago
64

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. Thus, it works only as PDO connection option as well. So, set it up this way

$opt  = array(
    PDO::MYSQL_ATTR_FOUND_ROWS   => TRUE,
    // you may wish to set other options as well
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$this->_db = new PDO($dsn,DB_USER,DB_PASS,$opt);
Wednesday, March 31, 2021
 
shivam
answered 7 Months ago
14

Typescript now comes with a predefined Parameters<F> type alias in the standard library which is almost the same as ArgumentTypes<> below, so you can just use that instead of creating your own type alias.

type TestParams = Parameters<(a: string, b: number) => void> // [string, number]

Then to get for example the second parameter's type you can use the numeric indexing operator:

type SecondParam = TestParams[1] // number

Original answer:


Yes, now that TypeScript 3.0 has introduced tuples in rest/spread positions, you can create a conditional type to do this:

type ArgumentTypes<F extends Function> = F extends (...args: infer A) => any ? A : never;

Let's see if it works:

type TestArguments = ArgumentTypes<typeof test>; // [string, number]

Looks good. Note that these beefed-up tuples also capture things like optional parameters and rest parameters:

declare function optionalParams(a: string, b?: number, c?: boolean): void;
type OptionalParamsArgs = ArgumentTypes<typeof optionalParams>; 
// [string, (number | undefined)?, (boolean | undefined)?]

declare function restParams(a: string, b: number, ...c: boolean[]): void;
type RestParamsArgs = ArgumentTypes<typeof restParams>;
// [string, number, ...boolean[]]

Hope that helps. Good luck!

Monday, June 21, 2021
 
Manmay
answered 4 Months ago
61
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;
Monday, July 5, 2021
 
FWH
answered 4 Months ago
FWH
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :