Insert Data From Another Table With Foreign Key?

Mar 20, 2011

I have a problem inserting a data into another table that has foreign key using PHP. I have a table named CUSTOMER_INFORMATION with fields(customer_no(PK), first_name, last_name, etc..) and a CATERING_RESERVATION table with fields(catering_no(PK), type_of_event, number_of_persons, customer_no(FK), etc).. I wanted to insert the customer_no in the CATERING_RESERVATION table but I have this error:

"Cannot add or update a child row: a foreign key constraint fails (`thesis/catering_reservation`, CONSTRAINT `catering_reservation_ibfk_1` FOREIGN KEY (`customer_no`) REFERENCES `customer_information` (`customer_no`) ON DELETE CASCADE)"

But I think I lack some codes on this. I'm just a newbie in php.[code]...



Find All Of The Rows In The Primary Table That Doesn't Exist In The Foreign Table

Aug 19, 2005

i have 2 databases, one uses the primary key of another as the foreign key. I want to find all of the rows in the primary table that doesn't exist in the foreign table. e.g:

table jobs:


table companies

from the example above i want to find all companies that have NOT had any jobs between one date and another.

View 5 Replies View Related

CakePHP - Custom HasOne Object Association - Using A Join Table Instead Of In-table Foreign Keys?

Sep 22, 2010

In CakePHP, I'm trying to build a $hasOne relationship, but I can't add a foreign key to the tables (for legacy reasons). Is there any way I can create a join table (object1_object2) and link them together like this? I want to leverage Cake's powerful object relationship functionality, so if there's a way I can make this custom association permanent throughout the model, without always building a custom $options array for each find(), that'd be great. (Maybe a bindModel in the Model's initalize()?)

View 2 Replies View Related

Use Multiple Foreign Keys In One Table Referencing Another Table In Cakephp?

Mar 17, 2009

Using cakephp, I have a generic address table, which I want to link to customers, vendors, contacts. most of the tables only have a 1 to 1 relationship, but I want my customers table to have 2

perhaps for clarification: I have a customers table

id, int
mailing_address_id, int
billing_address_id, int

and an addresses table

addr, varchar
city, varchar

Now I know I could put a customer_id in the addresses table. But I don't want to do that because I have a vendors table, and contacts table, and other tables that all are going to use the addresses table. the customer_id would not really be relavant to those other tables.

I'd like the Customer model to automatically link in the two addresses

View 4 Replies View Related

Put The Value Of One Field In The First INSERT Into The Second As A Foreign Key

Nov 25, 2004

I am developing what could be likened to a very simple php/mysql forum solution for a client. I have come to an instance where I need to do two INSERT queries on my MySQL database to put data into two tables. However i need to put the value of one field in the first INSERT into the second as a foreign key sorta thing.

The field i need the value for is an auto incrementing field. Is it possible to return the value of the auto incremented field after and INSERT query so that i can then use this value and insert it into the second table. I think I could probably do this by doing a SELECT query in between to find the max value of that column and then use that but I can't see this being the most efficient method.

View 1 Replies View Related

Using Session To Insert Foreign Key?

Aug 5, 2010

i am trying to use a session to insert in to the database a user_id of a user who is posting a topic in to the topic table to act as a foreign key in the topic table. but i kept getting 0 in the user_id column instead of the real userid below is my code.

<?php require_once('Connections/forum_con.php'); ?>
//initialize the session


View 2 Replies View Related

MySQL Insert With Undefined Foreign Key?

Feb 5, 2011

I have some table with such schema (simplified):

INDEX FK_folders_folders_id (parent),
CONSTRAINT FK_folders_folders_id FOREIGN KEY (parent)

1 folder can have many subfolders and can belongs to one another folder. If it's root folder then parent will contain it's own ID. The problem is: how can I create root folder? ID is auto_increment and I can get it only after inserting a row but I cant insert row while parent is not defined. Recursion...

View 1 Replies View Related

Insert Into Multiple Tables Linked By Foreign Keys (MySQL)?

Mar 6, 2011

I've managed to get (in my opinion) quite far surfing forums for any problem I encounter, but now I’ve hit a wall and can't find the solution anywhere.  As most of the previous answered I found were on this site, I hope you don't mind me asking my question!The problem:  I have two tables: customerdetail (with primary key custid) and customeraddress (with primary key addressid and foreign key custid). My insert statement was working perfectly inserting into both tables from one form.  As soon as I create the foreign key for customeraddress (in phpmyadmin) my insert statement only working for customerdetail (i.e the table without the foreign key), and nothing gets inserted into customeraddress.I suspect this is because I’m inserting into both tables using one php form and therefore customeraddress can't pull the foreign key from customerdetail as it’s not been created yet...but i have no clue how to solve this or if it really is the problem.QuotePHP Code:



View 9 Replies View Related

Inserting Id Into Another Table As A Foreign Id?

Mar 12, 2011

I want to allow users to post entries to a NEWS table and, if they wish, to post an accompanying image to an IMAGES table.Tables are like this:


View 14 Replies View Related

Figure Out The Foreign Key Constraints Of A MySQL Table Using Doctrine 1.1?

Oct 26, 2010

When two models are related to each other, Doctrine automatically generates foreign key constraints in the MySQL database beautifully. The default behaviour for onDelete and onUpdate RESTRICT, but can also be set to SET NULL or CASCADE.Now, I want to create a migration diff from an existing database compared to a YAML file. To do this, I use the Doctrine::generateModelsFromDB() function. I use an overloaded versoin of Doctrine_Migration_Diff to do the diff work for me, that takes care of non-matching class names, MySQL field lengths etc. It does all stuff nicely, but there is a problem with these foreign key constraints.When I create PHP classes from a MySQL database, it seems Doctrine doesn't set the 'onDelete' and 'onUpdate' to the appropriate values. When I then try to diff these PHP classes against classes that do have 'onDelete' and/or 'onUpdate' set, Doctrine tries to add a new foreign key that already exists, and MySQL fails.I worked around this error temporarily by manually ignoring all changed foreign keys in my overloaded Doctrine_Migration_Diff, but this really is no solution. What I wan: if Doctrine thinks a new Foreign Key needs to be added, chance is it already exists, and what its onDelete and onUpdate behaviour are set to. How can I check that, if I have the local field name and the foreign field name?P.S. If somebody is certain this was fixed in Doctrine 1.2 (which I doubt), please don't advise me to upgrade. I have specific reasons to work with 1.1 in this case, but that is a different story. I would rather be pointed into the direction of what code change provided this fix, that might be a step closer to a workaround.

View 2 Replies View Related

Wrong Sintax For Alter Table With Compound Foreign Key?

Aug 18, 2010

Who can repair this it really a wizard. I try to add a column to an existing table. It a compound foreign key column from ather two.

ALTER TABLE valoare_lucrari
ADD COLUMN id_oferta
FOREIGN KEY (id_ofertant, id_proiect)
REFERENCES valoare_lucrari(id_ofertant, id_proiect)

It may be a problem of sintax or even it all idea wrong. But I need to pot in 'id_oferta'toghether two foreign keys from 'id_ofertant' and 'id_proiect'

View 2 Replies View Related

MySQL INSERT Into New Table, SELECT From Old Table, UPDATE Old Table With New Key?

Feb 17, 2009

Okay, here goes!

INSERT INTO retailers
(retailername, retailerdesc, retailerwebsite, retailerurl, active)

UPDATE datasources_retailers_idx SET idretailers=last_insert_id();

Here's what I'm trying to do:

Select from old table

check if there is a matching retailer in the new table

If not, insert retailers details into new table

Update a reference column in the old table with the INSERTID primary key value of that row from the new table

The problem is that - as it should - the 'last_insert_id' is the VERY LAST row entered into the database. - So every row in the old table is updated with the LAST inserted ID.I am trying to avoid PHP completely and do it totally in mysql - I can do it in php, but it takes 9 hours to complete.

View 2 Replies View Related

Insert Query Doesn't Insert Data To Mysql Database Table?

Dec 10, 2010

i have a recipe table and ingredient table the primary key of both tables are auto increament and primary key of recipe is foreign key in ingredient. i post data from html to php.Note that my ingredient textboxes are generated dynamically and successfully post the data to php script. posted data is correct when i insert this data to table my query working fine but data is not added to mysql table. my code and output is

$sql = "insert into recipe (rec_id, Name, Overview,category, Time, Image) values ('', '$name','$overview','$category','$time','$TARGET_PATH')";
$result = mysql_query($sql) or die ("Could not insert data into DB: " . mysql_error());
$rec_id = mysql_insert_id();


View 5 Replies View Related

InnoDB Foreign Key / Users_profiles Table And Notice The Users.uid Is Not Inserted Automatically In The Users_profiles??

Apr 21, 2011

Below i have two tables, users and users_profiles. Both are innoDB and collation: utf8_general_ci

They are as follows:


CREATE TABLE `users` (
`status` char(10) NOT NULL,
`username` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`password` char(32) NOT NULL,
`reg_date` int(11) NOT NULL,

After creating the tables in phpmyadmin i created a foreign key on the users_profiles table, code below is what phpmyadmin created.

as follows:

ALTER TABLE `users_profiles`

basically the users_profiles.uid is a foreign key and references the users.uid

In phpmyadmin i go to insert and fill in some sample data leaving the uid obviously to auto increment. When i have inserted a record in users table i goes into the users_profiles table and notice the users.uid is not inserted automatically in the users_profiles,

Is this normal?

Reason is when someone for example registers on a form, they will be asked for username, email and password, and i do a query in php to insert that data into users table, i thought that because i have a foreign key that it would also automatically insert a row in the users_profiles table with the uid from users table so there is a link between the user and there profile. But when i insert a record into users table the users.uid is not inserted into the users_profiles table.

I tried another example to see what would happen and this one works as i would expect due to the cascade on update and delete.

If i insert a row in users table and then manually insert the users.uid into users_profiles.uid (they are now linked) and add for example my first_name and last_name then in phpmyadmin delete the user from users table it deletes the row in the users_profiles table. This works like it should obviously as i don't want a user to be deleted and have there profile remain.

This has confused me as when i do create a form and a user signs up, they essentially would
not have a profile because on signup no profile is created for them with there users.uid in the user_profiles table (no link between them) althou i have a foreign key.

Can some explain why it's not working as i expect, maybe it should be working like i want it to but something is wrong or i am missing the whole point i guess otherwise.

Sorry for going on to much as per usual, i am not great at explaining things in small detail. Please forgive me for that.


In reference to reply from @Mark Wilkins

I understand what you mean now. But something i am not 100% sure on is this:

user signs up, a record is created in users table; they login and visit profile page where the can fill it in and submit the form.

On processing the form am i rite in thinking i would need to do the following:

user filled in profile form and submitted (first time they submitted profile as they are a new user), after validating data etc i first check to see if the uid in the users table match a uid in the users_profile table, if there's a match then UPDATE record with new values (this would mean the user has previously filled in there profile as on signup they don't have one) but if no match is found on uid from both tables then i would perform an INSERT query because no profile yet exists for the user. I take it that obviously i would store the uid from users table in session with other data on successful login and the uid in session would be the uid that is inserted into the users_profiles table in column uid ? that way a link is created between two tables and if i now decide to delete the user there profile will also be deleted to.

View 2 Replies View Related

Insert Into Table - $query = "INSERT INTO Tbl

Oct 13, 2005

I have a small problem which I've been working on for a while. PHP Code:

$query = "INSERT INTO tbl(username,ip,link) VALUES("Brin' . $result['id'] . '","$REMOTE_ADDR",""' . $result['id'] . ' )";

Any ideas?

View 3 Replies View Related

Insert Values - Table Has Four Columns To Insert Into

Aug 25, 2007

I have a bunch of information that I want to insert into a table

insert into TABLE values ('hello','I','am','lost');

pretend that the words that are being inserted are coming from a form for another page. In he case I above I have declared that the table has four columns to insert into. What if I do not know how many columns there are?

View 2 Replies View Related

Insert A Record Into A Table Where The Table Fields Are Related To Ther Tables?

Sep 2, 2008

I am trying to insert a record into a table where the table fields are related to ther tables.
It is not necessary to have entries in each table for the record to insert so I'm trying to insert either values or NULL into the main insert's table.

PHP Code:
INSERT INTO `table-a` (`field1`,`field2`,`field3`,`field4`)
SELECT 'one',`b`.`tbl_id`,`c`.`table_id`,`d`.`table_id`
FROM `table-b` `b`
LEFT JOIN `table-c` `c` ON `c`.`title` = 'some title'
LEFT JOIN `table-d` `d` ON `d`.`title` = 'another tile'
WHERE `b`.`title` = 'yet another title'

I Had IF (`b`.`table_id` IS NULL, NULL, `b`.`tab;e_id`) before but that didnt resolve my problem, if not all conditions are met no rows are returned and therefore there are no NULL values. How can I do this so that if a table doesnt match I can still return a NULL for that specific field, all of table-a's fields can be NULL.

View 1 Replies View Related

Insert Into One Main Table And One Detail Table In One Go Without Double Records?

Oct 11, 2009

I have a table with orders and a table with order details. Clients can order multiple products at once during one order, so one line has to be inserted in the main order table, and multiple order details from the same order in the order detail table. Right now if a client orders 5 products during one order, the order detail table looks fine, but also get 5 records in my main order table. It has to be one.

(renatef_bestellingen is the main ordertable, renatef_besteldetail is the order detail table)

A part of my current code:
$sql = "INSERT INTO renatef_bestellingen (KLANT_ID, besteldatum, besteluur, leveringswijze, leveringskost) VALUES ('$klantid', '$datum', '$uur','$leveringswijze', '$leveringskost')";
$theresult = mysql_query($sql);  


in the 'renatef_besteldetail' picture everything is correct except for the 'bestelling_id' which both should be value 26 I also added a print screen of more of my code.

View 1 Replies View Related

How Can I Update/insert One Complete Table Into Mysql Table

Nov 12, 2006

How can I update/insert one complete table into mysql table. Input data will be from another page (a form html/php) and data will be update/insert into mysql table. table would be like this : total coloum-- 11 , total row -- 7. I want update in one go.. it may be using loop or otherwise.

View 9 Replies View Related

Insert Records Into Table From Text File As Well As From Another Table

Feb 24, 2010

i have two tables emp_company and at0310 in at0310 having fields: ecardno,atdate,time,IO,serialno,cardrd and ecode. and at0310 records comes from text file.. into my text file ecardno,atdate,time,IO,serialno,cardrd record available but ecode not comes from there. in my emp_compnay i have fields like ecode,ecardno,etc i want into my at0310 table ecode comes from emp_company and only those ecode should be stored into at0310 that matches with ecardno with text file.... can you tell me any query. according to me select and insert needed but i cant place them in working order

if (!$con)
die('Could not connect: ' . mysql_error());
//(1) Read the text file into a variable
$file = "ATND.txt";

View 2 Replies View Related

Insert MySQL Table Column Array Into New Table

Jun 6, 2011

I have a php form that gets dumped into a MySQL table. I create an array that is populated from a Product table using php. Queries the table to get the values for the array - works fine.

function getProducts ($id)
$sql =("SELECT * FROM products where products.CAT_ID = $id AND DISABLED ='NO'");
$result = mysql_query($sql);
if ($result > 0)

Then I use the above array to create labels in a javascript accordion. Next to the labels -are select boxes. (Removed unnecessary page elements.. ) Html/php form below that displays the above array.


View 1 Replies View Related

Select Value From Drop Down List Of One Table And Insert That Value Into Another Table?

Oct 6, 2010

My problem is that i have two table city and location.In city table i have 3 field city_id(Primary key),city_name,city_status where city_status is true or false.In location table i have 3 field also loc_id(Primary key),city_id,loc_city.i done a code in which city_name comes into drop down list from city problem starts here when i select one of this value and want to insert into location table...then this show there any code available related to my problem plz send my id...

View 9 Replies View Related

Delete All The Contents Of A Table In My Sql And Insert A New Thing Into That Table

Feb 15, 2009

i need a to delete all the contents of a table in my sql and insert a new thing into that table here is my try at it

PHP Code:
$mum="DELETE * FROM bignews ";
if (!mysql_query($mum))
die('Error: ' . mysql_error());

View 4 Replies View Related

Grab Data From A Table, Echo, Then Insert It Into A New Table...

Sep 18, 2007

I'm trying to do is count the number of times usernames shows up in a table. I then want to reinsert that information into a different table with the Username in column1 & the occurrences in column2 for every user that is echoed on the page. Code:

View 5 Replies View Related

Checkbox Form - Insert Row Data From One SQL Table To Another Table?

Nov 15, 2009

I have two tables "tbl_jobs" and "tbl_jobs_done". I have an SQL query which displays and formats my daily job list from tbl_jobs SQL database, and has a checkbox at the end of each row. I want to be able to select 1, 2, 3 or more checkboxs and then click my submit button which will then insert the data into my second MySQL database named "tbl_jobs_done". i have spent hours and hours looking at tutorials and other peoples forum posts but can't seem to find something that hits home and works for me I only need the jobs_id field data from the tbl_jobs to be inserted into tbl_jobs_done table and into jobs_id field. I have other data on the form that will also be submitted. But in general, that's all I need. Here is my SQL Query ->


View 4 Replies View Related

Update Table While Insert Data To Another Table

Feb 7, 2006

it seem like mine coding din work.

$conn=mysql_connect('localhost','root','') or die ('Could not connect to server');
$today = date("Y.m.d");
mysql_select_db('hms', $conn);

$ok = mysql_query("INSERT INTO check_in(ID,room_no,datein)values ('$ID','$room','$today')", $conn);

if( $ok ){

$up = mysql_query("UPDATE studtable SET checked = 'checkin' where [ID] = '%ID');

if(mysql_affected_rows( $up ) == 1)
echo'Checked IN!'
}else { echo'No such student to check in' }}
else { echo'Check In FAILED, please check again'}?>[php]

View 1 Replies View Related

Insert Many Rows To One Table OR Insert Rows Separately To Many Table?

Jun 21, 2011

Possible Duplicate: Insert many rows to one table OR insert rows separately to many table? Which is better 100 tables with 10,000 rows or 1,000 tables with 1,000 rows?The above question is just a base.Its not completed without the conditions below.


R1-No. of rows in first case.
R2-No. of rows in second case.
T1-No. of tables in first case.
T2-No. of tables in second case............

Query execution time and server load time(Minimum RAM usage). Feasibility of dynamically increase in no. of tables in database. Feasibility of dynamically increase in no. of rows in a table.

View 2 Replies View Related

Take Certain Fields From One Table And Insert Them Into Another Table?

Jan 19, 2009

i am trying to take certain fields from one table and insert them into another table using one query. I have tried using the following sql:

INSERT INTO invoices( ' ', company, firstname, lastname, email, address1, address2, town, city, county, country, postcode )
SELECT company, firstname, lastname, email, address1, address2, town, city, county, country, postcode
FROM `clients`
WHERE id =2

however i get the following error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' ',company,firstname,lastname,email,address1,address2,town,city,county,country,' at line 1

by the way i am using MySql version 5.0.67-community

View 4 Replies View Related

First In Table Insert

Oct 5, 2006

I have a very large form and its anoying to get each form name seperatly and insert them into the database, as you could imagine the sql insert statement will be huge. Is there a way i could 'foreach' the post variables and insert them into the database without using the names from the fields. I mean that could i for example insert into the first field in the table and then the second and so on?

foreach ($post as $key => $val){

INSERT INTO blabla SET (the first field) to $postvar


View 3 Replies View Related

Value Not Insert Into Table

May 31, 2005

Any ideas on why the value for $user is not getting inserted into my database table? Everything else makes it in OK, and I can echo the value on the form, so I know it's populated correctly. I think I'm just missing something small.

View 2 Replies View Related

Table Insert

Jan 19, 2005

i design a form that post some value to same php page, and i write some code in the page which is responsible for insert the values into database table, i use if submit = 'add' to execute that part of code. thats works nice but when i reload(using browser reload button)the page everytime it insert the same value to the database again and again. is there any good way to prevent this?

View 1 Replies View Related

Insert A Table

Oct 14, 2006

How to insert a table like this with a

<table width="100%" border="0" cellspacing="0" cellpadding="0">

i would like to put the text to the right to the website screenshot.

View 2 Replies View Related

Insert Into More Than One Table

Feb 9, 2006

What's the best way to write a SQL statement so it will insert data from a form into more than one table? For example, I'd like to be able to do something like: PHP Code:

$sql = "INSERT INTO attendees SET firstName='$firstName', lastName='$lastName', typeOfAttendeeID=&#391;', conventionID=&#391;'";

and simultaneously do this: PHP Code:

"INSERT INTO relationships SET role='father', typeOfAttendeeID=&#391;', conventionID=&#391;'";

View 2 Replies View Related

Insert At Specified Row Of Table

Jan 15, 2006

I've checked my reference books, and many sites including this one, and can't find a way to insert a row at the location I choose. I don't want it in alphabetical order, or anything, but I do want to control where the data can be placed.

View 5 Replies View Related

Insert Into TABLE

Jun 27, 2007

here is my code:

if ($_POST['submit']) {
mysql_pconnect('localhost', 'root', '');
$result = mysql_query("SELECT * FROM customer");
$col = mysql_num_fields($result);
for ($j=0;$j<$col;$j++) {
$info = array(mysql_field_name($result,$j));
mysql_query("INSERT INTO customer (".$info[&#390;'].") VALUES ('".$_POST[$j]."')");
echo "<meta http-equiv=REFRESH content=0;url=http://".$_SERVER['HTTP_HOST'].">";
} else {
echo "<form method=post action=".$PHP_SELF.">";

View 1 Replies View Related

Insert Row From One Table To Another?

Feb 16, 2010

here is the script that I have written so far:


//Include database connection details
//Connect to mysql server


It always dies on the very first insert. So I know that the issue is probably with the fact that I am trying to use values that I am reading which mySQL won't let me do due to synchronization issues? If this is true how do I get around this. Also I have found methods to transfer a row directly... but the table I am transferring into isn't exactly the same in structure it is missing one column.

View 2 Replies View Related

Insert Last Id Value From One Table To Another?

Aug 5, 2010

how to insert last id value from one table to another

View 2 Replies View Related

Insert 0 In A Db Table?

Feb 11, 2010

I had a mysql table with php pages related to it, When I insert 033333 , it will take 33333, which type should I put in order to preserve the leading 0?

View 10 Replies View Related

Insert Into Table Using For Loop

Jul 22, 2003

i managed to get bits a pieces and managed to get this script to nearly work. I put together this script which a)gets a no. of fields to create 2) creates those fields 3) on submission it checks for blank fields & may display error msg 4) also it checks in the db for duplicates & may display error msg 5) if all good it submits to db.

Here comes my dilemma. 1, 2, 3 and 4 work fine however the final part 5 doesnt work at all. 5 works if 3 and 4 are taken away for some reason. I am sure there i am missing something. Can someone please take on the challenge and direct me to where i could be going wrong. PHP Code:

View 7 Replies View Related

MS Access And Php- Insert Row In A Table

Feb 21, 2006

I am not able to insert a row through a form in my database which is in Access.Please see my code below and help..Select works O.K. but inserting through form is a problem.This is I am inserting the values.This form has action process.php......

View 1 Replies View Related

Multiple Table Insert

Oct 4, 2005

The $IDNumber variable falls in an auto_increment column. However, nothing is being inserted into the 'General' table, and 'Notes' is inserted into the 'IncNotes' table, but it is coupled with a blank value for the IDNumber. Code:

View 6 Replies View Related

AJAX - Insert Table Row

Feb 1, 2006

I basically want to fire off an AJAX request to the server (on a form POST) which will run a php function and return a New Table Row to the original requesting page in TABLE 1, Remove a Table row on the original page TABLE 2, without refreshing the entire page contents. Can anyone point me to some simple examples?

View 4 Replies View Related

Insert Into New Table And Delete From Old

Aug 29, 2005

I've got 2 tables of users: 'pending' and 'approved'.

The first table (pending) is a 'holding' table for unapproved users and when the user is approved I can click on a checkbox by their name and copy them over to the approved users table (INSERT INTO approved...)

This all works fine but I'd also like to delete them from the 'pending' table at the same time. How would I do this? Something like (INSERT INTO approved, DELETE FROM pending)?

I'd do a bit of trial and error but since it's on a live site, I don't want to muck it up and not be able to fix it! Code:

View 1 Replies View Related

Insert Row Into Middle Of Table

Dec 24, 2005

Is there a lovely easy way of swapping 2 mysql rows? Come to think of it, swapping the unique IDs instead of the entire row data may have the desired effect.

View 8 Replies View Related

Insert Array Into Table

Mar 8, 2006

how do I insert an array into a MYSQL table with just one insert statement??

View 5 Replies View Related