5 Replies Latest reply on Oct 13, 2006 1:21 PM by Shane930

    Record ID retrieval

    Shane930
      I am inserting information from two forms into one row in the same table in MySQL. When the first form is inserted it creates a line ID. Is there an easy way to retrieve that ID so any further forms can correctly add information to that line in the table?

      Thanks

      Shane
        • 1. Re: Record ID retrieval
          Level 7
          SELECT MAX(expression )
          FROM tables

          Which should grab the largest id in the database

          or I use the poor mans way. If the user has a session name or id, on the
          next page / area, have it query the database for the last record from that
          person. Works for my apps. :-)


          • 2. Re: Record ID retrieval
            MikerRoo Level 1
            Never, ever, select the max id! The more your DB is used, the more that technique will yield wrong values and corrupt your DB.

            Use the LAST_INSERT_ID() function.
            EG:
            <cfquery ... ... >
            INSERT INTO t VALUES (NULL, 'Bob');

            SELECT LAST_INSERT_ID();
            </cfquery>


            • 3. Re: Record ID retrieval
              Shane930 Level 1
              Ok so I want to use the LAST_INSERT_ID() function but I continue to get this error: Error Executing Database Query.
              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 '; SELECT LAST_INSERT_ID()' at line 57

              I have it at the end of the Insert statements as follows:

              <cfquery datasource="saa">
              INSERT INTO saa.vehicle_ad (mm_username, make, model, doors, drive_type, transmission, mileage, mpg, fuel, price, options, add_info, vehicle_type, VIN) VALUES (
              <cfif IsDefined("FORM.mm_username") AND #FORM.mm_username# NEQ "">
              '#FORM.mm_username#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.make") AND #FORM.make# NEQ "">
              '#FORM.make#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.model") AND #FORM.model# NEQ "">
              '#FORM.model#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.doors") AND #FORM.doors# NEQ "">
              '#FORM.doors#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.drive_type") AND #FORM.drive_type# NEQ "">
              '#FORM.drive_type#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.transmission") AND #FORM.transmission# NEQ "">
              '#FORM.transmission#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.mileage") AND #FORM.mileage# NEQ "">
              #FORM.mileage#
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.mpg") AND #FORM.mpg# NEQ "">
              #FORM.mpg#
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.fuel") AND #FORM.fuel# NEQ "">
              '#FORM.fuel#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.price") AND #FORM.price# NEQ "">
              #FORM.price#
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.options") AND #FORM.options# NEQ "">
              '#FORM.options#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.add_info") AND #FORM.add_info# NEQ "">
              '#FORM.add_info#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.vehicle_type") AND #FORM.vehicle_type# NEQ "">
              '#FORM.vehicle_type#'
              <cfelse>
              NULL
              </cfif>
              ,
              <cfif IsDefined("FORM.vin") AND #FORM.vin# NEQ "">
              '#FORM.vin#'
              <cfelse>
              NULL
              </cfif>
              );
              SELECT LAST_INSERT_ID();
              </cfquery>

              But it will not run. Also, how does this output to the next form to be used. Is it a SESSION variable or ??

              Any help would be great. Thanks, Shane
              • 4. Re: Record ID retrieval
                MikerRoo Level 1
                It looks like cf_dev2 beat me to the latest answer on your other thread.

                You need to edit the datasource, in the CF administrator, to add ?allowMultiQueries=true at the end of the JDBC URL.
                • 5. Re: Record ID retrieval
                  Shane930 Level 1
                  Yes, thanks for the input.

                  Shane