Forum Moderators: coopster
However this year i am going to attempt to use MySQL and PHP to have it automatic bar result entry. I have built a basic php and MySQL site before with the usual insert, edit etc.. But this one will need features im not sure how i go about making.
Basically there will be a list of horses (around 150) divided into 4 grades. Entrants pick 2 horses from each grade so a total of 8 horses. Winning horses score 5, 2nd = 3 and 3rd =1.
I was thinking of having the following 2 tables - one for the horses - listing their name and grade. One for entrants - the peoples contact details and their chosen horses.
However from there im not sure how to able to enter the latest results and how i can create a leaderboard page and list the points each horse has. I was thinking of a new table with a row for each result showing horse name and points scored but i then dont't know how this table can be used to create a column on the horse table that lists each horses total points. And also how it can be used to create the total points each entranant has got so far (i.e. the total point for all 8 of their horses)
HORSES
horse_id
horse_name
horse_grade
ENTRANTS
entrant_id
entrant_name
horse_grade_1_1
horse_grade_1_2
horse_grade_2_1
horse_grade_2_2
horse_grade_3_1
horse_grade_3_2
horse_grade_4_1
horse_grade_4_2
RACES
race_id
race_info <whatever other info relevant to the result>
RESULTS
result_id
race_id
horse_id
result_points
Then if you want to find which horse has the highest amount of points you can use SELECT and GROUP BY and query multiple tables
I have created the first two tables. However i have come across my first problem. I assume in the ENTRANTS table for horse_grade_1_1 etc... we want the horse_id from the first table rather than the actual horses name.
How is it best to create a page to insert data into the ENTRANTS table. For example at the moment i have the code (within a form and already connected to the DB):
<select name="horse_1">
<option selected></option>
<?
$query = "select * from a where horse_grade='bn' ";
$result = mysql_query($query) or die("");
$i=0;
while ($row= mysql_fetch_array($result))
{$horse=mysql_result($result,$i,"horse_name");
$id=mysql_result($result,$i,"horse_id");?>
<option>
<? echo "$id" ;
echo " $horse";
echo "</option>";
++$i;
}?>
</select>
This however of course sends both the horse_id and horse_name forward as $horse_1. I guess what we are looking for is displaying the horse name to users but only forwarding the ID for the insert statement.
<option value="<?php echo $id;?>"><?php echo $horse?></option>
Then when you submit the form the $_POST variable will be submitted the id, but the users only see the horse name.
Try to use the <?php opening tag instead of the <? shorthand tag wherever possible. As far as I am aware the shorthand version doesn't receive 100% support on by different parsers.
That has worked a treat. I will carry on with table three and the next stage tommorow. I shall no doubt get stuck again at some point.
Will also try to remember to use <?php tag in future. Quick replace and find has sorted the old page.
I have tried to figure out what table i need to create where i can input the 3 horses that score points in each race. Would i need to insert each horse on a different row with the number of points it got. e.g. do 3 inserts for each race?
