2 Replies Latest reply: Feb 27, 2012 3:45 PM by lmaryott RSS

    Attach User Id to inserted records

    lmaryott

      I'm building a page/form that will allow users to add new rows to my database.  I want to auto-populate the 'student_id' column in the table they are inserting into with the information contained in my table 'users' that contains their login information.  In both tables, their numeric id is 'student_id'. I have their username = their email.

       

      How is this done?

       

      to recap, i have them inserting rows into a table called sponsors.  I want the column sponsors.student_id to autopopulate with their student_id from the users table based on the fact that they are logged in as said user.

       

      I know this must be possible, but I'm incredibly new to all of this.

       

      Thanks in advance for any help!

        • 1. Re: Attach User Id to inserted records
          bregent MVP

          Typically when someone logs in you store their ID in a session variable. Are you storing the student_id or username in the session variable. If the former, then obviously you just need to use that in the insert statement.  If the latter, then you have 2 options.

           

          1. When retrieving their info at login, also capture the student_id. Then you can use the session variable directly in your insert statement
          2. Use a subselect in the insert statement that retrieves the student_id based on the username.
          • 2. Re: Attach User Id to inserted records
            lmaryott Community Member

            thanks so much.. i know exactly what you MEAN i need to do.. but have no clue how to do it    right now... the insert code from my form looks like the following:


            if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form3")) {

              $insertSQL = sprintf("INSERT INTO sponsors (studentid, schoolid, sponsorname, sponsor_address1, sponsor_address2, sponsor_city, sponsor_state, sponsor_zip, status, `level`, signed_contract, total_due, pymt_rcvd, prize_rcvd, prize_to_sgf) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

                                   GetSQLValueString($_POST['studentid'], "int"),

                                   GetSQLValueString($_POST['schoolid'], "int"),

                                   GetSQLValueString($_POST['sponsorname'], "text"),

                                   GetSQLValueString($_POST['sponsor_address1'], "text"),

                                   GetSQLValueString($_POST['sponsor_address2'], "text"),

                                   GetSQLValueString($_POST['sponsor_city'], "text"),

                                   GetSQLValueString($_POST['sponsor_state'], "text"),

                                   GetSQLValueString($_POST['sponsor_zip'], "int"),

                                   GetSQLValueString($_POST['status'], "text"),

                                   GetSQLValueString($_POST['level'], "text"),

                                   GetSQLValueString($_POST['signed_contract'], "date"),

                                   GetSQLValueString($_POST['total_due'], "double"),

                                   GetSQLValueString($_POST['pymt_rcvd'], "date"),

                                   GetSQLValueString($_POST['prize_rcvd'], "date"),

                                   GetSQLValueString($_POST['prize_to_sgf'], "date"));

             

            I have studentid and schoolid hidden so students can't populate them... i want them populated by the session variable..   right now my session variable for this page looks like this:

             

            if (!isset($_SESSION)) {

              session_start();

            }

            $MM_authorizedUsers = "";

            $MM_donotCheckaccess = "true";

             

             

            // *** Restrict Access To Page: Grant or deny access to this page

            function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {

              // For security, start by assuming the visitor is NOT authorized.

              $isValid = False;

             

             

              // When a visitor has logged into this site, the Session variable MM_Username set equal to their username.

              // Therefore, we know that a user is NOT logged in if that Session variable is blank.

              if (!empty($UserName)) {

                // Besides being logged in, you may restrict access to only certain users based on an ID established when they login.

                // Parse the strings into arrays.

                $arrUsers = Explode(",", $strUsers);

                $arrGroups = Explode(",", $strGroups);

                if (in_array($UserName, $arrUsers)) {

                  $isValid = true;

                }

                // Or, you may restrict access to only certain users based on their username.

                if (in_array($UserGroup, $arrGroups)) {

                  $isValid = true;

                }

                if (($strUsers == "") && true) {

                  $isValid = true;

                }

              }

              return $isValid;

            }

             

             

            $MM_restrictGoTo = "index.php";

            if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {  

              $MM_qsChar = "?";

              $MM_referrer = $_SERVER['PHP_SELF'];

              if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";

              if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0)

              $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];

              $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);

              header("Location: ". $MM_restrictGoTo);

              exit;

            }

            ?>

             

            given all of this information... are you able to help me with inserting the correct information/syntax here so that i can capture from my users table the studentid and schoolid to be inserted into the sponsors table?