sql command INSERT INTO not working

Question

I'm creating a website and I'm running into an error with inserting data from a form into my database (using phpMyAdmin). It is simply coming up with the self defined error 'Error', 'Sorry, your registration failed. Please go back and try again.'

I've looked at many other questions with the same issue but I cannot find an answer which corresponds to my problem.

<?php include "base.php"; ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">  

<div id="main">
 <?php

if(!empty($_SESSION['LoggedIn']) && !empty($_SESSION['Email']))
{
    $PlanSize = mysql_real_escape_string($_POST['PlanSize']);
    $DatePaid = mysql_real_escape_string($_POST['DatePaid']);
    $AmountPaid = mysql_real_escape_string($_POST['AmountPaid']);
    $MonthUsage = mysql_real_escape_string($_POST['MonthUsage']);
     
      // $currentUser = mysql_query('SELECT * FROM User WHERE Email = "'. mysql_real_escape_string($_SESSION['Email']) . '"') or trigger_error(mysql_error());
     
      $currentUser = ($_SESSION['Email']);
        $registerquery = mysql_query("INSERT INTO Plan (PlanSize, DatePaid, AmountPaid, MonthUsage, PlanUserID) VALUES('".$PlanSize."','".$DatePaid."','".$AmountPaid."', '".$MonthUsage."','".$currentUser."')");
        if($registerquery)
        {
            echo "<h1>Success</h1>";
            echo "<p>Your account was successfully created. Please <a href=\"index.php\">click here to login</a>.</p>";
        }
        else
        {
            echo "<h1>Error</h1>";
            echo "<p>Sorry, your registration failed. Please go back and try again.</p>";    
        }       
     }

    ?>
    <form method="post" action="Plans.php">
 <table class="bill">
 <input type="hidden" name="data" value="true" />
    <fieldset>
     <p>
     <select name="PlanSize" id="PlanSize" placeholder="Plan Size"/>
   <option value="Small">Small</option>
   <option value="Medium">Medium</option>
   <option value="Large">Large</option>
   <option value="Extra Large">Extra Large</option>
  </select><p>

     <input type="text" name="DatePaid" id="DatePaid" placeholder="Date Paid (YYYY-MM-DD)"/><br />
        <input type="text" name="AmountPaid" id="AmountPaid" placeholder="Amount Paid"/><br />
       <input type="text" name="MonthUsage" id="MonthUsage" placeholder="Data Used"/><br />
        <input type="submit" name="Submit" id="Submit" value="Submit" />
    </fieldset>
 </table>
    </form>
  
</center>

</div>
</body></html>

Note: my base.php document is used to connect to the database and is fully functional; I have implemented other database data insertions using the base.php so I know it is not the root of the problem.

Many thanks in advance for any help

Don't use [mysql_ functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) - Mainly becuase of SQL injection, though that will also probably help your error handling. You want to know what failed with that obselete code then see: [mysql_error](http://php.net/manual/en/function.mysql-error.php) — ficuscr, Aug 21 '15 at 19:57
@ficuscr is right, but if you stick with it (not recommended), append `or die (mysql_error());` to your `mysql_query()` to get more accurate error — Berriel, Aug 21 '15 at 20:01
Cannot add or update a child row: a foreign key constraint fails (`hb00294`.`bill`, CONSTRAINT `bill_ibfk_1` FOREIGN KEY (`PlanUserID`) REFERENCES `User` (`UserID`)) - Added or die! Thanks for the tip with the or die. Not sure how to approach this. — Hans, Aug 21 '15 at 20:06
@Hans If the foreign key is defined as `NOT NULL`, then you must provide it in your `INSERT` query. try making the changes in your db table like `ON DELETE RESTRICT ON UPDATE CASCADE`. — DirtyBit, Aug 21 '15 at 20:11
I have to provide it even though I specified it as AUTO_INCREMENT? Also how do I make the table like ON DELETE RESTRICT ON UPDATE CASCADE? Do I change to CREATE TABLE to (BillID INT NOT NULL ON DELETE RESTRICT ON UPDATE CASCADE UNIQUE AUTO_INCREMENT, ? — Hans, Aug 21 '15 at 20:19
@Hans goto your `phpmyadmin database > table (the one you have applied the raltion on) > structure > Indexes` and see what you have and also click on `Relation view` present at the bottom in `structure` — DirtyBit, Aug 21 '15 at 20:32
never output a fixed error message when there's trouble. they're useless. `if (!$registerquery) { die(mysql_error()); }` will TELL you what went wrong. — Marc B, Aug 21 '15 at 20:36
Relation View doesn't specify any relations apart from the internal relations, the database name. What should I look for in the Indexes section? — Hans, Aug 21 '15 at 20:39
@Hans look for the table you have applied a FK Constraint on in Relation View and then change it as mentioned above. — DirtyBit, Aug 21 '15 at 20:40
@HawasKaPujaari Result of adding the constraint: Cannot add or update a child row: a foreign key constraint fails (`hb00294`.`bill`, CONSTRAINT `bill_ibfk_1` FOREIGN KEY (`PlanUserID`) REFERENCES `User` (`UserID`) ON UPDATE CASCADE) — Hans, Aug 21 '15 at 20:51
I think I might take this in another direction if there's I can't find a solution that I'm able to implement by myself. I really appreciate all the help, though @HawasKaPujaari — Hans, Aug 21 '15 at 20:55

score 1 · Accepted Answer · edited May 23 '17 at 12:29

Give this a go:

$registerquery = mysql_query("INSERT INTO `Plan` (`PlanSize`, `DatePaid`, `AmountPaid`, `MonthUsage`, `PlanUserID`) 
 VALUES('$PlanSize', '$DatePaid', '$AmountPaid', '$MonthUsage','$currentUser')");

Moreover, Are you sure you're inserting the correct values to their respective columns? Is $currentUser really suppose to go inside PlanUserID column?

After the discussion in the comments:

Since you have the FK Constraint enable and it should work but let's check a few things:

The Parent and Child table columns should have same data types.
Make sure you don't have any values in your child table's column that do not exist in your parent table's column.
Both tables should have identical collation.
Both of your tables should be InnoDB.

You can do so by:

SHOW TABLE STATUS WHERE Name =  'table1';
ALTER TABLE table1 ENGINE=InnoDB;

And if all fails you can always take backup of your parent and child table first, then truncate child table and try to make a relation again.

You can always study more at the similar questions available here: MySQL - Cannot add or update a child row: a foreign key constraint fails

score 1 · Answer 2 · answered Aug 21 '15 at 20:23

1

Looks like you haven't made check for existance of 'PlanSize' and others in $_POST array. Consider that you can just reload page for authenticated user without sending data via post method. So you get into your first condition, but indexes such as 'PlanSize' and other won't be created in POST array. Make check for isset beforehand:

$planSize = isset($_POST['PlanSize'])? $_POST['PlanSize'] : null;

answered Aug 21 '15 at 20:23

RomanPerekhrest

88,541
4
65
105

I've added this just before $PlanSize = mysql_real_escape_string($_POST['PlanSize']); but still receiving the same errors unfortunately. – Hans Aug 21 '15 at 20:31

score 0 · Answer 3 · edited Aug 21 '15 at 21:31

0

try it like this

$registerquery = mysql_query("INSERT INTO `Plan` (`PlanSize`, `DatePaid`, `AmountPaid`, `MonthUsage`, `PlanUserID`) VALUES('$PlanSize','$DatePaid','$AmountPaid', '$MonthUsage','$currentUser')");

edited Aug 21 '15 at 21:31

SuperDJ

7,488
11
40
74

answered Aug 21 '15 at 19:56

volkinc

2,143
1
15
19

Now receiving the following error: Notice: Undefined index: PlanSize in /Applications/XAMPP/xamppfiles/htdocs/plans.php on line 102 Notice: Undefined index: DatePaid in /Applications/XAMPP/xamppfiles/htdocs/plans.php on line 103 Notice: Undefined index: AmountPaid in /Applications/XAMPP/xamppfiles/htdocs/plans.php on line 104 Notice: Undefined index: MonthUsage in /Applications/XAMPP/xamppfiles/htdocs/plans.php on line 105 aswell as the other two errors. – Hans Aug 21 '15 at 20:00

sql command INSERT INTO not working

3 Answers3