The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> PHP Development
|
PDO Fetch users from database returns only one user
Discuss PDO Fetch users from database returns only one user in the PHP Development forum on Dev Shed.
PDO Fetch users from database returns only one user PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
February 11th, 2013, 02:59 PM
|
|
Contributing User
|
|
Join Date: Mar 2007
Posts: 230
Time spent in forums: 4 Days 20 h 39 m
Reputation Power: 7
|
|
|
PDO Fetch users from database returns only one user
I try to fetch users from a mysql database with PDO, therefor I created a class.
I select certain columns from the database and I know there are 2 records in that database, but only the second user is displayed.
It's being displayed inside an html table (through php code) and I believe that code is correct (if not please correct me), so I have no clue of what the problem is.
Here is the code I use (I originally have it in a try try catch block):
PHP Code:
$this->pdo = $this->connectMySql();
$query = 'SELECT id, username, account_type FROM users';
$stmt = $this->pdo->prepare($query);
//print_r($stmt);
if(!$stmt->execute()){
return false;
}
$nart = $stmt->rowCount();
if ($nart == 0) {
return "Geen gebruiker gevonden";
}
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
print_r($row);
$userResult = "<tr>";
$userResult .= "<td>" . $row['id'] . "</td>";
$userResult .= "<td>" . $row['username'] . "</td>";
$userResult .= "<td><img src=\"images/yes.png\" width=\"24px\"/></td>";
$userResult .= "<td>" . $row['account_type'] . "</td>";
$userResult .= "<td><a href=\"edit_user.php?user=" . $row['id'] . "\"><img src=\"images/edit.png\" width=\"24px\"/></a></td>";
$userResult .= "<td><a href=\"delete_user.php?user=" . $row['id'] . "\" onclick=\"return confirm('Weet je deze pagina wilt verwijderen? Dit kan niet ongedaan gemaakt worden!');\"><img src=\"images/trash.png\" width=\"24px\"/></a></td>";
$userResult .= "</tr>";
}
$this->pdo = null;
return $userResult;
I even tried to change the query to *, but even this displays only 1 user.
I believe the <tr></tr> is set right, because each row of the table contains a user.
I even use a print_r($row) on the fetch which displays both users but not in the table. When I change the fetch to fetchAll it just extents the array.
This is the array I get:
PHP Code:
Array ( [id] => 1 [username] => Super-Admin [account_type] => Super-admin ) Array ( [id] => 3 [username] => Testing [account_type] => Beheerder )
Just for further info, the user that does get displayed is the second record in the database (the username: Testing), the first record is not displayed (username:Super-Admin).
If someone notices a typo, please let me know, cause I lost it.
Thanks in advanced.
Last edited by ud2006 : February 11th, 2013 at 03:04 PM.
Reason: add some more info
|
February 11th, 2013, 03:21 PM
|
 |
Likely to be eaten by a grue.
|
|
Join Date: Oct 2006
Location: Pennsylvania, USA
|
|
|
Check your HTML source to see if the data is in there with a bad tag or something.
You can't print_r inside a table, it makes invalid HTML. Maybe that's your issue.
It looks like both records are being fetched and returned, but the HTML just isn't working.
__________________
HEY! YOU! Read the New User Guide and Forum Rules
"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin
"The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002
Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
|
February 11th, 2013, 03:25 PM
|
|
Contributing User
|
|
Join Date: Mar 2007
Posts: 230
Time spent in forums: 4 Days 20 h 39 m
Reputation Power: 7
|
|
|
Thanks for the reply, I've checked the html, but nothing wrong found. Just the record inserted into the table. As for the print_r, I have removed it, but still the same result.
|
February 11th, 2013, 03:51 PM
|
|
Likely to be eaten by a grue.
|
|
Join Date: Oct 2006
Location: Pennsylvania, USA
|
|
|
So you're saying that this code produced 2 print_r outputs, but only 1 table row? That's not even really possible.
|
February 11th, 2013, 03:56 PM
|
|
Contributing User
|
|
Join Date: Mar 2007
Posts: 230
Time spent in forums: 4 Days 20 h 39 m
Reputation Power: 7
|
|
Quote: | Originally Posted by ManiacDan So you're saying that this code produced 2 print_r outputs, but only 1 table row? That's not even really possible. |
No that's not what I am saying, the print_r is just for me a test to see what the fetch actually gets, and displays that array on the page. I just removed it to be sure that that doesn't stands in the way. The while loop only gives me at this time 1 table row of user back, which seems very strange because all the users are fetched.
The $userResult builds up inside the while loop to retrieve that database items.
The return $userResult; than will return the value to page, where I call the function I need (to show all users).
|
February 11th, 2013, 03:58 PM
|
|
Likely to be eaten by a grue.
|
|
Join Date: Oct 2006
Location: Pennsylvania, USA
|
|
Ooooooh, I see.
Look, the first line:
That resets $userResult. You have to use .= on every line.
PHP Code:
$userResult = '';
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
print_r($row);
$userResult .= "<tr>";
|
February 11th, 2013, 04:01 PM
|
|
Contributing User
|
|
Join Date: Mar 2007
Posts: 230
Time spent in forums: 4 Days 20 h 39 m
Reputation Power: 7
|
|
Quote: | Originally Posted by ManiacDan Ooooooh, I see.
Look, the first line:
That resets $userResult. You have to use .= on every line.
PHP Code:
$userResult = '';
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
print_r($row);
$userResult .= "<tr>";
|
 That did the trick, please explain, why did I need the extra line above the while loop? I was almost breaking my keyboard because of this.
|
February 11th, 2013, 06:04 PM
|
|
Contributing User
|
|
Join Date: Aug 2011
Location: Sydney Australia
|
|
Quote: | Originally Posted by ud2006 why did I need the extra line above the while loop? |
The first line inside the loop
is shorthand for
PHP Code:
$userResult = $userResult . "<tr>";
Without the $userResult = ''; before the loop, the first line inside the loop will find that $userResult doesn't exist.
But the first line in the loop must be a concat for the second and subsequent iterations of the loop.
|
February 11th, 2013, 08:23 PM
|
 |
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
Hi,
by the way, there are several things you can improve in your database code: When you don't have any parameters in your query, there's no need for a prepared statement. Just use query. And this low level "while ($row = fetch ...)" pattern known from the old MySQL extension is no longer necessary in PDO. A PDOStatement is iterable, so you can use it directly in a foreach loop:
PHP Code:
$users_stmt = $this->pdo->query('
SELECT
id
, username
, account_type
FROM
users'
);
foreach ($users_stmt as $user) {
#...
}
Don't use fetchAll unless you actually physically need the rows in an array. Otherwise, it's just a waste of resources.
By the way, do you open and close the database connection for every single query? That makes no sense and creates a lot of unnecessary overhead. Just open the connection once in the script and reuse it.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
