PHP - PDO Only Returns One Row

B

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
Hey, everyone. It's me again.

So I'm doing abit of OOP here, and I have a bit of problem, where PDO returns only 1 row instead all rows, my code for example is below. This happens for every function, it only returns one row.
PHP: 
public static function getBans()
    {
        global $conn; 
        $stmt = $conn->prepare("SELECT * FROM bans");
        $stmt->execute();
        $stmt = $stmt->fetchAll();
        foreach($stmt as $ban)
        {
            return "<tr>
                      <th scope='row'>".$ban['id']."</th>
                      <td>".self::userDataFromId($ban['user_id'],'username')."</td>
                      <td>".$ban['ip']."</td>
                      <td>".$ban['machine_id']."</td>
                      <td>".self::userDataFromId($ban['user_staff_id'],'username')."</td>
                      <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
                      <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
                      <td>".$ban['ban_reason']."</td>
                      <td>".$ban['type']."</td>
                      <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
                     </tr>";
        }
    }
public static function getFilters()
    {
        global $conn;
        $stmt = $conn->prepare("SELECT * FROM `hk`.`wordfilter` LIMIT 0, 1000");
        $stmt->execute();
        $stmt = $stmt->fetchAll();
        foreach($stmt as $filter)
        {
            return "<tr id='". $filter['id']. "'>
                      <td>".$filter['key']."</td>
                      <td>".$filter['replacement']."</td>
                      <td><a href='editfilter?key=".$filter['key']."' id='edit' ><i class='fa fa-pencil'></i></a></td>

            ";
        }
    }
I don't really know what I'm doing - i wish i did. anyway, I hope y'all can help me.

Thanks.
 
Sort by date Sort by votes
MayoMayn

MayoMayn

BestDev
Oct 18, 2016
1,423
683
You must be registered for see links


You need to define the fetch mode.
This should do the trick.
PHP: 
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

You must be registered for see links
 
Upvote 0 Downvote
B

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
Virtuex said:
You must be registered for see links


You need to define the fetch mode.
This should do the trick.
PHP: 
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

You must be registered for see links
Click to expand...
I have tried this, forgot to mention sorry. It didn't work.
 
Upvote 0 Downvote
Weasel

Weasel

👄 I'd intercept me
Nov 25, 2011
4,135
2,461
Change
Code: 
$stmt = $stmt->fetchAll();
to
Code: 
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

and

Code: 
foreach($stmt as $filter)
to
Code: 
foreach($result as $filter)
 
Upvote 0 Downvote
Weasel

Weasel

👄 I'd intercept me
Nov 25, 2011
4,135
2,461
Virtuex said:
You must be registered for see links


You need to define the fetch mode.
This should do the trick.
PHP: 
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

You must be registered for see links
Click to expand...
This doesn't really matter. If you don't set a fetch mode, it falls back to its default, which is FETCH_BOTH.
 
Berk said:
Yeah, still same remains with no error...
Click to expand...
What happens if you run the query directly within MySQL, can you show your results?
 
Upvote 0 Downvote
Weasel

Weasel

👄 I'd intercept me
Nov 25, 2011
4,135
2,461
You're using return in your foreach loop. After using the return statement, it stops executing. Either stop all results in an array, and return the array after the foreach loop, or echo all the results instead of returning it.
 
Upvote 0 Downvote
B

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
Holmes said:
You're using return in your foreach loop. After using the return statement, it stops executing. Either stop all results in an array, and return the array after the foreach loop, or echo all the results instead of returning it.
Click to expand...
This has worked, thank you.
 
Upvote 0 Downvote
Weasel

Weasel

👄 I'd intercept me
Nov 25, 2011
4,135
2,461
Here are some options worked out.

OPTION 1
In your class:
Code: 
<?php
public static function getBans()
{
    global $conn;

    $stmt = $conn->prepare("SELECT * FROM bans");

    if ($stmt->execute()) {
        return $stmt->fetchAll();
    }

    return false;
}
?>

On the page itself:
Code: 
<?php
$bans = yourClassName::getBans();

if ($bans !== false) {
    foreach ($bans as $ban) {
        echo "<tr>
            <th scope='row'>".$ban['id']."</th>
            <td>".self::userDataFromId($ban['user_id'],'username')."</td>
            <td>".$ban['ip']."</td>
            <td>".$ban['machine_id']."</td>
            <td>".yourClassName::userDataFromId($ban['user_staff_id'],'username')."</td>
            <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
            <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
            <td>".$ban['ban_reason']."</td>
            <td>".$ban['type']."</td>
            <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
            </tr>"
    }
}
?>

OPTION 2
In your class:
Code: 
<?php
public static function getBans()
{
    global $conn;

    $bans = [];
    $stmt = $conn->prepare("SELECT * FROM bans");

    if ($stmt->execute() && $stmt->rowCount() > 0) {
        $stmt = $stmt->fetchAll();

        foreach($stmt as $ban) {
            $bans[] = "<tr>
                <th scope='row'>".$ban['id']."</th>
                <td>".self::userDataFromId($ban['user_id'],'username')."</td>
                <td>".$ban['ip']."</td>
                <td>".$ban['machine_id']."</td>
                <td>".self::userDataFromId($ban['user_staff_id'],'username')."</td>
                <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
                <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
                <td>".$ban['ban_reason']."</td>
                <td>".$ban['type']."</td>
                <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
            </tr>";
        }
    }

    return $bans;
}
?>

On the page itself:
Code: 
<?php
$bans = yourClassName::getBans();

foreach ($bans as $ban) {
    echo $ban;
}
?>


OPTION 3
In your class:
Code: 
<?php
public static function getBans()
{
    global $conn;

    $bans = [];
    $stmt = $conn->prepare("SELECT * FROM bans");

    if ($stmt->execute() && $stmt->rowCount() > 0) {
        $stmt = $stmt->fetchAll();

        foreach($stmt as $ban) {
            echo "<tr>
                <th scope='row'>".$ban['id']."</th>
                <td>".self::userDataFromId($ban['user_id'],'username')."</td>
                <td>".$ban['ip']."</td>
                <td>".$ban['machine_id']."</td>
                <td>".self::userDataFromId($ban['user_staff_id'],'username')."</td>
                <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
                <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
                <td>".$ban['ban_reason']."</td>
                <td>".$ban['type']."</td>
                <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
            </tr>";
        }
    }
}
?>

On the page itself:
Code: 
<?php
yourClassName::getBans();
?>
 
Upvote 0 Downvote
B

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
Holmes said:
Here are some options worked out.

OPTION 1
In your class:
Code: 
<?php
public static function getBans()
{
    global $conn;

    $stmt = $conn->prepare("SELECT * FROM bans");

    if ($stmt->execute()) {
        return $stmt->fetchAll();
    }

    return false;
}
?>

On the page itself:
Code: 
<?php
$bans = yourClassName::getBans();

if ($bans !== false) {
    foreach ($bans as $ban) {
        echo "<tr>
            <th scope='row'>".$ban['id']."</th>
            <td>".self::userDataFromId($ban['user_id'],'username')."</td>
            <td>".$ban['ip']."</td>
            <td>".$ban['machine_id']."</td>
            <td>".self::userDataFromId($ban['user_staff_id'],'username')."</td>
            <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
            <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
            <td>".$ban['ban_reason']."</td>
            <td>".$ban['type']."</td>
            <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
            </tr>"
    }
}
?>

OPTION 2
In your class:
Code: 
<?php
public static function getBans()
{
    global $conn;

    $bans = [];
    $stmt = $conn->prepare("SELECT * FROM bans");

    if ($stmt->execute() && $stmt->rowCount() > 0) {
        $stmt = $stmt->fetchAll();

        foreach($stmt as $ban) {
            $bans[] = "<tr>
                <th scope='row'>".$ban['id']."</th>
                <td>".self::userDataFromId($ban['user_id'],'username')."</td>
                <td>".$ban['ip']."</td>
                <td>".$ban['machine_id']."</td>
                <td>".self::userDataFromId($ban['user_staff_id'],'username')."</td>
                <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
                <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
                <td>".$ban['ban_reason']."</td>
                <td>".$ban['type']."</td>
                <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
            </tr>";
        }
    }

    return $bans;
}
?>

On the page itself:
Code: 
<?php
$bans = yourClassName::getBans();

foreach ($bans as $ban) {
    echo $ban;
}
?>


OPTION 3
In your class:
Code: 
<?php
public static function getBans()
{
    global $conn;

    $bans = [];
    $stmt = $conn->prepare("SELECT * FROM bans");

    if ($stmt->execute() && $stmt->rowCount() > 0) {
        $stmt = $stmt->fetchAll();

        foreach($stmt as $ban) {
            echo "<tr>
                <th scope='row'>".$ban['id']."</th>
                <td>".self::userDataFromId($ban['user_id'],'username')."</td>
                <td>".$ban['ip']."</td>
                <td>".$ban['machine_id']."</td>
                <td>".self::userDataFromId($ban['user_staff_id'],'username')."</td>
                <td>".date('Y-m-d H:s:i',$ban['timestamp'])."</td>
                <td>".date('Y-m-d H:s:i',$ban['ban_expire'])."</td>
                <td>".$ban['ban_reason']."</td>
                <td>".$ban['type']."</td>
                <td><a href='editban?id=".$ban['id']."'><i class='fa fa-pencil'></i></a>&nbsp; <a id='delete' href='#' data-id='".$ban['id']."'><i class='fa fa-trash'></i></td>
            </tr>";
        }
    }
}
?>

On the page itself:
Code: 
<?php
yourClassName::getBans();
?>
Click to expand...
Option 3 Has worked perfectly, thanks.
 
Upvote 0 Downvote

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
☀️  Switch to Light Theme

Latest posts

Top