Issue inserting empty values into indexed db

I'm hoping someone will be able to help me as i'm pretty stuck.

I'm trying insert some form data into my MySQL db. When I set indexes the db will not populate saying something along the lines of cannot update db a child row a foreign key constraint fails, however, if I remove the foreign keys it works fine.

I'm trying to link 2 indexed fields which just grab the auto incremented id from another table. I think that the error is caused because the indexed fields have no values. Please can someone help??? :)

==Update==

Hi,Thanks for the replies.

I removed the foreign keys before I posted the question.

I'll try and explain a little better. When I add foreign keys to the database in MySQL I CAN change the primary key field and the corresponding indexed field will automatically change giving me the same value which is exactly what i want.

The issue is when I try to insert the values using my php code i get the cannot update child error. I have not selected a value for the unique_id field as I want it to inherit the value from the pri开发者_C百科mary key. Here's my code as simplified as I could do it.

                                                               <?php  //this calls the class productupload and instantiates the function insert_form to insert values into the db.
$product = new productUpload();
$product->insert_form();?> //This calls the insert_form function and inserts info into tables
                                                                                   <?php
class productUpload extends DatabaseObject{

protected static $table_name="itm_details";
protected static $db_fields =array('id','unique_id','itm_cat','itm_make','itm_model','itm_desc','itm_cond','itm_date_from','itm_date_to','itm_add_date');      


        public $id;
        public $unique_id;
        public $itm_cat;
        public $itm_make;
        public $itm_model;
        public $itm_desc;
        public $itm_cond;
        public $itm_date_from;
        public $itm_date_to;   
        public $itm_add_date;

Heres the function insert_form which grabs the form submitted data. Note I have not defined a value for $unique_id

public function insert_form(){  
global $database;  
 //set the object attributes to the form the parameters
        if(isset($_POST['submit'])) {
        $result = array(
        $this->itm_cat =(!empty($_POST['itm_cat'])) ? trim($_POST['itm_cat']) : NULL,
        $this->itm_make =(!empty($_POST['itm_make'])) ? trim($_POST['itm_make']) : NULL,       
        $this->itm_model = (!empty($_POST['itm_model'])) ? trim($_POST['itm_model']) : NULL,                                   
        $this->itm_desc =(!empty($_POST['itm_desc'])) ? trim($_POST['itm_desc']) : NULL,
        $this->itm_cond =(!empty($_POST['itm_cond'])) ? trim($_POST['itm_cond']) : NULL,
        $this->itm_date_from =(!empty($_POST['itm_date_from'])) ? trim($_POST['itm_date_from']) : NULL,
        $this->itm_date_to =(!empty($_POST['itm_date_to'])) ? trim($_POST['itm_date_to']) : NULL,
        $this->itm_add_date = date("Y-m-d H:m:s"));
//check date is numeric
        //if(is_numeric($_POST['itm_date_from'])) {
        //$this->itm_date_from = $_POST['itm_date_from'];
//}    
//check date is numeric                        
        //if(is_numeric($_POST['itm_date_to'])) {
        //$this->itm_date_to = $_POST['itm_date_to'];
        //}
        if($result){
        $result = $this->create();
        }
        }
        }

//

here's the create function referred to above which sanitises the posted data and inserts it into the db.

public function create() {
        global $database;
        $attributes = $this->sanitised_attributes();

        $sql = "INSERT INTO ".self::$table_name."(";
        $sql .= join(", ", array_keys($attributes));
        $sql .= ") VALUES ('";
        $sql .= join("', '", array_values($attributes));
        $sql .= "')";
        if($database->query($sql)) {
        $this->id = $database->insert_id();
        return true;
        } else {       
        return false;
        }

}
?>

I am assuming that the key constraint is something like this.

ALTER TABLE itm_pic_detail ADD FOREIGN KEY (`unique_id`) REFERENCES itm_pic_detail(`itm_pic_id`) ON DELETE CASCADE ON UPDATE RESTRICT;

If I understand correctly you cant add the constraint itself, which indicates you already have conflicting records that violate this new rule. Run the following query to identify them and correct them first.

SELECT id FROM itm_details WHERE unique_id NOT IN (SELECT itm_pic_id FROM itm_pic_detail);

Did you check that the user your script is using has access to update both tables?

Also try to echo out $sql and check it can be run by hand, you may have a simple yet hard to spot error.

Also, you can optimize your script by dropping all those messy trim's and doing this instead...

function ParseInt($value) {
  $value = (int)$value;
  return ($value <= 0) ? 'NULL' : $value;
}

$this->itm_cat = ParseInt($_POST['itm_cat']);

Provided you never plan to have IDs <= 0, auto increment fields always start at 1.

You can also clean up your $db_fields and automate it..

/* get the columns from the table */
function __construct($db, $table) {
  $this->db       = $db;
  $this->table    = $table;
  $this->dbfields = array();

  $rs = mysqli_query($db, 'SHOW COLUMNS FROM `' . $this->table . '`');
  while($row = mysqli_fetch_assoc($rs))
    $this->dbfields[] = $row['Field'];
}

Put that in your parent class, then you can go one step further and also add this to your parent class, so you get sanity checking on column names.

function __set($member, $value) {
  if (!in_array($member, $this->dbfields))
    throw new Exception('The table ' . $this->table . ' does not have a column named ' . $member);
  $this->$member = $value;
}

function __get($member) {
  if (!in_array($member, $this->dbfields))
    throw new Exception('The table ' . $this->table . ' does not have a column named ' . $member);
  return $this->$member;
}