PDO fetchAll wont fetch last column

Question

When I use fetchAll to get an entire column into an array my code does fetch the first two columns and puts these into an array. However when I try to do this with the final third column it gives me an blank array my code is as follows.

 <?php

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test"; // Table name 

// Connect to server and select databse.
$con = new PDO('mysql:host=localhost;dbname=test', $username, $password );
// username and password sent from form 

$sth = $con->prepare("SELECT range   FROM test");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

Now when we replace range for instead nr I get this output

Fetch all of the remaining rows in the result set: Array ( [0] => Array ( [nr] => 1 [0] => 1 ) [1] => Array ( [nr] => 2 [0] => 2 ) [2] => Array ( [nr] => 3 [0] => 3 ) [3] => Array ( [nr] => 4 [0] => 4 ) )

However when we leave range as it is we get

Fetch all of the remaining rows in the result set: Array ( )

Essential: http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858 — Your Common Sense, Apr 28 '14 at 11:32

score 1 · Accepted Answer · answered Apr 28 '14 at 11:30

1

The reason SELECT nr FROM test works and SELECT range FROM test doesn't is because range is a reserved keyword in MySQL. Surround it by backticks (`) in order to use reserved keywords in column names:

SELECT `range` FROM test
//     ^     ^ Nessesary when using reserved keywords

answered Apr 28 '14 at 11:30

h2ooooooo

39,111
8
68
102

1

Okay now I feel stupid for not knowing this thank you code works now – user3581123 Apr 28 '14 at 11:32
@user3581123 We only learn from our mistakes. Next time something similar happens you'll know what to check first. If the answer was helpful, make sure you accept it when you can (there's a 15 minute timer from when the question was posted till you're able to accept an answer). – h2ooooooo Apr 28 '14 at 11:33
Instead of surrounding it by backticks, consider changing the name altogether. It's a reserved word for a reason, and other people are undoubtedly gonna run into this same issue with this database schema. – Sherlock Apr 28 '14 at 11:34

PDO fetchAll wont fetch last column

1 Answers1