MySQL insert query

I

iDenTiTy

Expert Member
Joined
Apr 14, 2007
Messages
3,899
Reaction score
2
Location
Fourways (vicinity)
Hi guys,

Ok this is a n00b question:
:o
I have 2 tables:

a) Table_Seller
sellerID(PK)
sellerName

b) Table_Property
propertyID(PK)
property_address
sellerID(FK)

The problem I have is; When ever a seller registers, their details (name) is inserted into the system. But on the same page, their property_address (which was entered) needs to be inserted into the Table_property table.
How would the insert statement look like?
Considering that the FK is set to cascade on update/delete...?

I'm really stumped.

Any help would be greatly appreciated.

:(
 
You need 2 inserts. First for the sellers then for the properties.
 
I think you will have to use multiple queries:
1) insert new seller into Table_Seller
2) Get the ID from the new seller added
3) insert property address into Table_Property

Somebody might have a nicer, and cleaner way of doing it. With an update command you can update multiple tables in one go. With insert, I'm not sure.
 
So like:

Insert into [Sellers] values (1, "Dude");
Insert into [Property] values(1, "Someplace") where sellerID = propertyID;

?

So what I'm trying to do is inserting into seller and property where the propertyID matches the sellerID...

:confused:
 
iDenTiTy said:
So what I'm trying to do is inserting into seller and property where the propertyID matches the sellerID...

:confused:
Click to expand...

I think more like:
Code: 
insert into [Sellers] values (1, "Dude");
insert into [Property] values (1, "Someplace", 1);
insert into [Property] values (2, "Someplace else", 1);

So in the above, "Dude" have two properties.
Ideally the Id's (sellerID and propertyID) will be auto-incrementing. Your queries will net change to:
Code: 
insert into [Sellers] (sellerName) values ("Dude");
select sellerID from [Sellers] where sellerName="Dude"; // save ID for next queries.
insert into [Property] (property_address, sellerID) values ("Someplace", $sellerID);
insert into [Property] (property_address, sellerID) values ("Someplace else", $sellerID);

I think something like this will work.
 
Raithlin said:
A more reliable way of retrieving the seller's ID would be to use the LAST_INSERT_ID() function. http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

So to useNod's example...
Code: 
insert into [Sellers] (sellerName) values ("Dude");
SELECT LAST_INSERT_ID() --Save this scalar value into $sellerID
insert into [Property] (property_address, sellerID) values ("Someplace", $sellerID);
Click to expand...

So can I combine these statements in one batch?
i.e. "GO" ?

If so, awesome. Thank you guys.
Will try it out.

:)
 
iDenTiTy said:
So can I combine these statements in one batch?
i.e. "GO" ?

If so, awesome. Thank you guys.
Will try it out.

:)
Click to expand...
If you put it in a stored proc, yes. Otherwise give it a try and see what happens. ;) You may end up running the first 2, storing the result, then using that variable server-side to run the remaining statements.
 
Going on what was previously posted you can change the query so it is entirely executed in SQL.

Code: 
INSERT INTO Sellers (sellerName) VALUES ("Dude");
INSERT INTRO Property (property_address, sellerID) values ("Someplace", (SELECT LAST_INSERT_ID()));
 
I'm getting panickey now.
I am totally confused as to what is going wrong...

I'm trying the following code:

Insert INTO seller (sellerID, sellerName, sellerSurname, sellerNumber, sellerEmail) Values (null, 'Bob', 'Some', '0854541414', '[email protected]');

INSERT INTO property (propertyID, address, price, prop_Bathrooms, prop_Bedrooms, parking, suburbID, sellerID) Values (null, 'Someplace', 'R 950,000', 1, 1, 'yes', 1, (SELECT LAST_INSERT_ID()));

My problem is the following error:

Cannot add or update a child row: a foreign key constraint fails (`realty/property`, CONSTRAINT `FK_property_1` FOREIGN KEY (`sellerID`) REFERENCES `seller` (`sellerID`) ON DELETE CASCADE ON UPDATE CASCADE)

As you can guess, Seller is the parent table.

Please anyone?

All the searches online resulted in a huge mess around in my brain..

:(
 
I'm not an expert on foreign keys, but it looks to me like this is what's going wrong. In the first query, you give "SellerID" a value "null" but in the second query, you give "SellerID" the result of SELECT LAST_INSERT_ID(). Now watch what happens:

mysql> CREATE TABLE `seller` (
-> `sellerID` int default NULL,
-> `sellerName` varchar(20) default '',
-> `sellerSurname` varchar(20) default '',
-> `sellerNumber` char(10) default '',
-> `sellerEmail` varchar(40) default ''
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO seller (sellerID, sellerName, sellerSurname, sellerNumber, sellerEmail) VALUES (null, 'Bob', 'Some', '0854541414', '[email protected]');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

BUT:

mysql> select sellerID from seller;
+----------+
| sellerID |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)

So the sellerID don't match, which violates the constraint.

Why are you using NULL for the ID, by the way? Doesn't that defeat the purpose?
 
The sellerID and propertyID should be auto-incrementing. You also don't insert anything into it when you insert. Simply leave out that field completely.
 
does mysql support a transaction ?

...would be wise if you split the insert to have it done in a transaction - what if the insert gets interrupted for some reason and stops midway ?
 
Necuno said:
does mysql support a transaction ?

...would be wise if you split the insert to have it done in a transaction - what if the insert gets interrupted for some reason and stops midway ?
Click to expand...

MySQL does if you're using the InnoDB storage engine for the table you're writing to. When you use a transaction, the inserts you do in that transaction, are commited in one go. The transaction can be rolled back too.
 
Ok, all I did was split the queries...

Too lazy to create a procedure etc....

Oh, ja - I;m using the InnoDB engine....


Thanks guys, for all your help.

:)
 
koffiejunkie, cool - just curious as i don't know mySQL so good.
 
Why dont you just run an insert and then an update statement and leave out the FK
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X