Show system/ picking a winner?

[salukigirl]

I'm making a dog game where when animals are created they are given a set of randomized stats, these are added together for a total.
I want to make a showing system that will give the animal with the highest total 1st place and next highest 2nd and so on and so forth. It seems pretty simple but how do I do that? The animals judged would have to be entered otherwise they aren't eligible. so how do I make it so animals are entered to be judged?
anyone get what I mean?

 

[supajason]

i understand what do you have so far?

 

[salukigirl]

a form where the user can pick which of their animals to enter into the show, then they are put in a "temporary" table in the database. I mean temporary because after the show is run the table will be emptied.


But with the info in the database I'm not quite sure how to go on from there.....
The "show system" will have to select the entered dogs form the table in the database and sort them by highest stat total and then display on a "Results" page which entered animal got what place...

 

[supajason]

have you got a link or the code?

 

[salukigirl]

sure. lemmie go fetch the code.
Edit:

<form name="form 1" method="post" action="show.php">
<select name='petname'>
<option value=''></option>
<?php
$query = "SELECT petname FROM pets WHERE ownerid ='".$_SESSION['id']."'";
$result = mysql_query($query)
or die ("You don't have any animals to enter!");

while ($pet = mysql_fetch_object($result))
{

$petname = $pet->petname;
?>

 

<?php
echo
"
<option value='$petname'>$petname</option>

 

<br>
";
?>

<?php
}
?>
</select>
<input type='submit'/>


<?php
$check = @mysql_query("SELECT petname FROM show WHERE petname = '$pname'");
$check = @mysql_num_rows($check);

if($check > 0)
die("Sorry, that animal is already entered in this show");
?>

<?php
$show = @mysql_query("INSERT INTO show (petname)
VALUES ('$petname')") or die ('error' . @mysql_error());
?>

That is supposed to select the users animals to enter in the show. It only enteres the name right now but I'm going to fix it to enter all it's info and the stat total which is what the show will be judged on.

 

[supajason]

ok so this is what i came up with:

<?PHP
$db = mysql_connect("a", "a", "a") or die("Connection Failure to Database");
mysql_select_db("a", $db) or die ("Connection Failure to Database");
if(isset($_POST['pet']))
{
  //Enter dog into show
	$sql = 'INSERT INTO `pet`.`show` (`petname`, `totalstats`) VALUES (\'' . $_POST['pet'] . '\', \'' . $_POST['total'] . '\')';
  mysql_query($sql);
  echo $_POST['pet'] . ' Entered show.';
}
//SHOW USERS DOG(S)
$sql = "SELECT petname, totalstats FROM pets WHERE owerid ='" . $_SESSION['id'] . "'";
$result = mysql_query($sql);
$check = @mysql_num_rows($result);
if($check == 0)
{
	echo 'You have no animals.';
}
else
{
	echo '<table border="1"><tr><td><b>Pet Name</b></td><td><b>Total Stats</b></td><td><b>Enter Comp</b></td></tr>';
	while($row = @mysql_fetch_array($result))
	{	
	echo '<tr>
	<td>' . $row['petname'] . '</td><td>' . $row['totalstats'] . '</td><td>' . checkentered($row['petname'],$row['totalstats']) . '</td></tr>';
	}
}
echo '</table><br>';

//CHECK IF PET IS IN THE SHOW
function checkentered($petname, $total)
{
	$sql = "SELECT petname FROM show WHERE petname = '$petname'";
	$result = mysql_query($sql);
	$check = @mysql_num_rows($result);
	if($check == 0)
	{
		$r = '<form name="enterdog" method="post" action=""><input type="hidden" name="total" value="' . $total . '"><input type="hidden" name="pet" value="' . $petname . '"><input type="submit" value="Enter"></form>';
	}
	else
	{
		$r = "This dog has already been entered";
	}
	return $r;
}
//SHOW SYSTEM
$sql = "SELECT * FROM `show` ORDER BY totalstats DESC";
$result = mysql_query($sql);
$check = @mysql_num_rows($result);
if($check == 0)
{
	echo 'The show has no contests.';
}
else
{
	echo '<table border="1"><tr><td><b>Postion</b></td><td><b>Pet Name</b></td><td><b>Total Stats</b></td></tr>';
	while($row = mysql_fetch_array($result))
	{
	$i++;	
	echo '<tr>
	<td>' . $i . '</td>
	<td>' . $row['petname'] . '</td>
	<td>' . $row['totalstats'] . '</td>
	</tr>';
	}
}
echo '</table>';

?>

what you think?

 

[woiwky]

Personally, I wouldn't use a temporary table or anything like that. I would just add a new field to the pets table called 'entered' or something, which would hold a value of either 0 or 1 for false and true respectively(an enum type could be used for that, but any int type would work fine too). Then, when the show is over, just use an update query to set the 'entered' field to 0 for all pets.

So the page for entering a pet would have to change a bit to something like this:

<?php
if (empty($_POST['petname'])) {
?>
<form name="form 1" method="post" action="show.php">
<select name='petname'>
<option value=''></option>
<?php
$query = "SELECT petname FROM pets WHERE ownerid ='{$_SESSION['id']}' AND entered = 0";
$result = mysql_query($query)
or die ("You don't have any animals to enter!");

while ($pet = mysql_fetch_object($result))
{
echo "<option value='{$pet->petname}'>{$pet->petname}</option>";
}
?>
</select>
<input type='submit'/>
</form>
<?php
}
else {
    $pname = mysql_real_escape_string($_POST['petname']);
    $query = "SELECT ownerid FROM pets WHERE petname = '$pname' AND entered = 0 AND ownerid ='{$_SESSION['id']}'";
    $result = mysql_query($query);
    if (mysql_num_rows($result)) {
        $query = "UPDATE pets SET entered = 1 WHERE petname = '$pname'";
        mysql_query($query);
    }
    else {
        die('You cannot enter this pet');
    }
}
?>

And the page to display the results would look something like this:

<?php
$query = "SELECT *, (stat1 + stat2 + stat3) AS total FROM pets WHERE entered = 1 ORDER BY total DESC";
$result = mysql_query($query);
$trows = '';
for ($i = 1; $pet = mysql_fetch_object($result); $i++) {
    $trows .= "<tr><td>$i</td>";
    $trows .= "<td>{$pet->petname}</td>";
    $trows .= "<td>{$pet->stat1}</td>";
    $trows .= "<td>{$pet->stat2}</td>";
    $trows .= "<td>{$pet->stat3}</td>";
    $trows .= "<td>{$pet->total}</td></tr>";
}
if (empty($trows)) {
    $trows = '<tr><td colspan="6">There are currently no pets entered in the show</td></tr>';
}
?>

<table>
    <tr>
    <th>Place</th>
    <th>Pet Name</th>
    <th>Stat 1</th>
    <th>Stat 2</th>
    <th>Stat 3</th>
    <th>Total</th>
    </tr>
    <tr>
    <?php print $trows; ?>
</table>

Note that I'm leaving out the code to connect to the database and check if the user is logged in and whatever else should happen at the beginning of every script. Also, you'll need to change the display page a bit to match whatever the stat names are. And if you already have a field which stores the total of the stats, you'll need to edit the page further to take that into account.

 

[salukigirl]

Ok for the total stats I randomized each individual stat and then had php add it for a total. the total isn't stored in the database. Should it be? how do I do that?
Edit:
I also want the animals to earn points from a place in a show and those points add up to a different level.


Each animal will get 1 point for ever dog they beat in the placings. Like if there were 100 entrants and your dog got 50th place he'd get 50 show points. and lets say it takes 100 to get to level 1(dogs start out at level 0)
How would I update that by points?

 

[woiwky]

Well according to the "rules" of databases, you shouldn't store values which are the results of calculations based on values which are already stored unless the calculation is time consuming. This helps to minimize db size and ensure data doesn't get stale. Since totaling the stats is very simple, you shouldn't be storing the result in the db.

As for points/levels, it's probably best to create a table in the db to store the levels and their required points, and add a field to the pets table to store the pets' points. Then you'd use a query like this to get the current level based on a pet's points:

SELECT level FROM levels WHERE requirement < $petpoints ORDER BY level DESC LIMIT 1

 

[salukigirl]

ok I see now. thanks so much!!
Edit:

Well according to the "rules" of databases, you shouldn't store values which are the results of calculations based on values which are already stored unless the calculation is time consuming. This helps to minimize db size and ensure data doesn't get stale. Since totaling the stats is very simple, you shouldn't be storing the result in the db.

As for points/levels, it's probably best to create a table in the db to store the levels and their required points, and add a field to the pets table to store the pets' points. Then you'd use a query like this to get the current level based on a pet's points:

SELECT level FROM levels WHERE requirement < $petpoints ORDER BY level DESC LIMIT 1

How would I add that to the database? a new table "Level" and then....

Like level 1 as a column or how would I do the required points for each level?


Sorry about all the questions :happysad:

 

[woiwky]

I made a slight error in that example query actually, it should be <= instead of <.

Anyway, to match that query, you would make a table named 'levels' and give it at least 2 fields called 'level' and 'requirement'. 'level' would probably be the primary key and auto-increment, and 'requirement' would be the number of points, which you would specify, required for the level. It would also be a good idea to add a row with 'level' and 'requirement' set to 0 to act as the default.

So the table would look something like:

level     requirement
0                     0
1                 100
2                 200
3                 300
4                 400
etc.