It takes a few tweaks to be able to display multiple results from the database in a table. This has been asked a few times now, so I should probably end up writing a tutorial about it. Anyway, I usually end up showing the same script, which is from a now-abandoned Pokemon-style monster collecting game I was working on a while back. When the player logged in, I wanted to pull up every monster the player owned in the database and display them all, allowing them to select one and begin the game. Here is the monster selection script, with some notes following:
Code: Select all
<?php
include 'connect.php';
session_start();
$exitScript = 0;
$username = $_SESSION['name'];
$playerid = $_SESSION['playerid'];
//Get ready to skip main script if player not logged in
if (!$_SESSION['name']) {
$exitScript = 1;
}
?>
</head>
<body>
<div id="container">
<div id="header">
<h1>Working Title</h1>
</div>
<div id="nav">
<ul>
</ul>
</div>
<div id="content">
<div id="page">
<?php
//main decision block for if player not logged in
if ($exitScript == 0){
echo "Choose a monster by clicking its name, or create a new monster: <br />";
//check to see what monsters the logged in player has from the 'playermonster' table
$sql = "SELECT * FROM playermonster WHERE playerid = '$playerid'";
if ($result = mysqli_query($db, $sql)) {
//fetch array and set up table
?>
<table border="3" cellpadding="3" cellspacing="0">
<tr>
<th align="center">Name</th>
<th align="center">Level</th>
<th align="center">Monster Type</th>
<th align="center">Monster Species</th>
<th align="center">Max Health</th>
<th align="center">Attack</th>
<th align="center">Defense</th>
<th align="center">Speed</th>
<th align="center">Mind</th>
<th align="center">Will</th>
</tr>
<?php
while ($row = mysqli_fetch_row($result)) {
//create the table to choose a monster
?>
<tr>
<td align="center"><?php echo("<a href='monsterselected.php?mid=$row[0]'>$row[2]</a>") ?> </td>
<td align="center"><?php echo($row[13]) ?> </td>
<td align="center"><?php echo($row[3]) ?> </td>
<td align="center"><?php echo($row[4]) ?> </td>
<td align="center"><?php echo($row[6]) ?> </td>
<td align="center"><?php echo($row[8]) ?> </td>
<td align="center"><?php echo($row[9]) ?> </td>
<td align="center"><?php echo($row[10]) ?> </td>
<td align="center"><?php echo($row[11]) ?> </td>
<td align="center"><?php echo($row[12]) ?> </td>
</tr>
<?php //go back into PHP for rest of script
}
?>
</table>
<br /><br />
<a href='createnew.php'>Create a New Monster</a><br /><br />
<a href='deletemonster.php'>Delete Monster</a>
<?php
}
} else {
echo "You are not logged in. Please go back and log in.<br />";
echo "<a href='index.php'>Home Page</a>";
}
?>
The important part of the script is the way I set up the table: I made the SELECT query, but without doing the final step (so I now just have a SQL query result object in the $result variable), and then I opened the table tag as well as all of the table header tags:
Code: Select all
$sql = "SELECT * FROM playermonster WHERE playerid = '$playerid'";
if ($result = mysqli_query($db, $sql)) {
//fetch array and set up table
?>
<table border="3" cellpadding="3" cellspacing="0">
<tr>
<th align="center">Name</th>
<th align="center">Level</th>
<th align="center">Monster Type</th>
<th align="center">Monster Species</th>
<th align="center">Max Health</th>
<th align="center">Attack</th>
<th align="center">Defense</th>
<th align="center">Speed</th>
<th align="center">Mind</th>
<th align="center">Will</th>
</tr>
Then I started a WHILE loop to iterate through each of the results, one-by-one. Inside that loop, I make a table row, <tr>, with several table data cells in it, <td>, that each contain a piece of the data retrieved from the query. Each pass through of the WHILE loop represents one record returned from the SELECT query, so you can see how this WHILE loop would build multiple rows in the table, all filled with each monster's stats. One thing to note is that instead of being able to use associative arrays (like $row['name']), you must use a numerically indexed array with this command. So $row[0] means the first column of the retrieved record (likely the ID of whatever you are selecting), $row[1] would be the second column, $row[2] the third, etc. Also note that all through my code I use the "mysqli" extension to PHP, not "mysql". It is newer and has better performance and more security, but this code will work fine if you just remove the "i" from the method calls and also removed the reference to $db from the method calls as well (mysqli requires the link to the database connection in each call, whereas traditional mysql method calls do not):
Code: Select all
<?php
while ($row = mysqli_fetch_row($result)) {
//create the table to choose a monster
?>
<tr>
<td align="center"><?php echo("<a href='monsterselected.php?mid=$row[0]'>$row[2]</a>") ?> </td>
<td align="center"><?php echo($row[13]) ?> </td>
<td align="center"><?php echo($row[3]) ?> </td>
<td align="center"><?php echo($row[4]) ?> </td>
<td align="center"><?php echo($row[6]) ?> </td>
<td align="center"><?php echo($row[8]) ?> </td>
<td align="center"><?php echo($row[9]) ?> </td>
<td align="center"><?php echo($row[10]) ?> </td>
<td align="center"><?php echo($row[11]) ?> </td>
<td align="center"><?php echo($row[12]) ?> </td>
</tr>
The only thing left to do is go back into PHP (using <?php) and close the brace I opened to create the WHILE loop--you will get an error if you don't--and then close the table:
Code: Select all
<?php //go back into PHP for rest of script
}
?>
</table>
If you have any questions or need any help let me know! As I usually suggest, do not copy and paste this code and then tweak it to work for you. Learn what it is doing and then write it from scratch for your game. That way you will have a much better handle on any errors that will pop up, and it would be more of a pain to copy'n'paste it anyway because of all the minute details you would have to change.
