#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    233
    Rep Power
    8

    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] => [username] => Super-Admin [account_type] => Super-admin ) Array ( [id] => [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
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    233
    Rep Power
    8
    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.
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    So you're saying that this code produced 2 print_r outputs, but only 1 table row? That's not even really possible.
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    233
    Rep Power
    8
    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).
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Ooooooh, I see.

    Look, the first line:
    PHP Code:
        $userResult "<tr>"
    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>"
    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.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    233
    Rep Power
    8
    Originally Posted by ManiacDan
    Ooooooh, I see.

    Look, the first line:
    PHP Code:
        $userResult "<tr>"
    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.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    183
    Rep Power
    84
    Originally Posted by ud2006
    why did I need the extra line above the while loop?
    The first line inside the loop
    PHP Code:
    $userResult .= "<tr>"
    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.
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo