I want to get all players with a team name given

Question

I will get team_name from previous page and then I want to show all the players with that team name in the browser. But it gives error and when I try to print variable $players it has only details of one player with that team name. I think the result of query must be stored in some kind of 2-D array but I am not able to think the logic behind it.

Player Table has attributes

player_name
jearsy_no
team_name
phone_no
dob

<?php 
 include('config/db_connect.php');
 if(isset($_GET['team_name'])) {
  $team_name = mysqli_real_escape_string($conn, $_GET['team_name']);
  $sql_players = "SELECT * FROM player WHERE team_name = '$team_name'";
  $result_players = mysqli_query($conn, $sql_players);
  $players = mysqli_fetch_assoc($result_players);
  //print_r($players);
 }   
?>
<!DOCTYPE html>
<html>
<?php include('templates/header.php'); ?>

  <?php foreach($players as $player) : ?> 
  <div class="container">
        <h6><?php echo htmlspecialchars($player['player_name']); ?></h6>
  </div>
  <?php endforeach; ?>

<?php include('templates/footer.php'); ?>
</html>

I get this error:

Warning: Illegal string offset 'player_name'

And when I try to print_r($players) I get:

  Array ( [player_name] => Dwayn Miller 
          [jearsy_no] => 32 
          [team_name] => Team Australia 
          [dob] => 1998-12-11 
          [player_phone] => 4009871234 
        )

Note that your $players variable is not an array of players. Try $players['player_name'], [mysqli_fetch_assoc](https://www.php.net/manual/en/mysqli-result.fetch-assoc.php) fetch a result row. — marcell, Nov 11 '19 at 17:01
`mysql_fetch_assoc()` only returns the first row, you have to iterate it to get all the rows. Try `while($player=mysqli_fetch_assoc($result_players)){echo $player['player_name'];}` — gbestard, Nov 11 '19 at 17:05
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 11 '19 at 17:50

erik258 · Answer 1 · 2019-11-12T18:11:20.410

  $players = mysqli_fetch_assoc($result_players);

That sets the perhaps unfortunately named $players to an associative array for one player row, as your print_r shows.

But here you treat $players as if you expect it to have every row.

  <?php foreach($players as $player) : ?>

You should be calling mysqli_fetch_assoc once for each row, so you'll need to iterate over $result_players to pull each player row from the result set.

Returns an associative array that corresponds to the fetched row or NULL if there are no more rows.

You can simply watch the result coming out of mysql_fetch_assoc() to decide whether yo break your loop.

https://www.php.net/manual/en/mysqli-result.fetch-assoc.php has a great example of how to do it with a while loop. Essentially, it's:

while ($row = $result->fetch_assoc()) {
    printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
}

You could combine looping over each row, and outputting each row, by moving the mysqli_fetch_assoc call into the loop around the output:

  <?php while ($player = mysqli_fetch_assoc($result_players) : ?> 
  <div class="container">
        <h6><?php echo htmlspecialchars($player['player_name']); ?></h6>
  </div>
  <?php endwhile; ?>

score 1 · Accepted Answer · edited Nov 11 '19 at 17:53

1

I got a solution that instead of

$players = mysqli_fetch_assoc($result_players);

If I use mysqli_fetch_all() then I can get all players details with that team name

$players = mysqli_fetch_all($result_players, MYSQLI_ASSOC);

edited Nov 11 '19 at 17:53

Dharman

30,962
25
85
135

answered Nov 11 '19 at 17:21

score 0 · Answer 3 · answered Nov 11 '19 at 18:03

Remove the following line from your code:

$players = mysqli_fetch_assoc($result_players);

Then instead of looping $players loop on $result_players

<?php foreach($result_players as $player) : ?>

mysqli_fetch_assoc() returns a single row from the result set. You could also get all records into an array using:

$players = mysqli_fetch_all($result_players, MYSQLI_ASSOC);

but if all you want to do is iterate over all the records from the result set, you can simply iterate on $result_players

Warning:
You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough!

Together with prepared statements your code would look like this:

<?php
include 'config/db_connect.php';
if (isset($_GET['team_name'])) {
    $stmt = $conn->prepare('SELECT * FROM player WHERE team_name=?');
    $stmt->bind_param('s', $_GET['team_name']);
    $stmt->execute();
    $result_players = $stmt->get_result();
}
?>
<!DOCTYPE html>
<html>
<?php include('templates/header.php'); ?>

  <?php foreach($result_players as $player) : ?> 
  <div class="container">
        <h6><?php echo htmlspecialchars($player['player_name']); ?></h6>
  </div>
  <?php endforeach; ?>

<?php include('templates/footer.php'); ?>
</html>

Also don't forget to implement correct error reporting. See How to get the error message in MySQLi?

I want to get all players with a team name given

3 Answers3