3 Replies Latest reply on Dec 31, 2010 8:49 AM by swood30

    Multiple SQL Tables

    Captain825

      Hi

       

      Are there any tutorials that deal with how to save data from one form to two or three tables and then retrieving data from two or three tables and displaying it on one form?

       

      Thanks

        • 1. Re: Multiple SQL Tables
          avsesis

          The SQL syntax for  inserting into a database table is 'Insert into tableName1 (Field1, Field2, Field3) values (Field1value, Field2value Field3value)'

          so you would need to use this for all tables that you want to save the data to ie 'Insert into tablename2 (Field4, Field5, Field6) values  (Field4value, Field5value, Field6value)'

           

          To retrieve the data your SQL syntax will depend on whether you have a connecting key field such as tablename2 .ID in tableName1 list of fields. If this were the case then your syntax will be

          'Select tableName1.Field1, tableName1.Field2, tableName1.Field3, tableName2.Field4, tableName2.Field5, tableName2.Field6 from tableName1 inner join tableName2 on tableName1.tableName2ID = tableName2.ID where tableName1.ID=(whatever key that you are using to store and retrieve the data by)'

           

          Otherwise retrieving data from 2 tables that are unconnected would have a syntax of this

          'Select tableName1.Field1, tableName1.Field2, tableName1.Field3,  tableName2.Field4, tableName2.Field5, tableName2.Field6 from tableName1 cross join tableName2  where  tableName1.ID=(whatever key that you are using to store and retrieve the  data by) and tableName2.ID=(whatever key that you are using to store and retrieve the  data by)'

           

          Retrieving data using the joins will allow you to bind your file straight to the collected data.

          • 2. Re: Multiple SQL Tables
            swood30

            avsesis-

            Can you explain in a little more detail your solution for inserting into multiple tables?

            An example of the actual code from the Dreamweaver Insert Record server behavior is shown below.

            If I wanted to insert the same data into 2 tables (Table1 and Table2), how/where in this code would I insert further $insertSQL commands?

             

            I have copied the lines below in bold and placed directly beneath the GetSQL Value striing for Field2, but the result is that the records are inserted into my 2nd table and not my 1st.  I have also tried to place the insertSQL statement in other places separated by ; but I always get syntax errors in my code.

             

            Thanks!!

             

            Example Code:

            }

            $editFormAction = $_SERVER['PHP_SELF'];
            if (isset($_SERVER['QUERY_STRING'])) {
              $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
            }

            if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Form1")) {
              $insertSQL = sprintf("INSERT INTO Table1 (Field1, Field2) VALUES (%s, %s)",
                                   GetSQLValueString($_POST['Field1'], "text"),
                                   GetSQLValueString($_POST['Field2'], "text"));

             

              mysql_select_db($database_connDB, $connDB);
              $Result1 = mysql_query($insertSQL, $connDB) or die(mysql_error());

              $insertGoTo = "NextPage.php";
              if (isset($_SERVER['QUERY_STRING'])) {
                $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
                $insertGoTo .= $_SERVER['QUERY_STRING'];
              }
              header(sprintf("Location: %s", $insertGoTo));
            }

            • 3. Re: Multiple SQL Tables
              swood30 Level 1

              Posted too soon I guess.  I tried a couple more things, finally, the insert shown in bold below worked correctly!!!

               

              Example Code:

              }

              $editFormAction = $_SERVER['PHP_SELF'];
              if (isset($_SERVER['QUERY_STRING'])) {
                $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
              }

              if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Form1")) {
                $insertSQL = sprintf("INSERT INTO Table1 (Field1, Field2) VALUES (%s, %s)",
                                     GetSQLValueString($_POST['Field1'], "text"),
                                     GetSQLValueString($_POST['Field2'], "text"));

               

                mysql_select_db($database_connDB, $connDB);
                $Result1 = mysql_query($insertSQL, $connDB) or die(mysql_error());

               

                $insertSQL = sprintf("INSERT INTO Table2 (Field1, Field2) VALUES (%s, %s)",
                                     GetSQLValueString($_POST['Field1'], "text"),
                                     GetSQLValueString($_POST['Field2'], "text"));

               

                mysql_select_db($database_connDB, $connDB);
                $Result2 = mysql_query($insertSQL, $connDB) or die(mysql_error());

               

                $insertGoTo = "NextPage.php";
                if (isset($_SERVER['QUERY_STRING'])) {
                  $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
                  $insertGoTo .= $_SERVER['QUERY_STRING'];
                }
                header(sprintf("Location: %s", $insertGoTo));
              }