4 Replies Latest reply on Dec 30, 2007 8:39 AM by (Tim_Gavin)

    Insert into Two Tables Wizard (need to update if record exists)

      This has been asked before over at Interakt, but has never been fully answered.

      My site has a few forms, each asking for customer information. If a visitor fills out *any* one of the forms, his information is stored in a mysql record and a cookie is set with his email address.

      If he comes back to the site and goes to a form, the cookie is read, his info is retrieved from mysql and the form is populated with his information.

      Here's the rub: I have a form that requests customer information, as well as information for a price quote. If the visitor filling out the form is new, then that form will INSERT into two tables; 'customers' & 'price_quote'. However, if an existing customer fills out the quote form, then I want to UPDATE the 'customers' table and INSERT into the 'price_quote' table.

      I've already created the form using the Insert Into Two Tables wizard, and it works correctly - unless that customer is already in the db, then I just receive an 'duplicate entry' error for the customers table.

      How can I achieve this?
        • 1. Re: Insert into Two Tables Wizard (need to update if record exists)
          Level 1
          Nobody?????

          I've seen other examples of this, BUT, instead of updating - or even doing nothing at all - the code supplied by interakt is to display an error message. That's not acceptable. There's nothing graceful about that. Graceful is letting the form proceed with its business, and just not insert the record.

          I'm pulling my hair out and getting highly frustrated (and somewhat angry) that this doesn't seem to be possible.

          Forget the UPDATE. How about if the record exists do NOTHING at all. Or, at the most let me execute a send email trigger.

          if(recordExists) {
          sendEmail
          } else {
          performInsertTransaction
          }

          I have a ton of forms that I need to do (one with over 100 fields!), and It'll kill me if I have to do this by hand.

          Anybody?
          • 2. Re: Insert into Two Tables Wizard (need to update if record exists)
            Yo- you can change the tNG_inset.class.php file put in the includes folder to at least ignore duplicates... find where it builds the beginning of the query- look for

            $sql = 'INSERT INTO ' . $this->table;

            and change it to

            $sql = 'INSERT IGNORE INTO ' . $this->table;

            that'll at least suppress the error for the duplicate rows. now, if you can figure out how to get a suppressed, would-be-generated id from one table to pass to the other, you get a prize. lol. I've been trying to figure that out for 3 days...
            t bender
            • 3. Re: Insert into Two Tables Wizard (need to update if record exists)
              glensbo Level 1
              for both

              The interakt team did a tutorial on building a blog. Part of this tutorial keeps trak of users commenting on different topics ie logging in and commenting (price_quote). You might get some hints as to building your database here.

              http://www.adobe.com/devnet/dreamweaver/articles/php_blog4.html

              this is part 4 but you will find the others easily

              regards
              • 4. Re: Insert into Two Tables Wizard (need to update if record exists)
                Level 1
                Read it.

                The problem with that, and all other interakt examples is that they spit out an error message instead of gracefully dying, or doing what we want - updating the table.

                I found it much easier to just do it by hand.