1

So originally I used mysqli to connect to the database but now I'm attempting to implement PDO.

Previously when using mysqli I'd do something like this:

$sql = "select * from companies";

$statement = $this->conn->prepare($sql);
$statement->execute();
$result = $statement->get_result();
$companies = $result->fetch_assoc();

and this would fetch the associative values. Phone numbers would be stored as varchar inside mysql so the phone number would return as a string as apposed to a number.

Now, when I try to use PDO like so:

$sql = "SELECT * FROM companies";

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->query($sql);
    $companies = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($companies);

} 
catch (Exception $ex) {
    echo '{"error": {"text": '.$ex->getMessage().'}';
}

all values would be returned as strings.

I tried echo json_encode($companies, JSON_NUMERIC_VALUE); but I have issues with postal codes and phone numbers starting with 0, where the 0 just dissapears off of the front.

I thought about looping through the values and assigning the necessary type to the value, but it seems a little long winded.

luke
  • 2,743
  • 4
  • 19
  • 43
  • just simply convert postal code to string to avoid that problem – Beginner May 08 '17 at 09:51
  • *"all values would be returned as strings."* - Is that a problem? Both PHP and JavaScript are loosely typed - there shouldn't be any issues with everything being strings. – CD001 May 08 '17 at 09:52
  • @CD001 I'd much rather convert them to the necessary types before they are used inside my mobile application, as I need to convert them anyway. – luke May 08 '17 at 09:55
  • @Beginner how is that going to help? I'm converting it to a string from a string then the use of `JSON_NUMERIC_VALUE` will convert it to an int... – luke May 08 '17 at 09:57
  • `json_encode` *shouldn't* wrap integers/floats in quote marks anyway; postcodes and phone numbers *are* strings (e.g. '1017 PH' or '+31 20 123 1234') - so what data have you got that *has* to be a number? – CD001 May 08 '17 at 10:00
  • @CD001 latitude and longitude values which are doubles, as well as id value which is an int. – luke May 08 '17 at 10:03
  • Surely latitude and longitude are strings as well, since you have to know whether they're N/S or E/W? ... but anyway, you *can* tell PDO not to "stringify" everything by setting the `ATTR_EMULATE_PREPARES` option in the constructor to `false` - then it'll use types from database : http://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo – CD001 May 08 '17 at 10:22

0 Answers0