SQL prepared statements - how to SELECT multiple rows

Question

I have this code so far

// Starts the transaction
self::$database->startTransaction();
    try {

        $sql  = "SELECT playerId FROM players WHERE name=?";
        $stmt = self::getConnection()->prepare($sql);
        $stmt->bind_param('s', $playerName);
        foreach ($playerNames as $key => $playerName) {
            $stmt->execute();
            $stmt->bind_result($playerId);
            $stmt->fetch();
            echo $playerId . "<br>";
        }

        // commits the transaction
        self::$database->commit();
    } catch (Exception $e) {
        self::$database->rollback();
        throw new Exception(__METHOD__." | ".$e->getMessage());
    }

The array $playerNames contains the names of the players, e.g.

array('Player1', 'Player2', 'player3')

The code from above should select the playerId of those players from the database. I have some issues:

1. It just returns the last playerId (in this case the Id of 'player3'). I don't know why.

2. I use a foreach-loop to execute(). is this bad for the performance, if there were hundreds of names in the array?

3. In generell: Is this the correct approach for SELECTing or INSERTing stuff from or into a database?

I read this question: How can I prevent SQL injection in PHP?

But it didn't really work because of this:

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

I get an error with the getResult()-method. It says, the method doesn't exist. I think my webspace doesn't support it and I can't install it manually. So I need to stick with fetch().

Or might it have other reasons?

Answer 1

    $sql  = "SELECT playerId FROM players WHERE name=?";
    $stmt = self::getConnection()->prepare($sql);
    $stmt->bind_param('s', $playerName);
    $stmt->bind_result($playerId);
    foreach ($playerNames as $key => $playerName) {
        $stmt->execute();
        $stmt->fetch();
        echo $playerId . "<br>";
    }

1. You are fetching results of only last execute
2. Running long loops is apparently bad for performance. Try to avoid them.
3. Yes, in general.