CODEWITHSUNDEEP
phpmysql
dbj44
dbj44

Reputation: 1998

PHP not inserting some values into MySQL database

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.

Upvotes: 1

Views: 2656

Answers (6)

user1149244
user1149244

Reputation: 755

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

Upvotes: 1

Bob Davies
Bob Davies

Reputation: 2282

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).

Upvotes: 0

daghan
daghan

Reputation: 1028

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

Upvotes: 1

Darren Craig
Darren Craig

Reputation: 462

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!

Upvotes: 2

Jasminder Pal Singh
Jasminder Pal Singh

Reputation: 512

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.

Upvotes: 2

Celos
Celos

Reputation: 517

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

Upvotes: 1

Related Questions