Mysql PHP and foreign keys

geekyEd · 18 Mar 2009 at 14:31

Im writing a database for departmental equipment loans for my university as my final year project. Im an electronic and electrical engineering student with a larger interest in software than hardware as \Ive discovered over the few years of my degree.

For my final year project as said earlier i need a php interface with mysql back end.

I have written the basic database however now Im coming to writing the php to enter information into the database I have come unstuck on one thing. How do I enter into a field that is a foreign key referenced from another table mysql throws a wobbly when doing anything I have tried. So far Im kind of working around it but how do I go about using this properly.

Thanks in advance Edd

PMWD · 18 Mar 2009 at 14:33

Give us an example

geekyEd · 18 Mar 2009 at 14:50

its a bit messy and I dont want to get slated lol. Im using a basic form to collect data then passing it to mysql after serious ammount's of ereg's for error checking. I do Intend to use a selection list taken from the parent table of the foreign key, But thought ill hash it up quickly to enter some data using a form as it would only be sending the same information to the query in the end. however this doesnt work. any special way to input data to foreign key fields?

PMWD · 18 Mar 2009 at 15:19

Not really, assuming you're auto incrementing the primary key for each of the tables, you just need an additional int(10) column in the child table to store the parent id on the basis it's a one to many relationship.

Augmented · 18 Mar 2009 at 20:17

Are you using InnoDB for your DB, or MyISAM? Only InnoDB supports foreign keys properly i.e. with constraints and schema definitions.

geekyEd · 19 Mar 2009 at 11:41

yup innoDB is being used, a further update today, to prove that this is the error causing my form to crash i tried manually inserting the data via mysql cmd prompt. this is the error i get back.

geekyEd · 19 Mar 2009 at 19:15

cmon anyone got any ideas. If im getting this completely wrong please say.

philjohn · 19 Mar 2009 at 20:07

Well, that tells you that the insert breaks referential integrity - if you need to insert a row before you know the proper FK to use you'll need to set that field to be NULL rather than NOT NULL.

If would probably help if you could post a couple of the tables for us to look at, as well as the query you're trying (and failing) with.

geekyEd · 19 Mar 2009 at 22:31

might have found it whilst taking a screeny for you, both are varchars but of varying maximum length will test and get back to you. thanks a million for your time

edit: yup that was it! what a muppet!!!