How to return multiple rows from a table in in php using mysql

Question

I've decided to build a website for fantasy football for my family but am getting stuck with returning multiple rows from the database.

What I want: to make a single sql call and get the entire list of players so I can populate an object or list of objects. (if the whole table could be returned that would be great...). My goal is to have the list of available players to be drafted simply displayed to the user.

Currently I can somewhat see some results when testing by the following method (credit: from the php docs...). However I can't help be feel lost as to what is going on.. I can't make this make sense in my brain.

// My query
$sql = mysql_query("SELECT * FROM players");

//$data = mysql_fetch_array($sql);
while ($row = mysql_fetch_array($sql, MYSQL_NUM)) {
    printf("Name: %s <br/>", $row[1]);
}

This seems to print the names of each player. However how does 'mysql_fetch_array()' iterate each row as the while loop iterates? Further, since there are multiple columns how can I access each column... does this have to be hardcoded such as:

while ($row = mysql_fetch_array($sql, MYSQL_NUM)) {
    printf("Name: %s <br/>", $row[1]);
    printf("Team: %s <br/>", $row[2]);
    ..
    .
    printf("Team: %s <br/>", $row[5]);
}

Eventually I will replace the print statements with code that will store relevant date in a roster object or something:

class Roster(){

    $playerName;
    $team;
    $etc.
}

The while loop method above feels difficult and clunky. Is there a better way to access all returned rows? How would someone else attempt this? My limited experience is C# and sql server, which can do some amazing stuff with some effortless configuration... immediately return a list ^

If I'm not clear I will check back soon and comment further. I'm still a CS student. Thank you for your patience.

Answer 1

Try this,

 while($row_data = mysql_fetch_array($row_data))
 {
  $row_player_name = $row_data['column_name_player'];
  $row_team = $row_data['column_team'];
  echo $row_player_name;
  echo $row_team;
}

column_name_player is the column name of table which contains player name.

Answer 2

Although as Jason Suggested, please do not use mysql_* functions. But just to clarify your concept

        // My query
        $sql = mysql_query("SELECT * FROM players");

        //$data = mysql_fetch_array($sql);

        while ($row = mysql_fetch_array($sql, MYSQL_NUM)) {
                printf("Name: %s <br/>", $row[1]);
        }

This is complicated for a beginner unnecessarily. Instead of using row indexes like 1 or 2 or 3 you can (and probably should) simply use your field names there.

So update it to like

        // My query
        $sql = mysql_query("SELECT * FROM players");

        //$data = mysql_fetch_array($sql);

        while ($row = mysql_fetch_assoc($sql)) {
                echo "Name:". $row["name"];
                echo "Other Data:". $row["otherdata"];
                echo "<br>";
        }

Ofcourse name and otherdata are just assumed. You will replace them with your actual field names from table

Answer 3

If you want to retrieve every column in every row without having to know each column name, you can count the number of columns in the table.

$queryCols = "SHOW COLUMNS FROM players":
$queryPlayers = "SELECT * FROM players";
$result = mysql_query($queryPlayers);
$playerColumns = mysql_query($queryCols);
$colsResult = mysql_fetch_row($playerCols);
$numFields = count($colsResult);
while ($row = mysql_fetch_array($result)) {
  for ($i = 0; $i < $numFields; $i++) {
    $row[$i]; // Each column in each row
  }
}

---

Here an example of a mysqli select statement which is the successor to the soon to be deprecated mysql_* statements:

$DB_NAME = 'test';
$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '';

$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if (mysqli_connect_errno()) {
 printf('Connection to $s using %s@$s failed: %s\n', 
  $DB_NAME, $DB_USER, $DB_HOST, mysqli_connect_error());
 exit();
}

$query = "SELECT `fname`, `lname`, `team` FROM `players`;";

if ($stmt = $mysqli->prepare($query)) {
 $stmt->execute();
 $stmt->bind_result($fname, $lname, $team);
 $table = <<< TABLE
<table border="1">
  <thead><tr>
   <th>First Name</th><th>Last Name</th><th>Team</th>
  </tr></thead>
  <tbody>
TABLE;
 while ($stmt->fetch()) {
  $table .= "<tr><td>$fname</td><td>$lname</td><td>$team</td></tr>";
 }
 $table .= "</tbody></table>";
 printf($table);
 $stmt->close();
} else {
  printf("Prepared Statement Error: %s\n", $mysqli->error);
}

Table

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `fname` varchar(32) NOT NULL,
  `lname` varchar(32) NOT NULL,
  `team` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

INSERT INTO `players` (`id`, `fname`, `lname`, `team`) VALUES
(1, 'Peyton', 'Manning', 'Denver Broncos'),
(2, 'Matt', 'Ryan', 'Atlanta Falcons'),
(3, 'Tom', 'Brady', 'New England Patriots'),
(4, 'Colin', 'Kaepernick', 'San Francisco 49ers'),
(5, 'Matt', 'Schaub', 'Houston Texans'),
(6, 'Aaron', 'Rodgers', 'Green Bay Packers'),
(7, 'Joe', 'Flacco', 'Baltimore Ravens'),
(8, 'Robert', 'Griffin III', 'Washington Redskins'),
(9, 'Andrew', 'Luck', 'Indianapolis Colts'),
(10, 'Matt', 'Flynn', 'Seattle Seahawks'),
(11, 'Andy', 'Dalton', 'Cincinnati Bengals'),
(12, 'Christian', 'Ponder', 'Minnesota Vikings');

The Results:

First Name  Last Name   Team
Peyton      Manning     Denver Broncos
Matt        Ryan        Atlanta Falcons
Tom         Brady       New England Patriots
Colin       Kaepernick  San Francisco 49ers
Matt        Schaub      Houston Texans
Aaron       Rodgers     Green Bay Packers
Joe         Flacco      Baltimore Ravens
Robert      Griffin III Washington Redskins
Andrew      Luck        Indianapolis Colts
Matt        Flynn       Seattle Seahawks
Andy        Dalton      Cincinnati Bengals
Christian   Ponder      Minnesota Vikings

Answer 4

//This function will print 5 rows 10 columns 

$n = 5; 
$m = 10;
$tr='';
	
 for($r=0; $r<$n; $r++)
  {
	$td='';		
		
   for ($c=0; $c<$m; $c++)
        {
		$td = $td."<td>".$c.$r."</td>";
		}
			
	   $tr =$tr."<tr>".$td. "</tr>";
	}

	echo $otp = "<table border='0'>".$tr."</table>";