I have an HTML form which submits values to the following PHP file, which inserts them into a MySQL database:
<?php
$con = mysql_connect("*","*","*");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("*", $con);
$sql="INSERT INTO scores (hometeam, awayteam, result)
VALUES
('" . mysql_real_escape_string($_POST['hometeam']) . "',
'" . mysql_real_escape_string($_POST['awayteam']) . "',
'" . mysql_real_escape_string($_POST['result']) . "')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con);
?>
Sometimes an input field in the HTML form will be left blank and in this case I do not want anything inserted into the database. I want the value to remain NULL. At the moment when I fill in my form like this:
Home team: Blue team
Away team: [blank]
Result: Won
The following is inserted into my database:
Home team: Blue team
Away team: ' '
Result: Won
What I want to be inserted/not inserted is:
Home team: Blue team
Away team: NULL
Result: Won
I've hunted hours for a solution. Can anyone help? Thank you.
if (!empty($_POST['hometeam'])) {
$sql="INSERT INTO scores (hometeam) VALUES ('" . $_POST['hometeam'] . "')";
}
Notice the single quotes around the 'hometeam' part. You should also clean that using mysql_real_escape_string($_POST['hometeam']).
Bear in mind this will create upto 3 rows for each call, if you want to have a row like scores (hometeam, awayteam, result) you'll need to construct your query differently (i.e. a single query not 3 seperate ones).
I think there is some problem with the declaration of name of your input field in you html form. Make sure, $_POST[hometeam] must be the same input name in your form
Example: In your form
<input type="text" name="hometeam" value="" />
In your PHP
if (!empty($_POST[hometeam])) {
$sql="INSERT INTO scores (hometeam) VALUES ('$_POST[hometeam]')";
}
And also, please use addslashes or mysql_real_escape_string in your post values before adding it on the database.
Look at this link below:
http://php.net/manual/en/function.addslashes.php
http://php.net/manual/en/function.mysql-real-escape-string.php
First off, there's a huge security flaw in this code, which is not sanitizing your inputs. A user could insert whatever they like and it's executed on the DB without any checking. This is bad. At the very least, you should be using something like mysql_real_escape_string(), even though even that is not exactly the best thing for the job (Google PHP + PDO for example).
Secondly, you're actually executing one query using one variable. If $_POST['result'] is set, then $sql will always be the last value. What you might want to do is make the query like so:
$query = 'INSERT INTO scores ('.$fields.') VALUES ('.$values.')';
And construct the $fields and $values variables using your if(!empty( .. )) code.
But to reiterate SANITIZE YOUR INPUTS
Well it will insert the final value only , because you are executing the $sql and the last values of $sql is "INSERT INTO scores (result) VALUES ('$_POST[result]')"; You are overiding the previous values by putting same variable name.
Also (!empty($_POST[hometeam])) remove the !empty if the fields can be blank sometimes.
You are overwriting your SQL statements each time. Beacue your 'result' field isn't blank, you are setting your SQL statement to:
"INSERT INTO scores (result) VALUES ('$_POST[result]')"
This is the only statement which is then being executed - your other values are being ignored as they are not part of this statement.
What you need to do is set up your variables first:
$hometeam = isset($_POST['hometeam']) ? $_POST['hometeam'] : NULL;
$awayteam = isset($_POST['awayteam']) ? $_POST['awayteam'] : NULL;
$result = isset($_POST['result']) ? $_POST['result'] : NULL;
You can then do your database interaction:
$sql = "INSERT INTO scores hometeam, awayteam, result VALUES $hometeam, $awayteam, $result";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con);
I should say that I haven't included any security on this - you should look into PDO or prepared statements to make sure your database isn't open to SQL Injection.
Hope this helps!
3 insert into statements will insert 3 records, with unspecified fields left as null or default. you must use 1 insert into statement, something like:
<?php
$con = mysql_connect("*","*","*");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("*", $con);
@$sql="INSERT INTO scores (hometeam,awayteam,result) VALUES ('{$_POST[hometeam]}','{$_POST[awayteam]}','{$_POST[result]}')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con);
?>
here, unspecified values will come as empty string, if that is a problem, first assign them to 3 seperate variables with ifs (e.g. set empty ones to null), then use them