How to insert a record onto a mysql table

[xx_princess_xx]

I am trying to insert details from my form to my database and i keep getting an error saying cannot insert or update a child record,

 

<?php 
require("Config.php");
class properties
{

private $connection;
function __construct()

{
//Empty constructor
}

function insert_properties($Type,$Price,$Address,$Postcode,$Photo,$IsAvailable ,$Agent_ID)
{
//$esc_Properties_ID  = mysql_real_escape_string($Properties_ID , $this->connection);
$esc_Type = mysql_real_escape_string($Type, $this->connection);
$esc_Price = mysql_real_escape_string($Price, $this->connection);
$esc_Address = mysql_real_escape_string($Address, $this->connection);
$esc_Postcode = mysql_real_escape_string($Postcode, $this->connection);
$esc_Photo = mysql_real_escape_string($Photo, $this->connection);
$esc_IsAvailable = mysql_real_escape_string($IsAvailable, $this->connection);
$esc_Agent_ID = mysql_real_escape_string($Agent_ID, $this->connection);

$sql="insert into properties('',Type,Price,Address,Postcode,Photo,IsAvailable,Agent_ID)

values ('','{$esc_Type}','{$esc_Price}','{$esc_Address}','
{$esc_Postcode}','{$esc_Photo}','{$esc_IsAvailable}','{Agent_ID}')";

$result=mysql_query($sql, $this->connection);
if(!$result)
{
die("SQL Insertion error: " . mysql_error());
}
else
{
$numofrows = mysql_affected_rows($this->connection);
return $numofrows;
}
}

function openDB()
{
$this->connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$this->connection)
{
die("SQL Connection error: " . mysql_error());
}
$db_selected = mysql_select_db(DB_NAME, $this->connection);
if(!$db_selected)
{
die("SQL Selection error: " . mysql_error());
}
}

function closeDB()
{
mysql_close($this->connection);
}


function getResult($sql)
{
$result = mysql_query($sql, $this->connection);
if($result)
{
return $result;
}
else
{
die("SQL Retrieve Error: " . mysql_error());
}
}

} // End of Class agent class
?>

<?php
if (!$_POST) //page loads for the first time
{
?>
<form action="
<?php echo $_SERVER['PHP_SELF'] ?>" method="post">



<br /> Type:<input type="text" name="Type" /><br />
  <br />Price:<input type="text" name="Price" /><br />
  <br />Address:<input type="text" name="Address" /><br />
  <br />Postcode:<input type="text" name="Postcode" /><br />
  <br />Photo:<input type="file" name="Photo" /><br />
  <br />Is Available:<input type="text" name="IsAvailable" /><br />
  <br />AgentID:<input type="text" name="Agent_ID" /><br />

<br /> <input type="submit" value="Save" />
</form>
<?php
}
else
{

$Type = $_POST['Type'];
$Price = $_POST['Price'];
$Address = $_POST['Address'];
$Postcode = $_POST['Postcode'];
$Photo = $_POST['Photo']; 
$IsAvailable = $_POST['IsAvailable'];
$Agent_ID = $_POST['Agent_ID'];


$db1 = new properties();
$db1->openDB();
$numofrows = $db1->insert_properties($Type,$Price,$Address,$Postcode,Photo,IsAvailable,Agent_ID);
echo "jhhjk";
$db1->closeDB();
}
?>

[Ken2k7]

<?php
$sql="insert into properties('',Type,Price,Address,Postcode,Photo,IsAvailable,Agent_ID)

values ('','{$esc_Type}','{$esc_Price}','{$esc_Address}','
{$esc_Postcode}','{$esc_Photo}','{$esc_IsAvailable}','{Agent_ID}')";

 

The above code, what is '' in properties('', Type... ? Is there a column name for it? If not, just leave it out.

[xx_princess_xx]

it is supposed to be for the property id,

 

 

[Ken2k7]

If it's an AUTO_INCREMENT, just leave the column out of the insert.

 

<?php
$sql="insert into properties(Type,Price,Address,Postcode,Photo,IsAvailable,Agent_ID)

values ('{$esc_Type}','{$esc_Price}','{$esc_Address}','
{$esc_Postcode}','{$esc_Photo}','{$esc_IsAvailable}','{Agent_ID}')";

[xx_princess_xx]

it still doesn't work

[Ken2k7]

Any errors? If so, what's the message? Copy and paste it here.

[xx_princess_xx]

my error is

 

 

SQL Insertion error: Cannot add or update a child row: a foreign key constraint fails (`comreg098jayathas`.`properties`, CONSTRAINT `properties_ibfk_1` FOREIGN KEY (`Agent_ID`) REFERENCES `agents` (`Agent_ID`) ON DELETE CASCADE ON UPDATE CASCADE)

 

[Ken2k7]

Oh oops, try this -

<?php
$sql="insert into properties(Type,Price,Address,Postcode,Photo,IsAvailable,Agent_ID)

values ('{$esc_Type}','{$esc_Price}','{$esc_Address}','
{$esc_Postcode}','{$esc_Photo}','{$esc_IsAvailable}','{$esc_Agent_ID}')";

 

You got the Agent_ID variable wrong.

[xx_princess_xx]

it still doesn't work

[xx_princess_xx]

this is the sql code i used to create the table

 

CREATE TABLE IF NOT EXISTS `properties` (

  `Properties_ID` int(4) NOT NULL AUTO_INCREMENT,

  `Type` varchar(20) NOT NULL,

  `Price` float NOT NULL,

  `Address` varchar(40) NOT NULL,

  `Postcode` varchar(7) NOT NULL,

  `Photo` varchar(20) NOT NULL,

  `IsAvailable` varchar(4) NOT NULL,

  `Agent_ID` int(4) NOT NULL,

  PRIMARY KEY (`Properties_ID`),

  UNIQUE KEY `Agent_ID` (`Agent_ID`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

[anupamsaha]

From the error message, it seems that the SQL failure is due to foreign key constraints. Can you please paste the SQL for creating the "agents" table here?

 

Thanks

[xx_princess_xx]

CREATE TABLE IF NOT EXISTS `agents` (
  `Agent_ID` int(4) NOT NULL AUTO_INCREMENT,
  `Agent_Name` varchar(20) DEFAULT NULL,
  `Address` varchar(40) DEFAULT NULL,
  `Postcode` varchar(7) DEFAULT NULL,
  PRIMARY KEY (`Agent_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

[kel]

You may also want to check your data. I had a similar problem before where I was loading data in via a text file and the only reason I had the error was that one of the entries was of the wrong data type.

 

Try inserting the data into your database directly (e.g. without php) does this work? Also, you may find that certain of your tables need to hold values before you can insert data into related tables - hence the foreign key constraint.

 

Play around with your data and your database (I assume mysql or other) and see if that is where the issue lies. It looks to me at least like there is an issue loading your data into the database rather than the issue being php related.

[xx_princess_xx]

ok thanks, i''l hav a go

[kel]

Ok, trust me - it drove me mad for days until I found that it was related to data type.

 

They can often be really small things, but if you don't make sure everything is right it can cause you headaches 

[xx_princess_xx]

i've have tried entering the right data but still no luck, any ideas

[anupamsaha]

i've have tried entering the right data but still no luck, any ideas

 

Please remove the UNIQUE KEY (Agent_Id) from the "properties" table and try.

 

 

[kel]

It may be worth posting the schema for your tables so that we can see how your tables are set up. That way maybe we'll be able to find what constraints you have on which tables and perhaps find the problem

[xx_princess_xx]

CREATE TABLE Properties (

  Properties_ID int(4) NOT NULL auto_increment,

  Type varchar(20) NOT NULL default 'unknown',

  Price int( NOT NULL,

  Address varchar(40) NOT NULL default 'unknown',

  Postcode varchar(7) NOT NULL default 'unknown',

  Photo varchar(20) NOT NULL default '',

  IsAvailable varchar(4) NOT NULL,

  Agent_ID int(4) NOT NULL,

  PRIMARY KEY (Properties_ID),

  FOREIGN KEY (Agent_ID) REFERENCES Agents(Agents_ID)

  ON UPDATE CASCADE ON DELETE CASCADE

) ENGINE=INNODB AUTO_INCREMENT=1;

[anupamsaha]

CREATE TABLE Properties (

  Properties_ID int(4) NOT NULL auto_increment,

  Type varchar(20) NOT NULL default 'unknown',

  Price int( NOT NULL,

  Address varchar(40) NOT NULL default 'unknown',

  Postcode varchar(7) NOT NULL default 'unknown',

  Photo varchar(20) NOT NULL default '',

  IsAvailable varchar(4) NOT NULL,

  Agent_ID int(4) NOT NULL,

  PRIMARY KEY (Properties_ID),

  FOREIGN KEY (Agent_ID) REFERENCES Agents(Agents_ID)

  ON UPDATE CASCADE ON DELETE CASCADE

) ENGINE=INNODB AUTO_INCREMENT=1;

 

There you are!!! Per DML of "properties", while inserting value in properties.Agent_ID, the same Agent_Id  MUST EXISTS  in agents.Agent_Id. Did you check that?

 

[xx_princess_xx]

yh

 

CREATE TABLE Agents (
  Agent_ID int(4) NOT NULL
  Address varchar(40) NOT NULL default 'unknown',
  Postcode varchar(7) NOT NULL default 'unknown',
  PRIMARY KEY (Agent_ID)
) ENGINE=INNODB AUTO_INCREMENT=1;

[anupamsaha]

yh

 

CREATE TABLE Agents (
  Agent_ID int(4) NOT NULL
  Address varchar(40) NOT NULL default 'unknown',
  Postcode varchar(7) NOT NULL default 'unknown',
  PRIMARY KEY (Agent_ID)
) ENGINE=INNODB AUTO_INCREMENT=1;

 

May be, I was not clear in my previous post. I meant the following:

 

If the insert query is trying to insert 3 as a value in Agent_Id field in "properties" table, then there must exists a value 3 in the Agent_Id field in the "agents" table. Otherwise, the error will occur.

 

Am I clear?

 

[xx_princess_xx]

ok, yh i made sure, sory of i didnt understand u b4,

 

i'm still getting errors i think i'm gona give up soon,

[kel]

Keep at it, you'll probably find that the error is caused by something like anupamsaha is saying.

 

When you have tables associated with each other via foreign keys, you will find that one of the tables usually has to be populated first before you can enter data into the other table because the tables require the data to be there.

 

you could try removing all of the data from those tables - hopefully you don't have a lot in them (or can reinsert the data back in easily) and then try putting the data into one table and then the other. If unsuccessful try inserting data into the 2nd one first and then the other table - this may show you how one table relies on the other.