need help starting a db for and election page

[ROCKINDANO]

Hello,

 

I picked up a project to do a php page using mysql on and election race.

 

this is the data that I need:

There are 7 polling locations and 2 runners.

I need to do a db for someone to enter the counts on to the db.

 

i am having trouble coming up with the scheme for the db on how to have the votes for all 7 polling locations and tie them to the 2 runners. Any suggestions or can someone guide me to a start?

 

Thank you in advance

[Muddy_Funster]

At first glance I would go with

 

tbl_runners

runner_id -- TINYINT(2), AUTO_INC, PK

first_name -- VARCHAR (50)

last_name --  VARCHAR (50)

 

tbl_location

location_id -- TINYINT(2), AUTO_INC, PK

name --  VARCHAR (255)

address1 -- VARCHAR (120)

address2 --  VARCHAR (120)

address3 --  VARCHAR (120)

postcode --  VARCHAR (9)

 

tbl_votes

vote_id -- INT(20), AUTO_INC, PK

location_id -- TINYINT(2), FK

runner_id -- TINYINT(2), FK

votes -- INT (255)

 

where PK is Primary Key and FK is Foreign Key

 

[ROCKINDANO]

Thank you so much i'll try to use this as a guide.

[ROCKINDANO]

on the location table, should i put address1 through address7?

[Muddy_Funster]

If you are going to have 7 lines of address then sure make 7 address fields.

[mikosiko]

one polling location has 7 addresses?... or you have 7 polling locations each one with only one address?

 

review this table design:

tbl_location
location_id -- TINYINT(2), AUTO_INC, PK
name --  VARCHAR (255)
address1 -- VARCHAR (120)
address2 --  VARCHAR (120)
address3 --  VARCHAR (120)
postcode --  VARCHAR (9)

[ROCKINDANO]

I have seven polling location with one address.

[mikosiko]

as I thought....  and as I said...

 

this table design should be checked (unless each address could have 3 lines 120 characters each... which will be extremely long)

 

tbl_location
location_id -- TINYINT(2), AUTO_INC, PK
name --  VARCHAR (255)
address1 -- VARCHAR (120)
address2 --  VARCHAR (120)
address3 --  VARCHAR (120)
postcode --  VARCHAR (9)

 

and related with that.... the answer to your previous question:

"on the location table, should i put address1 through address7?"  ... NO

[Muddy_Funster]

Yeah, miko is right.  I thought that you had the need to store seven lines for each locations address.  And by all means reduce the length of the fields, I just made that long to play it safe as I don't know (obviously) what lenth of information you will need to store for each line nad addresses have the potential to be quite long at times (thinking Welsh place names are a good example).

 

As I said, this was just an at a glance recomendation, I assume that if you are in the position to be "picking up projects" then you have a good working knowledge of what you are using.

[ROCKINDANO]

Ok this is what i got.

 

<table width="100%" border="1" cellspacing="1" cellpadding="0">
  <tr>
  		<td> </td>
    <?php 
					if(!($db = @ mysql_connect('localhost', 'dvera', 'letdanin23')))
		{
			print "Error: Could not connect to our database sorry for any inconvience.<br /> Please try at a later time.";
		}						
		 //select which database you want to edit
		mysql_select_db("elections"); 


						$query = "SELECT * FROM locations";
						$result = mysql_query($query);

						//goes through all records in database and retrieves the need ones.
			while($r=mysql_fetch_array($result))
			{	
				   $LocationID=$r["LocationID"];
					$Name=$r["Name"];
				   
				   //displays all info only first three
				   print "<td>".$Name."</td>";

			}//end while loop
			print "</tr><tr>";		
			$query = "SELECT locations.LocationID, locations.Name, runners.RunnerID, runners.FirstName, runners.LastName, votes.VoteID, votes.Votes FROM locations, runners, votes WHERE locations.LocationID = votes.LocationID AND runners.RunnerID = votes.RunnerID";

		$result = mysql_query($query);


			//goes through all records in database and retrieves the need ones.
			while($r=mysql_fetch_array($result))
			{	
				   $RunnerID=$r["RunnerID"];
					$FirstName=$r["FirstName"];
					$LastName=$r["LastName"];
					$VoteID=$r["VoteID"];
					$LocationID=$r["LocationID"];
					$RunnerID=$r["RunnerID"];
					$Votes=$r["Votes"];
					$Name=$r["Name"];
				   
				   //displays all info only first three
            	 print '<td> ' .$FirstName. ' ' .$LastName. '</td>';
					 print "<td>" .$Votes. "</td>";
			}
	   ?>
</tr>         
</table>

 

but this is whats displayed

 

        Place 1 Place 2 Place 3 Place 4 Place 5 Place 6 Place 7

Runner1 255       Runner1 300        Runner2 200

 

I am trying to display a two row 7 column table. Runner2 should be on row 2 and his count on Place 2.

 

any help?

 

[fenway]

"display" has nothing to do with mysql.