drop down box to filter data by a certain column/'category'

[thunderstorm654]

Hi, i've got three tables, member, race and results.

Member primary key is member_no, race is race_no and results is a composite of race_no and member_no.

 

Basically I want a page where users can view race results - i.e. show the members first name and last name (from member table), the race name, race date, race length (from race table), and the result (which is a time, from results table). I want to display all this as column headings and output the relevant data just in a table.

 

BUT, i want to allow the user to be able to view results for certain races, so i thought having a drop down box with race names in then somehow reloading the page and displaying just results for that race name would be the most suitable way.

 

All i've managed to do so far is get a drop down box with the race names in however - eek! i have done the join query yet to get all the data as I was experiementing, i selected everything from race table and stuck the race_name in the combo box, but do i just output the other fields into rows or something in the table? how??

 

this is my miniscule amount of code so far..

 

<?php
require_once('connection.php');
echo'<select name="categories">';

$res=mysql_query("select * from race");
if(mysql_num_rows($res)==0) echo "there is no data in table..";
else
for($i=0;$i<mysql_num_rows($res);$i++) {
$row=mysql_fetch_assoc($res);
echo"<option>$row[race_name]</option>";
}
echo'</select>';

?>

 

How can i go about making this page how I want? This has been confusing me for days as it surely can't be that hard, i've searched online for hours but not found anything just like i want.

 

Thanks

[boo_lolly]

first, i don't see why you'd need more than one table, especially if they're really only containing one or two columns. can you elaborate on why you think you need more than one table? that would be the first place to start approaching this issue entirely.

[thunderstorm654]

sorry i think i must have been a bit confusing- i only want one table on the page yes, but the data inside the single table will come from 3 tables in the database.

[boo_lolly]

my question was, why do you need 3 tables in the database?

[thunderstorm654]

oh right sorry, No idea, its coursework for uni and we got told the tables and aren't allowed to changed them, so its my lecturers fault!

[thunderstorm654]

I guess don't really need the three, but unfortunately got to keep them.

[boo_lolly]

why don't you post the exact assignment that your prof gave you, so we can help you.

[thunderstorm654]

Okay I have attached it, unfortunately is a pdf. I have done everything apart from the last bit- Part 2, Number 4., on page 3 of the pdf file.

 

The way I am trying to do it is not necessarily the only way, I just thought it would be the nicest to allow for both parts of that section- viewing results and allowing members to view results of certain races.

 

Cheers

 

[attachment deleted by admin]

[boo_lolly]

buddy this is a really simple assignment. have you started? post all your code and a description of what your code does.

[thunderstorm654]

i know its not too bad, i have done the lot apart from number 4 on part 2 which is what I have been asking about.

 

erm, its quite a lot (numerous pages), do you need to see all the code for the other sections?

 

Here's my code for number 1 of part 2 (allowing users to modify their info..)

 

modify_user.php:

 

<?php
session_start();
include('memberlogin.functions.php');
check_user();
$user = $_SESSION['valid_member'];

require_once('connection.php');

$get_memberdetails_sql = "SELECT * FROM member WHERE email_address = '{$user}'";
$result = mysql_query($get_memberdetails_sql);
$get_memberdetails_res = mysql_fetch_array($result);

$first_name = $get_memberdetails_res['first_name'];
$last_name = $get_memberdetails_res['last_name'];
$gender = $get_memberdetails_res['gender'];
$email_address = $get_memberdetails_res['email_address'];
$address_line_one = $get_memberdetails_res['address_line_one'];
$dob = $get_memberdetails_res['dob'];

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Incredible Shoes Running Club :: Update Your Details</title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
<div id="wrap">

<div id="top"></div>

<div id="content">

<div class="header"> 
<h1><a href="index.php">The Incredible Shoes Running Club</a></h1>
</div>

<div class="breadcrumbs">
<a href="index.php">Home </a> · <a href="admin_home.php">Administrators </a> · <a href="#">Members </a> · <b>Today's date is <?php echo date('D j F, Y ');?>· <a href="member_logout.php">Logout</a></b> 
</div>

<div class="middle">

<h2>Welcome.</h2>

<form action="do_modify_user.php" id="update" name="update" method="post">

<?php echo "Your Email Address: ".$user; ?>
<p>First Name
	<input name="updateFirstName" type="text" id="updateFirstName" value = "<?php echo $first_name;?>"/>
</p>
<p>Last Name
	<input name="updateLastName" type="text" id="updateLastName" value = "<?php echo $last_name;?>"/>"
    </p>   
<p>Gender
	<input name="updateGender" type="text" id="updateGender" value = "<?php echo $gender;?>"/>"
    </p>      
<p>Email Address
	<input name="updateEmail" type="text" id="updateEmail" value = "<?php echo $email_address;?>"/>"
    </p>     
<p>Address Line One
	<input name="updateAddress" type="text" id="updateAddress" value = "<?php echo $address_line_one;?>"/>"
    </p>        
	<p>Date of Birth (YYYY-MM-DD)
	<input name="updateDOB" type="text" id="updateDOB" value = "<?php echo $dob;?>"/>"
    </p>  
    	<input name="update" type="submit" id="update" value="Update Details" />
        <input type="hidden" name="MM_UPDATE" value="updateForm" />
    </p>
    <p align="center"> </p>
</form>
<br />
<hr />
<a href="index.php">Back to home page</a>

</div>

<div id="clear"></div>

</div>

<div id="bottom"></div>

</div>

<div id="footer">
Copyright Incredible Shoes Running Club 2007</a>
</div>

</body>
</html>

 

do_modify_user.php:

 

<?php
session_start();
include('memberlogin.functions.php');
check_user();
$user = $_SESSION['valid_member'];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Incredible Shoes Running Club :: Update Your Details</title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
<div id="wrap">

<div id="top"></div>

<div id="content">

<div class="header"> 
<h1><a href="index.php">The Incredible Shoes Running Club</a></h1>
</div>

<div class="breadcrumbs">
<a href="index.php">Home </a> · <a href="admin_home.php">Administrators </a> · <a href="#">Members </a> · <b>Today's date is <?php echo date('D j F, Y ');?>· <a href="member_logout.php">Logout</a></b> 
</div>

<div class="middle">

<h2>Modify Your Details.</h2>

<?php

require_once('connection.php');

$first_name = $_POST["updateFirstName"];
$last_name = $_POST["updateLastName"];
$gender = $_POST["updateGender"];
$email_address = $_POST["updateEmail"];
$address = $_POST["updateAddress"];
$dob = $_POST["updateDOB"];

$sqlquery = "UPDATE member SET first_name='$first_name',last_name = '$last_name', gender = '$gender', address_line_one = '$address', dob = '$dob' WHERE email_address = '$user'";
$update_result = mysql_query($sqlquery);


	if($update_result) { // if update was successful,
		echo '<h2 id="mainhead">Thank you!</h2>
		<p>Your details were updated successfully.</p>
		<p><a href="admin_home.php">Back to Administrator Home></p>';

	}
	else { // if it did not insert ok
		echo '<h2 id="mainhead">Error</h2>
		<p class="error">Your details were not updated successfully due to a system error, please go back and try again.</p>';
		echo '<p>' . mysql_error() . '<br /><br />Query: ' . $query_update . '</p>'; // debugging message

	}

?>

</div>

<div id="clear"></div>

</div>

<div id="bottom"></div>

</div>

<div id="footer">
Copyright Incredible Shoes Running Club 2007</a>
</div>

</body>

 

if you want to see any other part then let me know... i know its not very good, but tbh our lecturer is shit and wont notice far from perfect coding, as long as I can get the last bit working I don't mind..

[boo_lolly]

ok, i understand that. now, what search features would you like in your search/browse tool? would you just like to display all the races and results in a dynamic fashion, or would you like any user to be able to narrow the search by race, runner, or dates?

 

i can help you with this, no problem.

[thunderstorm654]

Well, cos it only says in the coursework spec about narrowing them down by race name, thats all i really want.. I'd like to have a entire table displaying of all the results, then a drop down above the table allowing the user to select a race name to narrow them down by that race name and just show those results.. e.g. a race called 'blah', choosing blah from the drop down would just list results for races wth the name blah.

 

hope that made sense. thanks