This is driving me insane! help please!

by **dpj74** » Wed May 09, 2012 7:59 am

Trying to get code together to basically take data from a HTML form, capture it and save it in a database but i've got errors on lots of different lines and worked my way through but are now stuck on line 16.

Cannot for the life of me see whats wrong here, could someone please kindly advise? thanks in advance:

<?php
$RunnerID = $_POST[ ' RunnerID int' ];
$EventID = $_POST[ ' EventID int' ];
$Date = $_POST[ ' Data date' ];
$FinishTime = $_POST[ ' FinishTime time' ];
$Position = $_POST[ ' Position int' ];
$CategoryID = $_POST[ ' CategoryID int' ];
$AgeGrade = $_POST[ ' AgeGrade float' ];
$PB = $_POST[ ' PB boolean' ];
$dbhost = 'dpj74.tt284.open.ac.uk';
$dbuser = 'dpj74';
$dbpass = '76d2SU9B';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to database');
$dbname = 'database';
mysql_select_db($dbname);
$sql = “INSERT INTO table2 “.
“ (RunnerID, EventID, Date date, FinishTime, Position, CategoryID, AgeGrade, PB) “.
“VALUES ( ' $_POST[RunnerID] ' , ' $_POST[EventID] ' , ' $_POST[date] ' , ' $_POST[FinishTime] ' , ' $_POST[Position] ' , ' $_POST[CategoryID] ' , ' $_POST[AgeGrade] ' , ' $_POST[PB]')”;
echo “Entered data successfully”;
>

by **Nullsig** » Wed May 09, 2012 8:49 am

First question...

Are your posted variables called "<space>RunnerID int","<space>EventID int",etc?

Second Question, can you post the HTML form that is POSTing to this page?

by **dpj74** » Wed May 09, 2012 9:55 am

Thanks for the reply:

heres my html code:

<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-type" content="text/html; charset=iso-8859-1"/>
<title>Submit runner time</title>
</head>
<body>
<hr/>
<h1>Submit runner time</h1>

The int I'm not sure wether to include, they were included in the brief given to me, but threw up errors so removed them, I relise I still have int in the brackets so maybe this needs removing?
<hr/>
Note: all fields are mandatory.
<p/>
<form action="http://ADDRESS REMOVED/files/storedata.php"
method="post" name="submitrunnertime">
<table>
<tr><td>Runner ID</td>
<td><input type="text" name="RunnerID" size="5" maxlength="5"/></td>
</tr>
<tr><td>Event ID</td>
<td><input type="text" name="EventID" size="5" maxlength="5"/></td>
</tr>
<tr><td>Date (YYYY-MM-DD)</td>
<td><input type="text" name="Date" size="10" maxlength="10"/></td>
</tr>
<tr><td>Finish time (HH:MM:SS)</td>
<td><input type="text" name="FinishTime" size="8" maxlength="8"/></td>
</tr>
<tr><td>Position</td>
<td><input type="text" name="Position" size="5" maxlength="5"/></td>
</tr>
<tr><td>Category ID</td>
<td><input type="text" name="CategoryID" size="2" maxlength="2"/></td>
</tr>
<tr><td>Age grade</td>
<td><input type="text" name="AgeGrade" size="5" maxlength="5"/></td>
</tr>
<tr><td>Personal best</td>
<td><input type="text" name="PB" size="1" maxlength="1"/></td>
</tr>
</table>
<input type="submit" name="submitrunnertime" value="submit"/>
<hr/>
</form>
</body>
</html>

by **dpj74** » Wed May 09, 2012 9:55 am

Thanks for the reply:

heres my html code:

<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-type" content="text/html; charset=iso-8859-1"/>
<title>Submit runner time</title>
</head>
<body>
<hr/>
<h1>Submit runner time</h1>

The int I'm not sure wether to include, they were included in the brief given to me, but threw up errors so removed them, I relise I still have int in the brackets so maybe this needs removing?
<hr/>
Note: all fields are mandatory.
<p/>
<form action="http://ADDRESS REMOVED/files/storedata.php"
method="post" name="submitrunnertime">
<table>
<tr><td>Runner ID</td>
<td><input type="text" name="RunnerID" size="5" maxlength="5"/></td>
</tr>
<tr><td>Event ID</td>
<td><input type="text" name="EventID" size="5" maxlength="5"/></td>
</tr>
<tr><td>Date (YYYY-MM-DD)</td>
<td><input type="text" name="Date" size="10" maxlength="10"/></td>
</tr>
<tr><td>Finish time (HH:MM:SS)</td>
<td><input type="text" name="FinishTime" size="8" maxlength="8"/></td>
</tr>
<tr><td>Position</td>
<td><input type="text" name="Position" size="5" maxlength="5"/></td>
</tr>
<tr><td>Category ID</td>
<td><input type="text" name="CategoryID" size="2" maxlength="2"/></td>
</tr>
<tr><td>Age grade</td>
<td><input type="text" name="AgeGrade" size="5" maxlength="5"/></td>
</tr>
<tr><td>Personal best</td>
<td><input type="text" name="PB" size="1" maxlength="1"/></td>
</tr>
</table>
<input type="submit" name="submitrunnertime" value="submit"/>
<hr/>
</form>
</body>
</html>

by **Nullsig** » Wed May 09, 2012 10:33 am

Based on the form you have you this should be the code of your submit page. Here is what I think your code for your submit page should be.

Code: Select all: <?php $dbhost = 'dpj74.tt284.open.ac.uk'; //probably didn't want to include this $dbuser = 'dpj74'; //probably didn't want to include this $dbpass = '76d2SU9B'; //probably didn't want to include this $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to database'); $dbname = 'database'; mysql_select_db($dbname); $sql = “INSERT INTO table2 “. “ (RunnerID, EventID, Date date, FinishTime, Position, CategoryID, AgeGrade, PB) “. “VALUES ('" . mysql_real_escape_string($_POST[RunnerID]) . "','" . mysql_real_escape_string($_POST[EventID]) . "','" . mysql_real_escape_string($_POST[date]) . "','" . mysql_real_escape_string($_POST[FinishTime]) . "','" . mysql_real_escape_string($_POST[Position]) . "','" . mysql_real_escape_string($_POST[CategoryID]) . "','" . mysql_real_escape_string($_POST[AgeGrade]) . "','" . mysql_real_escape_string($_POST[PB] . "')”; if($rs = mysql_query($sql)){ echo “Entered data successfully”; }else{ echo "Insert Failed"; } ?>

I have added the "mysql_real_escape_string" function to all of your inserted variables. This is used to prevent SQL Injection. If you don't know what that is read up on it .

I have also removed all of the assignments you did at the beginning, they were not being used in the query. If you were trying to use that section of code to validate the inputs, we can deal with that in another post.

Now in my next post I will go through some things you can do to improve upon this....

by **Nullsig** » Wed May 09, 2012 10:55 am

So the first thing I am going to address is your from construction. I will talk about each field to point out some things that will allow you to improve the data from your form.

Your first field is "Runner ID" this is actually put together correctly. You will need to validate after submit to ensure that this is a valid value. Assuming you are talking about the Bib Number you can use this code in your PHP to check if the value is an integer:

Code: Select all: if(!is_int($_POST['RunnerID'])){ echo "Invalid Runner ID!"; }

Next field you are using is Event ID. This should probably be a drop down menu. The reasoning is, there are going to be a limited number of Events and it simplifies the user's input to "choose one of these" which is harder to screw up:

Code: Select all: <tr><td>Event ID</td> <td> <select name='EventID'> <option value=''>--SELECT ONE--</option> <option value='1'>Warrior Dash</option> <option value='2'>Urbanathlon</option> <option value='3'>Boston Marathon</option> <option value='4'>Tough Mudder</option> <option value='5'>Iron Man Triathlon</option> </select> </td> </tr>

All you have to do is validate that a value is chosen:

Code: Select all: if(empty($_POST['EventID'])){ echo "You must select an event"; }

Moving on to your third value, Date. Normally I have a JQuery script I run that forces the user to choose a date from a calendar which then pushes the chosen date into a field using the format I want. An easier way to do this is to separate the date field into 3 separate drop down menus: one for Year (2011-2020), one for month(Jan-Dec), one for date(1-31). This will allow you to limit the user's choices to mostly correct values, and you can validate the date on the back end. The code for each drop down is the same as the above code segment. You will just need to alter the options and you would want to give each menu a different name.

On submit of the 3 date fields you will need to validate just in case the user chose nothing or chose a date like February 31st. You can do that with this code:

Code: Select all: if(!empty($_POST['month']) && !empty($_POST['day']) && !empty($_POST['year'])){ if(checkdate($_POST['month'], $_POST['day'] , $_POST['year'])){ echo "Invalid Date!"); } }else{ echo "You must select a value for day, month, and year"; }

Moving along you should apply the same logic to the "Finish Time" field. You can give the users sane fields for hours(0-24), minutes(0-59) and seconds(0-59). You would only have to validate that none of the fields are blank since there are not any unacceptable values to choose from.

Position should probably be treated like the RunnerID field. Just validate that the entry is an integer.

Category and Age Grades should be treated just like the Event field. Give the users a choice instead of expecting them to enter legitimate values.

The last field "Personal Best" should be changed to a check box field.

Code: Select all: <input type="checkbox" name="PB">

Check boxes only post values when they are checked so to validate it you will do this:

Code: Select all: if(isset($_POST['PB'])){ $PB = 1; }else{ $PB = 0; }

Hope this helps.

by **freshnet** » Wed May 09, 2012 11:09 am

You can also add some html to the form to back this up, e.g. you could use a datalist with a required attribute instead of a dropdown. This is supported in Firefox and Opera, otherwise it just shows as an input box. I believe that the 'required' attribute works in everything except IE.

<input list="events" required/>
<datalist id="events">
<option value="Ironman">
<option value="Boston marathon">
<option value="NY marathon">
</datalist>

You can also use the 'number' type to provide some pre-validation of the numbers (or dates if you opt for a separate year/month/day approach). Again this works in most browsers (I believe Firefox and IE treat it as a regular input).
<input type="number" name="quantity" min="1" max="5" />

by **dpj74** » Wed May 09, 2012 11:32 am

Thankyou so much! you have been so very helpful!

Adding validation was the next step in the process and the points you made are exactly what I need!

Before this though I need to get my php code working correctly to take the data from the html form and add it to the database, currently I have an error on line 16 of my php code which is preventing the code being saved to the database. The database has already been setup on an online server and from what output I currently get, connecting to the database is fine, the problem I got is line 16 throws an error.

Thanks so much

by **Nullsig** » Wed May 09, 2012 11:39 am

Freshnet is correct however, the words "works in everything except IE" or "Browser x treats it as a normal input" will prevent the code from working with all users.

The methods I have outlined are browser independent.

by **Nullsig** » Wed May 09, 2012 11:42 am

Sorry it seems my code was missing a closing ")" for one of the mysql_real_escape_string calls.

Here is the fixed code (I also replaced those wonky quotes):

Code: Select all: <?php $dbhost = 'dpj74.tt284.open.ac.uk'; //probably didn't want to include this $dbuser = 'dpj74'; //probably didn't want to include this $dbpass = '76d2SU9B'; //probably didn't want to include this $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to database'); $dbname = 'database'; mysql_select_db($dbname); $sql = "INSERT INTO table2 ". "(RunnerID, EventID, Date date, FinishTime, Position, CategoryID, AgeGrade, PB)" . "VALUES ('" . mysql_real_escape_string($_POST[RunnerID]) . "','" . mysql_real_escape_string($_POST[EventID]) . "','" . mysql_real_escape_string($_POST[date]) . "','" . mysql_real_escape_string($_POST[FinishTime]) . "','" . mysql_real_escape_string($_POST[Position]) . "','" . mysql_real_escape_string($_POST[CategoryID]) . "','" . mysql_real_escape_string($_POST[AgeGrade]) . "','" . mysql_real_escape_string($_POST[PB]) . "')"; if($rs = mysql_query($sql)){ echo “Entered data successfully”; }else{ echo "Insert Failed"; } ?>

by **dpj74** » Wed May 09, 2012 1:20 pm

ok using the code given i get error:

Parse error: syntax error, unexpected T_STRING in /var/www/vhosts/dpj74.tt284.open.ac.uk/httpdocs/files/storedata.php on line 2

by **dpj74** » Thu May 10, 2012 3:27 am

Thanks again guys for all the help, tweaked the code and now the code seems to be working, but now getting an output

'Insert Failed'

So it doesn't seem to be posting the info to the database? from what I can see it is connecting to the database fine.. but doesn't seem to be able to add data?

by **Nullsig** » Thu May 10, 2012 4:23 am

Change this line:

Code: Select all: echo "Insert Failed";

To this:

Code: Select all: echo "Insert Failed Query is:<br><br>" . $sql . "<br><br>" . mysql_error();

Then rerun the code. If the submit fails the query will be echo'd out and you can try and run the PHP generated query in your MySQL console or PHPMyAdmin.

This code will also echo out the mysql_error telling you where the query failed.

If you have trouble diagnosing the problem from there. Post the Query and the Error and we can go from there.

by **dpj74** » Thu May 10, 2012 6:05 am

Changed code and I get output of :

Insert Failed Query is:

INSERT INTO Results (RunnerID, EventID, Date, FinishTime, Position, CategoryID, AgeGrade, PB)VALUES ('1','1','1','1','1','1','1','1')

No database selected

by **Nullsig** » Thu May 10, 2012 6:09 am

The "No Database Selected" seems to indicate that this line failed:

Code: Select all: mysql_select_db($dbname);

Change that line to:

Code: Select all: mysql_select_db($dbname) or die("Could not find database " . $database);

by **dpj74** » Thu May 10, 2012 6:12 am

Yea as your prob predicted this gives output of:

Could not find database

Seems more of a syntax error now, on the server the database is simply named 'database' will look into it some more

by **dpj74** » Thu May 10, 2012 6:18 am

Had wrong database name!! just corrected this and are now getting 'database updated successfully' message!

Now need to add an echo message to display a confirmation of what data was added to the database

You have been so amazingly helpful!!!!!!!!!!!!!!

Thankyou so much!!!

by **freshnet** » Thu May 10, 2012 10:22 am

good luck!

Btw what I meant with my post was that you can use modern html to do some extra checking before the form is submitted. The fact that it doesn't work on all browsers doesn't really matter.

by **Nullsig** » Thu May 10, 2012 10:27 am

If you have to check for the non-compatible browsers anyway, why would you execute extra checks on the compatible browsers? You are essentially punishing those with compatible browsers with slightly longer processing time at no added benefit.

by **freshnet** » Thu May 10, 2012 10:40 am

the extra html doesn't add to the processing time. Try it on a new browser, it won't even let you submit the form if a required checkbox isn't filled out. The extra benefit is just that: you don't have to wait for the page to process to tell you you're missing something.

by **Nullsig** » Thu May 10, 2012 11:02 am

That validation is executed client side, it is basically JavaScript validation in how it is executed. So you validate using your browser instead of JavaScript, but the validation is still executed on the user's PC. Then after it has been validated you still have to deal with the server-side validation that has to be there since there is not 100% browser compatibility.

Additional processes are consumed, they just aren't consumed on your server. They are instead consumed on the typically MUCH SLOWER, user PC.

by **freshnet** » Thu May 10, 2012 3:02 pm

It's not javascript because it still works in the browser if javascript is disabled. Anyway, a tiny bit of script (whatever it is) on the client side that is done before the form I think will always provide a better user experience. That surely is why all of these things were added in html5 in the first place because previously people were using ugly javascript which was sometimes slow to do the same thing.

by **Nullsig** » Fri May 11, 2012 6:08 am

They designed these elements into HTML5 because the JavaScript is exploitable. I can in 5 minutes get past any JavaScript validation that doesn't bounce off of PHP.

I know it's not JavaScript that's why I pointed out that the Browser is doing the validation not JS.

The point is that regardless, you have to validate the variables using PHP since you can't guarantee the user is going to use an HTML 5 compatible browser.

Why introduce an extra step to the process? The only justification is, as you said, user experience. Since the validation is done without a full refresh they don't have to deal with network latency to figure out if they have correctly filled out the form.

Even when this is fully adopted, since the validation is happening on the client and not on the server, it is still very insecure. When you are dealing with database transactions that is not something you want.

Arguably the HTML 5 elements are easier to bypass than the JavaScript, but only slightly.

by **freshnet** » Fri May 11, 2012 6:35 am

exactly.. user experience. On the web user experience counts for a lot!

by **Nullsig** » Fri May 11, 2012 6:40 am

It does, but currently the browsers with the highest market share are partially, if not completely, incompatible with the HTML5. So you would have to resort to a blanket fix of JavaScript anyway.

This is driving me insane! help please!

This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Re: This is driving me insane! help please!

Who is online