fishbaitfood Posted September 8, 2011 Share Posted September 8, 2011 Hey all, I've got two tables in phpmyadmin: customers: - id (primary) - ... checklist: - id (primary) - id2 (index foreign key to customers.id) - ... id2 can have duplicates, and is obviously the same type as customer.id When I want to add a row to customers, there should also be added a row to checklist, using the foreign key. When I try to do this, I get the error stated in the topic title. I'm now using two queries (only primary and foreign key to keep simple here): I thought MySQL would handle the foreign key automatically? $add_customer = "INSERT INTO customer VALUES ('$id');"; $add_checklist = "INSERT INTO checklist VALUES ('$id', '');"; Can someone help me? Also, is it possible to achieve this with only one query? Thanks already! Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 Hey all, I've got two tables in phpmyadmin: customers: - id (primary) - ... checklist: - id (primary) - id2 (index foreign key to customers.id) - ... id2 can have duplicates, and is obviously the same type and length as customer.id When I want to add a row to customers, there should also be added a row to checklist, using the foreign key. When I try to do this, I get the error stated in the topic title. I'm now using two queries (only primary and foreign key to keep simple here): I thought MySQL would handle the foreign key automatically? $add_customer = "INSERT INTO customer VALUES ('$id');"; $add_checklist = "INSERT INTO checklist VALUES ('$id', '');"; Can someone help me? Also, is it possible to achieve this with only one query? Thanks already! Woops, seems like modifying is really limited on these forums? Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 Okay, so at first, I needed to cast my id's to integers, since my table uses integers. The ONLY problem so far is with adding rows while auto-incrementing. This is my code (explanation inside): <?php $year = (string) date('Y'); $id = $year . '0001'; // will be like 20110001 (for when a new year starts) $project = $id . '1'; // will be like 201100011 $id = (int) $id; $project = (int) $project; $add_customer = "INSERT INTO customers VALUES ('$id');"; // for adding when new year starts or table empty -- WORKING -- $add_checklist1 = "INSERT INTO checklist VALUES ('$project', '$id');"; // for adding when new year starts or table empty -- WORKING -- $add_customer = "INSERT INTO customers VALUES ('');"; // for adding with autoincrement -- WORKING -- $add_checklist = "INSERT INTO checklist VALUES ('', '');"; // for adding with autoincrement -- DOES NOT WORK -- if(($num < 1) || ((int)date('Y') > (int)substr($row['toegevoegd'], 0, 4))){ // check if table empty OR current year is "bigger" than last record's year mysql_query($add_dossier1) or die ("Query mislukt: " . mysql_error()); mysql_query($add_checklist1) or die ("Query mislukt: " . mysql_error()); } else { // else autoincrement mysql_query($add_dossier) or die ("Query mislukt: " . mysql_error()); mysql_query($add_checklist) or die ("Query mislukt: " . mysql_error()); } ?> So basically, when I've added a customer (id = 20110001), it needs to add a project (id = 201100011) to another table. When the table is NOT empty, I want to use increment, for both id's. BUT, the project id MUST contain the customer id. My initial plan was to have more projects (201100011, 201100012, ...) for EACH customer. But I think that's not possible anymore, when using auto-increment, right? As of now, the only problem occurs when the code needs to execute the 4th query. The 3th query is still executed succesfully. How do I solve this, preferrably with the functionality of my initial plan? Thank you. Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 Hmmm... Now I'm thinking of the whole thing.. Now, I add a customer, to have a project added. But I want to have more projects for the same customer... I don't want any duplicates. So, the fix would be, to add a project, and then a customer, right? Damn... Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 UPDATE: I now first create a project, and then get the customer id value of that project (see code) So now I can add projects, instead of customers. So it would go like this: projects: - 201100011 - 20110001 - John Doe - 201100012 - 20110001 - John Doe - 201100021 - 20110002 - Foo Bar customers: - 20110001 - John Doe - 20110002 - Foo Bar So now the problem is still, when auto-incrementing, but also ignoring duplicate customers (when entering name, not id). Hope this is clear enough? The last updated code: <?php $year = (string) date('Y'); $project = $year . '00011'; // Will be like 201100011 $id = substr($project, 0, ; // Will be like 20110001 $project = (int) $project; $id = (int) $id; $add_customer1 = "INSERT INTO customers VALUES ('$id');"; // for adding when new year starts or table empty -- WORKING -- $add_checklist1 = "INSERT INTO checklist VALUES ('$project', '$id');"; // for adding when new year starts or table empty -- WORKING -- $add_customer = "INSERT INTO customers VALUES ('');"; // for adding with autoincrement -- WORKING -- $add_checklist = "INSERT INTO checklist VALUES ('', '');"; // for adding with autoincrement -- DOES NOT WORK -- if(($num < 1) || ((int)date('Y') > (int)substr($row['added'], 0, 4))){ // check if table empty OR current year is "bigger" than last record's year mysql_query($add_customer1) or die ("Query mislukt: " . mysql_error()); mysql_query($add_checklist1) or die ("Query mislukt: " . mysql_error()); } else { // else autoincrement mysql_query($add_customer) or die ("Query mislukt: " . mysql_error()); mysql_query($add_checklist) or die ("Query mislukt: " . mysql_error()); } ?> Error I get: Query failed: Cannot add or update a child row: a foreign key constraint fails (`cre8`.`checklist`, CONSTRAINT `checklist_ibfk_1` FOREIGN KEY (`id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) Quote Link to comment Share on other sites More sharing options...
fenway Posted September 8, 2011 Share Posted September 8, 2011 TLDR -- where are you at now? Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 Yeh, sorry for that. I started the topic a bit too early... My previous post is my last situation. When both tables are empty, it WORKS. Yay! Because I specify the $project (checklist) and $id (customers). This is only when the table is empty, or a new year has started (201100011, 201200011, ...) Customer id uses 8/9 digits of project. In my previous post, you can see how my table needs to work. BUT when I want to add another project, I want to use auto-increment, obviously. But then I get that Error about my foreign key failing. Also, when auto-incrementing this way, I cannot achieve the functionality of my tables (no duplicate customer id/names). Quote Link to comment Share on other sites More sharing options...
fenway Posted September 8, 2011 Share Posted September 8, 2011 Those constraints don't enforce UNIQUEness unless you ask for it. I'm not sure why you create an empty row first -- but if you do so, you'll need to NULL out those FKs. But again, it sounds like a mistake. Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 So after 1 record, I need to specify the uniqueness of $project and $id in my queries, even when it's auto-increment? Could you guide me a little with the code? What do you mean, empty row? There are other fields ofcourse, but I didn't include them here, to make it easier on you guys. When the first project is added, it uses the first two queries. From then on, the 3th and 4th, for auto-incrementing. Only the primary keys are blank for auto-incrementing. But like I said, this auto-incrementing is causing the foreign key failure. What exactly do you mean with NULL out those FK? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 8, 2011 Share Posted September 8, 2011 I don't understand why you're adding rows with no values. Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 8, 2011 Author Share Posted September 8, 2011 Those "no-values" are the project and id fields, and will be auto-incremented if they are empty. There ARE other values in those queries, but they aren't important to explain here... Well, now they are actually. Hehe. Sorry that I haven't made this clear in a better way. Hope you get my point now? But ofcourse, if you know a better way, please do tell me. Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 9, 2011 Author Share Posted September 9, 2011 Solved it myself! Now I got a headache, damn... I'd like to post my solution, but it's quite different now, and not easy to understand without my whole code. And the whole code uses weird variable names (dutch), so it's kinda hard to place, I think. If someone would really like to have the solution posted, I'll do so and rename my variables. Thanks anyway guys, for taking the time. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 9, 2011 Share Posted September 9, 2011 Well, if you can post the logic behind the solution, that will suffice. Quote Link to comment Share on other sites More sharing options...
fishbaitfood Posted September 9, 2011 Author Share Posted September 9, 2011 Explanation inside code, I did my best. I think there are some ways to make it more efficient, but this is the most logical to me for now. (Best viewed when copied to your code editor) <?php $check_customers = "SELECT * FROM customers;"; // This is for incrementing Customer-ID and Project-ID when Customer(name) is new $check_customer = "SELECT * FROM customers WHERE customer = '$customer';"; // To check if the entered Customer already exists $check_checklist = "SELECT * FROM checklist WHERE customer = '$customer';"; // This is for incrementing Project-id when Customer exists $check_last = "SELECT added FROM customers ORDER BY added DESC LIMIT 1;"; // To check if current year is "bigger" than latest Customer's added year $result_customers = mysql_query($check_customers) or die ("Query failed: " . mysql_error()); // See above $result_customer = mysql_query($check_customer) or die ("Query failed: " . mysql_error()); // See above $result_checklist = mysql_query($check_checklist) or die ("Query failed: " . mysql_error()); // See above $result_last = mysql_query($check_last) or die ("Query failed: " . mysql_error()); // See above $num_customers = mysql_numrows($result_customers); // See above $row_customer = mysql_fetch_array($result_customer); // See above $num_checklist = mysql_numrows($result_checklist); // See above $row_last = mysql_fetch_array($result_last); // See above $date = date('Y-m-d'); // Get current date $year = date('Y'); // Current year // Starting variables $project = (int) ((string)$year . '00011'); // Project-ID, consists of current year + 5 digits ,, last digit is for incrementing when Customer is already in table $id = (int) substr($project, 0, ; // Customer-ID = Project-ID without last digit ,, last digit is for incrementing when Customer is new if (($num_customers < 1) || ($year > (int) substr($row_last['added'], 0, 4))){ // Check IF Customers table IS EMPTY ,, OR ,, Current year is newer than last Customer's year (If last Customer was added in 2011, and new Customer is added in 2012, the new Customer-ID will be 20120001) $add_checklist = "INSERT INTO checklist VALUES ('$project', '$id', '$customer');"; // Starting variables (see above) $add_project = "INSERT INTO customers VALUES ('$id', '$customer', '$date');"; // Starting variables (see above) mysql_query($add_checklist) or die ("Query failed: " . mysql_error()); mysql_query($add_project) or die ("Query failed: " . mysql_error()); } elseif ($customer == $row_customer['customer']) { // Check IF entered Customer-Name already exists in table Customers $id = $row_customer['id']; // Obviously, get that same Customer-ID $project = (int) ((string) $id) . ((string) ($num_checklist+1)); // Project-ID will also contain that Customer-ID, with an extra digit (as stated above), which will be incremented (starting variable + numrows + 1) ($check_checklist Query) $add_checklist = "INSERT INTO checklist VALUES ('$project', '$id', '$customer');"; // Only the new Project will be added to the table Checklist, since the Customer already exists in table Customers mysql_query($add_checklist) or die ("Query failed: " . mysql_error()); } else { // When Customer(name) is new $id = $id + $num_customers; // Customer-ID is incremented (starting variable + numrows in table customers) $project = (int) substr((string)$project, 0, ; // Project-ID, 2nd last digit needs to be incremented $project = (string) ($project + $num_customers); // Project-ID, 2nd last digit needs to be incremented $project = (int) ($project . '1'); // Because new customer, Project-ID last digit will start with 1 again $add_checklist = "INSERT INTO checklist VALUES ('$project', '$id', '$customer');"; // New Project $add_project = "INSERT INTO customers VALUES ('$id', '$customer', '$date');"; // New Customer mysql_query($add_checklist) or die ("Query failed: " . mysql_error()); mysql_query($add_project) or die ("Query failed: " . mysql_error()); } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.