6 Replies Latest reply on Jul 11, 2008 2:54 PM by (alterity)

    help: insert or update depending on existence of record

      I think I cannot think today. This doesn't seem like it should take brain surgery, but I am contemplating it.

      I send out emails with an "update your subscription options" link in the footer. This link passes a URL variable based upon a user id in a DB I have no access to at all. But anyway, the link would end up looking like this:

      http://mywebsite.com/subscription-options/index.php?id=34786

      Okay, what I need to set up on a single page is a form that will:

      1. create a record for that id if it is not found in my mysql DB (and insert some other things from the form on the page)

      or

      2. If that id is in the database, then update the record based on user input.

      I can do the INSERT record just fine by grabbing the id variable but I am at my wit's end trying to figure out how to get the form to realize it needs to be an update (if the id is in the DB) form rather than an insert form.

      Do I need to create a totally separate Insert and then and update transaction and then link them somehow. If so, I would assume that the id for the insert needs to continue grabbing the url id variable, but what will make the update record realize it needs "see" that id, realize it's in the DB, tell the Insert transaction it's taking over and then do the update instead of choking and thinking it's trying to insert a record that's already in the DB.

      If this question has been asked and answered, please just point me in that direction.

      Hurry. I am calling the Dr. to schedule my brain surgery. Thanks!
        • 1. Re: help: insert or update depending on existence of record
          Günter Schenk Level 4
          Hi alterity,

          that´s exactly what ADDT´s "Dynamic Form" does :: if it doesn´t receive an URL parameter (say, id), it switches to Insert Record mode, otherwise it´s going to perform an Update Record transaction.

          That said, I suggest to create a separate "stand alone" Dynamic Form for this purpose which doesn´t have to link back to whatever Dynamic List -- just have it redirect to a thankyou.php page.

          However, in your case you´ll of course not going to check against an URL parameter (this one always exists, right ?), but rather against the result of a recordset. After creating a Dynamic Form, you´ll see several page elements (text, buttons, form elements) wrapped in Show IF Conditional regions, which always check against whether a certain URL parameter is empty (aka "set") or not, example: if (@$_GET['id'] == "") {

          I *think* that you´ll have to replace those default condition with Dreamweaver´s "Show If Recordset is empty" behaviours.

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: help: insert or update depending on existence of record
            Level 1
            Gunter, I think I'm missing some steps.

            I need to insert that url id variable. That becomes the record's unique id in the DB so I have to send it for the Insert and I have to have the the insert transaction GET that variable.

            I also don't see how I can get by without it for any subsequent update, even if I use a recordset for it, it will still have to include a line in the query WHERE the id = (ADDT thing used to define the GET id in the url)

            I'm just not sure how to tell the dynamic form to Insert when that url id is not in the database but to update that record if it is in the DB.

            Have I completely misread you?
            • 3. Re: help: insert or update depending on existence of record
              921creative
              alterity- Correct me if I'm wrong, but it seems like a user would not receive an email from you with the "update subscription options" link at the bottom unless that user is already subscribed and therefore already has an id in your database. Therefore you would not need to insert an id, only update a record which already has an id.
              • 4. Re: help: insert or update depending on existence of record
                Level 1
                Ah, yes. this is the rub. This id is inserted into that "update your subscriptions" from a totally separate DB over which I have no access or control. That part is so convoluted I ..., well...

                Anyway, so the only way that subscriber gets into MY database, accessible over the Web (whereas the other aforementioned DB is not), is to pass that id to a page on my site that grabs that id and creates a record for it.

                So the record with that id is NOT necessarily in my DB before they click on that "update your subscription options" link in the footer of the email.

                The cracked part is that, if they have ever done that, that is get one of those emails and clicked that link and set their options, thereby creating a record, they are always going to get that same link in the footer with their id in the url - so they can come back and adjust their settings. That's the point.

                I should mention that I can't add any data to that other DB so I could perform some tests on it that logically come to mind. I can't touch it.

                So there's no way, at that point, - when that link is created in the footer of the email - where I can tell whether they've ever tried to set or update a record on my site. If I could, I could send them to an insert page or to an update page, depending.

                But since I can't, I need that single page to "see" that url variable and "know" whether to perform an insert (if the id is not in the DB) or an update if it is in the DB.

                Believe me, I wish I didn't have to do this.
                • 5. Re: help: insert or update depending on existence of record
                  This exact technique is covered step by step in the tutorial for creating a website forum, which can be found at the interaktonline.com website.
                  • 6. Re: help: insert or update depending on existence of record
                    Level 1
                    Thanks. And thanks for that tutorial recommendation.

                    Sometimes it takes a while for the obvious to sink in. Gunter, you were headed in the right direction, but I was trying to make things too complicated.

                    If anyone looks at this and says: well, did that every get solved, here's the ridiculously simple answer.

                    So there's an id variable being passed in the url. This id needs to be the determining factor about whether the page will perform an Insert or an Update.

                    The FIRST thing to do is create a query that simply says "select the id from the table where the id = {GET.id}. (Or if you know what you're doing with complex recordsets, define a name that basically equals that get. You know what I mean. I call this recordset rsID.

                    That recordset is either going to come up with a record from the database or it's now.

                    So I dont' use the form wizards anymore so just make two forms, one for the fields you'd want inserted and the other one for the fields you want to be able to update.

                    Call the forms different names, of course.

                    Surrounc each form with a conditional - show if the recordset (rsID) is empty should go around the Insert form - show if the recordset (rsID) is not emply should go around the Insert form.

                    Next the most amazing things happen. You simply add an Insert transaction that gets the value of the url id and inserts it as the unique record of the record.

                    Then you add another recordset that gets all the fields of the table. This recordset is used by the update translaction. Here you also build in a similar parameter that equates the url id with the id of the recordset, another on of these things

                    $customerID_rsUpdate = "-1";
                    if (isset($_GET['id'])) {
                    $customerID_rsUpdate = $_GET['id'];
                    }
                    mysql_select_db($database_mlam1, $mlam1);
                    $query_rsUpdate = sprintf("SELECT peps_subscribe.pepsid_subscribe, peps_subscribe.nl_subscribe, peps_subscribe.edu_subscribe, peps_subscribe.specials_subscribe, peps_subscribe.surveys_subscribe, peps_subscribe.optout_subscribe, peps_subscribe.bademail_subscribe FROM peps_subscribe WHERE peps_subscribe.id_subscribe = %s", GetSQLValueString($customerID_rsUpdate, "text"));
                    $rsUpdate = mysql_query($query_rsUpdate, $mlam1) or die(mysql_error());
                    $row_rsUpdate = mysql_fetch_assoc($rsUpdate);
                    $totalRows_rsUpdate = mysql_num_rows($rsUpdate);

                    And then you add an update transaction where the primary id is the url variable.

                    All this does is:

                    If the url id is NOT in the database, the insert form shows and the insert transaction takes place as it normally would, because it's associated with "form1" and KT_Insert1 in the submission.

                    If the url id IS in the database, the update form shows and the update transaction takes place as it normally would, because it's associated with "form2" and KT_Update1 in the submission.

                    I know, this is so completely transparent I should write tutorials. But maybe you can "see" what I'm saying.