Drop down menu using PHP to pull data from mysql database.

Discussion in 'PHP' started by Uncle Dave, Aug 19, 2009.

0
  1. #1
    Hi guys,

    I've made a website for a few mates who organise a running race. www.coast2kosci.com .

    One of the reasons I started tinkering with php and mysql is for things like the results section. Rather than creating a new html page for each year, I'd like to do one page with a dropdown menu, so people could select the year, and it would pull the results (firstname, lastname, time, position, gender_position ) for that year.

    What I have accomplished - I've managed to create two mysql tables (one of entrants and one for results), and figure out the sql code to display any given year. (example showing 2008's results is at www.coast2kosci.com/results.php )

    Where I need help - I'd love any advice one how I can link this sql code to a dropdown menu, so that the 2004 option pulls the 2004 results, 2005 pulls the results from that year, and so on.

    I might just put the code in for the body of the php page to which I linked above. Currently the menu is not linked to the sql statement - I tried various ways of doing this, but all were unsuccessful. So currently the sql statement for 2008 is sitting below the form.

    Again, any tips would be much appreciated,

    Dave

    

<body>


  
<form id="form1" name="form1" method="post" action="">
  <label>Year
  <select name="Year" id="Year">
    <option value="??"
>2004</option>
    <option value="??">2005</option>
    <option value="??">2006</option>
    <option value="??">2007</option>
    <option value="??">2008</option>
  </select>
  </label>
  <p>
    <label>
    <input type="submit" name="submit" id="submit" value="submit" />
    </label>
	

  </p>
</form>


			  <?php


  mysql_connect ("localhost", "MY_USERNAME", "MY_PASSOWRD");
  mysql_select_db ("coast2ko_results");

// Get all the data from the results and entrants tables


$result = mysql_query("SELECT firstname, lastname, time, position, gender_position FROM entrants, results
WHERE results.entrantid = entrants.entrantid
AND year = '2008'
ORDER BY time ASC
")
or die(mysql_error());


echo "<table border='1' align='center' cellpadding='10' cellspacing='0' bordercolor='#000000'>";
echo "<tr bgcolor='#6175BE'> <th>firstname</th> <th>lastname</th>  <th>time</th> <th>position</th> <th>gender position</th></tr>";


// keeps getting the next row until there are no more to get


while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr bgcolor='#CCCCCC'><td>";
	echo $row['firstname'];
	echo "</td><td>";
	echo $row['lastname'];
	echo "</td><td>";
	echo $row['time'];
	echo "</td><td>";
	echo $row['position'];
	echo "</td><td>";
	echo $row['gender_position'];
	echo "</td></tr>";
}

echo "</table>";

?>

			

</body>
    Code (markup):
     
    Uncle Dave, Aug 19, 2009 IP
  2. premiumscripts

    premiumscripts Peon

    Messages:
    1,062
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #2 
    
if (empty($_GET['year'])) {
   $_GET['year'] = date('Y');
} else {
   $_GET['year'] = (int) $_GET['year'];
}

//...

$result = mysql_query("SELECT firstname, lastname, time, position, gender_position FROM entrants, results
WHERE results.entrantid = entrants.entrantid
AND year = " . mysql_real_escape_string($_GET['year']));
    PHP:
     
    premiumscripts, Aug 19, 2009 IP
  3. Uncle Dave

    Uncle Dave Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks premiumscripts....at the risk of sounding a bit silly, can you give me a tip on where I insert that code in relation to the code I posted?

    I've tried various options but none have worked so far.

    Thanks again,
    Dave
     
    Uncle Dave, Aug 19, 2009 IP
  4. premiumscripts

    premiumscripts Peon

    Messages:
    1,062
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Just place it where you have the current mysql query ($result = ..) (replace that one with this code)

    Also, you'll need to change your form to GET instead of post.. If it doesn't work you can try accessing your site like this: script.php?year=xxx
     
    premiumscripts, Aug 19, 2009 IP