5 Replies Latest reply on Sep 9, 2016 7:11 PM by chiweez

    Best way to validate unique input via PHP API

    doughemi

      I have a web form which populates a new record in my Ballot table, using newAddCommand() in PHP API.

       

      What is the best way to validate that the UserID field is unique in the new record before actually creating a record? IOW, how do I emulate the Validate Unique Value function in the API?

        • 1. Re: Best way to validate unique input via PHP API
          Johan Hedman

          Have a field in each table with calculation for Get(UUID) and it will always be unique

          • 2. Re: Best way to validate unique input via PHP API
            Mike_Mitchell

            I think he means he wants to avoid duplicates for user-entered fields.

             

            One way around this is to perform a Find using the value the user inputs. Don't create the record if one's already there.

             

            Beyond that, I'm not 100% sure what the behavior is if you have a validation at the schema level that's violated via a PHP insert. (Never ran into that before.) I would say, try it and see what error code is returned. Then trap for that.

            • 3. Re: Best way to validate unique input via PHP API
              nicolai

              There is a general advise, not only for Filemaker, but for any other web system with database back-end: let the database create the primary keys.

               

              Uniqueness should be set up as a validation on the filed (in SQL databases that is a default for primary keys).

               

              If you need this key for further querying you will need to retrieve it from the new record object.

              • 4. Re: Best way to validate unique input via PHP API
                beverly

                Yes, yes, yes!!

                 

                • every db table (filemaker, sql, ???) row/record should have a unique identifier - always (even if a small lookup table)

                • this should be auto-enter by the database (only). never try to fill the value by entry or import.

                • this can be serial increment or UUID

                • this should be a Primary Key (you may have other key fields which are unique and used otherwise for relationships, too)

                • if you have other key fields that are used in relationships, you may enter or import them

                • sometimes a previous key field is a temporary key until the db-created Primary Key can be pushed to related child rows/records in other tables, as needed

                • when you create a row/record in a database, allow this primary key to be filled by the database and get the result back if you need to use it

                 

                beverly

                3 of 3 people found this helpful
                • 5. Re: Best way to validate unique input via PHP API
                  chiweez

                  I just ran into this on a recent project. One key thing to mention....be very cognizant of your Find query.

                   

                  If you have a user named "jim", performing a FM PHP API "Find" for that value could potentially return the user name "jimmy".

                   

                  So, instead of this:

                      $request = $fm->newFindCommand($layout);

                      $request->addFindCriterion('UserName', $username);

                      $result = $request->execute();

                   

                  Do this:

                      $request = $fm->newFindCommand($layout);

                      $request->addFindCriterion('UserName', "==".$username);

                      $result = $request->execute();

                   

                  Note the double "==" signs to bring back an EXACT match for the user name value.