Why is my method only returning the first row of my table? I can't understand why and it's driving me nuts. I'm sure it's something very simple.
public function getTitlesForRegistrationForm() {
$result = $this->_db->query("SELECT UserTitleID, UserTitleName FROM UserTitles");
$i=0;
$array[0] = "No result";
foreach($result->fetch(PDO::FETCH_ASSOC) as $row){
$array[$i] = $row;
$i++;
}
return $array;
}
Thanks.
It may be because your $result->fetch() call doesn't return an iterable value, but either a result row or FALSE. PHP's foreach only works on iterable values. Updating your code to something like this should do the trick:
public function getTitlesForRegistrationForm() {
$result = $this->_db->query("SELECT UserTitleID, UserTitleName FROM UserTitles");
$i=0;
$array[0] = "No result";
while (($row = $result->fetch(PDO::FETCH_ASSOC)) !== FALSE) {
$array[$i] = $row;
$i++;
}
return $array;
}
You need to fetch() inside a while loop. It will return only one row each time it is called.
I've also taken the liberty of refactoring away your $i counter. Instead the No result is appended onto the array in the first position (for whatever purpose you planned to use it), and subsequent rows are appended on with [].
public function getTitlesForRegistrationForm() {
$array = array();
$result = $this->_db->query("SELECT UserTitleID, UserTitleName FROM UserTitles");
// Why are you putting No Result onto the array?
// I've left it in, but it doesn't make sense to me.
$array[] = "No result";
while ($row = $result->fetch(PDO::FETCH_ASSOC)){
$array[] = $row;
}
return $array;
}
It's because you are using fetch(). You need fetchall().
http://www.php.net/manual/en/pdostatement.fetchall.php
[edit]Wow. I need to learn to type faster.
Try:
while($row = $result->fetch(PDO::FETCH_ASSOC)){
foo();
}
Try while instead of foreach. Foreach only iterates over a single row that is returned. code:
while($row = fetch(PDO::FETCH_ASSOC)) {
$array[$i] = $row;
$i++;
}
Just replace the fetch method with fetchAll
$result->fetch()returns a single row with each call. You are only calling it once …