inserting new records- but want to know fields from other tables too?

[thunderstorm654]

Hi,

I am doing a very simple website for a running club,and am rather new to mysql and php. The database just has 3 tables, member , race, and results.

 

member has stuff about the member, primary key is member_no.

 

race holds race details, e.g. race_no (primary key), the race name, etc.

 

results holds results for each race, primary key is member_no and race_no, the only other data recorded is the time.

 

basically i want a page where you can enter the results for a particular race. but im not sure how to do this, since it is pretty unreasonable to expect someone to remember a race's id no and a members id no, how can you allow them to enter (on a simple html form on the site) for example the members first name, the race's name AND its date (as some races are called the same on different days) along with the result time and then just update the results table?

 

i dont think this should be too hard at all, but im getting just slightly confused how to approach it,sorry for the rather n00bish post. cheers for any help!

[monk.e.boy]

So they enter all the stuff you said.

 

Then do an SQL select to get the race id WHERE race_name LIKE 'name' AND date='date'

 

Now use the results of this query (the race ID) to update the other table.

 

monk.e.boy

[thunderstorm654]

hi, thanks for your reply, i have figured out a way (at least i thought) of doing it, that i can't see why isn't working - it keeps stating that the member or race does not exist. here is an example of query used to get the race_no from the race_name and race_date which are typed into the form (nb they have been escaped, hence $rn and $rd)

 

// make query to get the race_no

 

$query_raceno = "SELECT race_no FROM race WHERE race_name = $rn AND race_date = $rd";

$race_result = mysql_query($query_raceno); // run the query

 

if($row['race_no'] ==  0)

{

echo "Error: That race does not exist in the database, please try again.";

// return them to form

}

else

{

$row = mysql_fetch_assoc($race_result);

$race_no = $row['race_no']; // then can use $race_no in insert statement

 

}

 

it keeps throwing the Error: That race does not exist in the database, please try again.

Any ideas? thanks

[monk.e.boy]

put quotes around strings in the query.

 

$query_raceno = "SELECT race_no FROM race WHERE race_name = '$rn' AND race_date = '$rd'";

 

This is probably the most inefficient and slow way of doing what you want - but unless you're getting 1,000s of updates I would just remember that this page could be optimized one day.

 

monk.e.boy

 

 

[thunderstorm654]

No, its not going to have very heavy usage at all, but would be interested to hear how it could be improved.

However, I fixed the pretty obvious mistake i made , but it hasn't made any difference, still claims races/members do not exist.

Could there be anything else wrong?

[monk.e.boy]

yeah, you have to get the race name and date perfect when you type them in. That's why this way of doing stuff is a bit evil. Are you typing the date like '2007-03-03' ? This is how the database expects to see it.

 

Look up the MySQL date type in google.

 

Most sites would give you options to choose a race from a drop down (for example) so you don't have to type the name in.

 

Also you should filter stuff by the date, so show the user races today, this week, this month, next month, this 6 months etc...

 

Most popular races filter to the top of lists (SQL = ORDER BY number_of_people_in_race)

 

Then when the user chooses a race, the race ID is passed to the page that has the SQL UPDATE commend in it, then the user never has to type anything as complex as a race name. I mean : Race for life, race for life. Note the different capitals. This will throw the DB query.

 

monk.e.boy

[thunderstorm654]

i can understand thats a much better way of doing it,. but unfortunately having enough trouble figuring out the 'crap' way, I haven't done any PHP till this. I'd like to improve it like you suggested, but not sure how yet and can't even get this one working..

Yup, i knew that would happen with the date and time so have typed them in exactly as they appear in the table  :-\

[monk.e.boy]

Download SQLLog,

 

connect this to you database.

 

Type in the SQL and get it working.

 

Then echo your SQL from the PHP page and compare the two.

 

Fix the PHP

 

run.

 

What does echoing the SQL from your PHP page show anyway?

 

monk.e.boy

[thunderstorm654]

how do you echo sql from the php page? googled that but couldnt find anything useful.

[monk.e.boy]

how do you echo sql from the php page? googled that but couldnt find anything useful.

 

 

echo '<br><br>MY SQL:<br><br>';
echo $query_raceno;
echo '<br><br>----END----<br><br>';

 

put that somewhere, browse to the page like normal (i.e. fill in the form and click submit) then copy and past the SQL.

 

monk.e.boy

[thunderstorm654]

ok, it just says

 

MY SQL:

 

SELECT race_no FROM race WHERE race_name = 'Stratford Half' AND race_date = '2006-04-25'

 

----END----