Form Into MYSQL Problem

[RMelnikas]

I Currently have a form where we have people putting an application forward and the form consists of this code:

<label for="age">Age Group:<span class="red">*</span></label><br/>//here!!!!
							<input name="age" type="checkbox" value="11s">11/under
							<input name="age" type="checkbox" value="13s">13/under
							<input name="age" type="checkbox" value="17s">17/under
							<input name="age" type="checkbox" value="open">Open
							<em>If trailing for two age groups please ensure both age groups are ticked, refer to Rep Protocols items 5.6 &amp; 5.7</em>

We have an area where the applicant have to select the age group, they are able to select two age groups but when submitted into the database it takes the lower one in the selected check boxes. Is there a way to allow the field if two are selected to enter two.

Also Second Question apart from that when the data is entered into the mysql database it creates the new row with the information but also creates a row with nothing in it just blank data or zeros. Whats the best way to stop this?
Here is the php database code:

<?php
//Define Parameters
$DBhost = "localhost";
$DBuser = "bdna";
$DBpass="2010";
$DBname="bdna";
$table="trails";

//connect to DB
$con = mysql_connect($DBhost,$DBuser,$DBpass);
if(!$con) { die('Could not connect to database: ' . mysql_error()); }

//Select Database
mysql_select_db("$DBname") or die("Unable to select database $DBname");

//Insert Data Into MYSQL
$sql="INSERT INTO $table (id, Name, Address, Suburb, Postcode, HomePhone, Mobile, DateofBirth, AgeNextYear, VNA, Email, ParentMobile, Club, AgeGroup, Position1, Position2)
VALUES(' ','$_POST[Name]','$_POST[Address]','$_POST[Suburb]','$_POST[Postcode]','$_POST[Phone]','$_POST[Mobile]','$_POST[dobd]/$_POST[dobm]/$_POST[doby]','$_POST[nextage]','$_POST[VNA]','$_POST[email]','$_POST[parentM]','$_POST[Club]','$_POST[age]','$_POST[pref1]','$_POST[pref2]')";

//Executes
if(!mysql_query($sql))
{
	die('Error: ' . mysql_error());
}
//Close Connection
mysql_close($con)
?>

[cwarn23]

Hi and let me be the first to say you should really use the mysql_real_escape_string() on all of the variables you are putting into the $sql variable. Also as per removing blank values all I can suggest is an if statement on the individual $_POST variables before you make the sql query. For example you could check if $_POST is empty and if it is then assign a value to it. Hope that helps :)

[Smeagel13]

I second the mysql_real_escape_string() ...

Not had much time to look but one thing that pops out is you're inserting a blank ID, just remove the ID field from the query and make sure it's set to auto_increment in the DB.

And I think from what you're asking, is you need to create additional age groups in the DB, Age_Group_Opt_1, Age_Group_Opt_2, Age_Group_Opt_3 and insert the values into those and if the user didn't give a value, then insert a NULL

[Stefano Mtangoo]

PDO or MySQLi is replacement for mysl_**
I propose using PDO if database portability is an issue otherwise use mysqli_** as it is good replacement (for legacy code) than PDO

[RMelnikas]

Smeagel13 thanks that worked and thank your for the help with the real escape string just new to sql

[RMelnikas]

I have one problem left that is when the code executes and sends to the database the information gets added, then another row gets added with no information in it at all??

[Smeagel13]

Can you insert this at line 19 and post the result:

echo $sql;

[RMelnikas]

the output was

INSERT INTO trails (Name, Address, Suburb, Postcode, HomePhone, Mobile, DateofBirth, AgeNextYear, VNA, Email, ParentMobile, Club, AgeGroup11, AgeGroup13, AgeGroup17, AgeGroupO, Position1, Position2, Signature) VALUES('','','','','','','//','','','','','','','','','','','','')

[cwarn23]

<?php
//Define Parameters
$DBhost = "localhost";
$DBuser = "bdna";
$DBpass="2010";
$DBname="bdna";
$table="trails";

//connect to DB
$con = mysql_connect($DBhost,$DBuser,$DBpass);
if(!$con) { die('Could not connect to database: ' . mysql_error()); }

//Select Database
mysql_select_db("$DBname") or die("Unable to select database $DBname");
foreach ($_POST AS $key=>$value) {
if (!is_array($value)) {
    $_POST[$key]=mysql_real_escape_string($value);
    }
}
//Insert Data Into MYSQL
$sql="INSERT INTO $table (id, Name, Address, Suburb, Postcode, HomePhone, Mobile, DateofBirth, AgeNextYear, VNA, Email, ParentMobile, Club, AgeGroup, Position1, Position2) VALUES(' ','{$_POST['Name']}','{$_POST['Address']}','{$_POST['Suburb']}','{$_POST['Postcode']}','{$_POST['Phone']}','{$_POST['Mobile']}','{$_POST['dobd']}/{$_POST['dobm']}/{$_POST['doby']}','{$_POST['nextage']}','{$_POST['VNA']}','{$_POST['email']}','{$_POST['parentM']}','{$_POST['Club']}','{$_POST['age']}','{$_POST['pref1']}','{$_POST['pref2']}')";

//Executes
if(!mysql_query($sql))
{
	die('Error: ' . mysql_error());
}
//Close Connection
mysql_close($con)
?>

[RMelnikas]

Added that. This is still bugging me though as i dont know a lot about SQL and from php this image shows what i mean [IMG]http://i897.photobucket.com/albums/ac175/Melnikas/trials.png[/IMG]

[Stefano Mtangoo]

Added that. This is still bugging me though as i dont know a lot about SQL and from php this image shows what i mean [IMG]http://i897.photobucket.com/albums/ac175/Melnikas/trials.png[/IMG]

Why do you put POST variable directly into query?
clean them or use PDO bind.
Here is a short example. Change it to suit your need. MySQL future is not blue so make sure you use MySQLi or PDO

<?php
$db = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$stmt=$db->prepare("INSERT INTO $table(id, Name, Address, Suburb) VALUES(:id, :name, :address)");
$stmt->BindParam(":id"," ");
$stmt->BindParam(":id", $_POST['Name']);
$stmt->BindParam(":id", $_POST['Address']); 
$stmt->BindParam(":id", $_POST['Suburb']); 
$stmt->execute();

?>

[cwarn23]

Then I conclude that your users are double posting via redirect (design flaw). This will probably be my last post unless there is a reply in the next 12 hours as I will be away from keyboard for 4 days (due to medical reasons) and hopefully will be back better than before. Try the following. I had this problem half a decade ago until I solved it and due to a habit never had it again.

<?php
//Define Parameters
$DBhost = "localhost";
$DBuser = "bdna";
$DBpass="2010";
$DBname="bdna";
$table="trails";

//connect to DB
$con = mysql_connect($DBhost,$DBuser,$DBpass);
if(!$con) { die('Could not connect to database: ' . mysql_error()); }

//Select Database
mysql_select_db("$DBname") or die("Unable to select database $DBname");
if (isset($_POST) && !empty($_POST)) {
foreach ($_POST AS $key=>$value) {
if (!is_array($value)) {
    $_POST[$key]=mysql_real_escape_string($value);
    }
}
//Insert Data Into MYSQL
$sql="INSERT INTO $table (id, Name, Address, Suburb, Postcode, HomePhone, Mobile, DateofBirth, AgeNextYear, VNA, Email, ParentMobile, Club, AgeGroup, Position1, Position2) VALUES(' ','{$_POST['Name']}','{$_POST['Address']}','{$_POST['Suburb']}','{$_POST['Postcode']}','{$_POST['Phone']}','{$_POST['Mobile']}','{$_POST['dobd']}/{$_POST['dobm']}/{$_POST['doby']}','{$_POST['nextage']}','{$_POST['VNA']}','{$_POST['email']}','{$_POST['parentM']}','{$_POST['Club']}','{$_POST['age']}','{$_POST['pref1']}','{$_POST['pref2']}')";

//Executes
if(!mysql_query($sql))
{
	die('Error: ' . mysql_error());
}
//Close Connection
mysql_close($con)
}
?>

[Stefano Mtangoo]

I will be away from keyboard for 4 days (due to medical reasons) and hopefully will be back better than before.

May God grant you a good health. Get well soon!

[Smeagel13]

Where is this code? If it's at the top of the HTML page then when you request the page (GET) you insert blank values into the database and then when the user submits the form (POST) the real values get inserted.

If this is the case then do the following:

<?php

  if($_SERVER['REQUEST_METHOD'] == 'POST') {

    // Do PHP Code (Form Insert)

  }

  // If the IF is false, no PHP code gets executed -- no blank submit.

?>