Jump to content


Photo

Create two user_type tables from a user table

create_table

Best Answer Barand, 28 April 2013 - 08:25 PM

Make the primary keys on the "honor student" and "faculty member" table auto_increment

Go to the full post


  • Please log in to reply
22 replies to this topic

#1 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 23 April 2013 - 10:56 PM

Hello,

 

I am creating a form for an administrator to fill in USER information.  So far, I have a database in MySQL that has a USER table, with a user_type field and two user_type tables: STUDENT_USER and FACULTY_USER.

 

I am setting up a drop-down list for the administrator to select whether the new USER is a "student" or "faculty."  When they select either one and submit the application to log in, I want the database to automatically create a new, child table of the selected type with a foreign key of user_id from the parent USER table.  

 

What is a query I can use to create a related table based on a newly created USER?  How do I start coding this?  Are there easier options I have?

 

Attached File  user and user_type tables.png   28.03KB   9 downloads

 

so far, my php code for the add, update, delete form:

 

 

<html>
    <head>
    </head>
    <body>
    
    <?php
        
        $connect = mysql_connect('localhost', 'root', '');
        if (!$connect){
            die("Cannot connect: " . mysql_error());
        }
        
        mysql_select_db('honors thesis', $connect);
        
        if(isset($_POST['update'])){
          $UpdateQuery = "UPDATE honor student SET student_id='$_POST[studentID]',
                                          user_id='$_POST[userName]',
                                          password='$_POST[passWord]',
                                          user_type='$_POST[userType]',
                                          last_name='$_POST[lastName]',
                                          first_name='$_POST[firstName]',  
                                          address='$_POST[addRess]',
                                          phone='$_POST[pHone]',
                                          email='$_POST[eMail]',
                                          committee_num='$_POST[committeeNum]',
                                          degree_program='$_POST[degreeProgram]' 
                                              
                                          WHERE 
                                          
                                          user_id='$_POST[hidden]'";
          mysql_query($UpdateQuery, $connect);                                
            
        };
        
        if(isset($_POST['delete'])){
          $DeleteQuery = "DELETE FROM honor student WHERE user_id='$_POST[hidden]'";
          mysql_query($DeleteQuery, $connect);                                
            
        };
        
        
        if(isset($_POST['add'])){
          $AddQuery = "INSERT INTO honor student (user_id, user_name, password, user_type, 
                                         last_name, first_name, address, phone, 
                                         email, committee_num, degree_program) 
                                         
                                         VALUES ('$_POST[uuserID]', '$_POST[uuserName]',
                                                 '$_POST[upassWord]', '$_POST[uuserType]',
                                                 '$_POST[ulastName]', '$_POST[ufirstName]', 
                                                 '$_POST[uaddRess]', '$_POST[upHone]',
                                                 '$_POST[ueMail]', '$_POST[ucommitteeNum]',
                                                 '$_POST[udegreeProgram]')";
          mysql_query($AddQuery, $connect);                                
            
        };
        
        
        $sql = "SELECT * FROM honor student";
        $myUsers = mysql_query($sql, $connect);
        echo "<table border=1>
            <tr>
            <th>User ID</th>
            <th>User Name</th>
            <th>Password</th>
            <th>User Type</th>
            <th>Last Name</th>
            <th>First Name</th>
            <th>Address</th>
            <th>Phone</th>
            <th>Email</th>
            <th>Committee Number</th>
            <th>Degree Program</th>
            </tr>";
        
        while($userRecord = mysql_fetch_array($myUsers)){
            echo "<form action=index.php method=post>";
            echo "<tr>";
            echo "<td>" . "<input type=text name=userID value='{$userRecord['user_id']}'>" . " </td>";
            echo "<td>" . "<input type=text name=userName value='{$userRecord['user_name']}'>" . " </td>";
            echo "<td>" . "<input type=text name=passWord value='{$userRecord['password']}'>" . " </td>";
            echo "<td>" . "<input type=text name=userType value='{$userRecord['user_type']}'>" . " </td>";
            echo "<td>" . "<input type=text name=lastName value='{$userRecord['last_name']}'>" . " </td>";
            echo "<td>" . "<input type=text name=firstName value='{$userRecord['first_name']}'>" . " </td>";
            echo "<td>" . "<input type=text name=addRess value='{$userRecord['address']}'>" . " </td>"; 
            echo "<td>" . "<input type=text name=pHone value='{$userRecord['phone']}'>" . " </td>";
            echo "<td>" . "<input type=text name=eMail value='{$userRecord['email']}'>" . " </td>";
            echo "<td>" . "<input type=text name=committeeNum value='{$userRecord['committee_num']}'>" . " </td>";
            echo "<td>" . "<input type=text name=degreeProgram value='{$userRecord['degree_program']}'>" . " </td>";
            echo "<td>" . "<input type=hidden name=hidden value='{$userRecord['user_id']}'>" . " </td>";
            echo "<td>" . "<input type=submit name=update value=update" . " </td>";
            echo "<td>" . "<input type=submit name=delete value=delete" . " </td>";
            echo "</tr>";
            echo "</form>";
        }
        echo "<form action=index.php method=post>";
        
        echo "<tr>";
        echo "<td><input type=text name=uuserID></td>";
        echo "<td>" . "<input type=text name=uuserName></td>";
        echo "<td>" . "<input type=text name=upassWord></td>";
        echo "<td>" . "<input type=text name=uuserType></td>";
        echo "<td>" . "<input type=text name=ulastName></td>";
        echo "<td>" . "<input type=text name=ufirstName></td>";
        echo "<td>" . "<input type=text name=uaddRess></td>"; 
        echo "<td>" . "<input type=text name=upHone></td>";
        echo "<td>" . "<input type=text name=ueMail></td>";
        echo "<td>" . "<input type=text name=ucommitteeNum></td>";
        echo "<td>" . "<input type=text name=udegreeProgram></td>";
        echo "<td>" . "<input type=submit name=add value=add" . " </td>";
         
        echo "</form>";
        echo"</table>";
        
        mysql_close($connect);
        
    ?>
       
    </body>
</html>

 



#2 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 23 April 2013 - 11:31 PM

would something along the lines of this work? (of course with proper MySQL coding)

 

 

If(user_type == student){
Create_table(student) WHERE user_id IS user.user_id;
else {
Create_table(faculty) WHERE user_id IS user.user_id;
}


#3 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 25 April 2013 - 08:19 AM

Let me rephrase my question:

 

 

I have already created a simple form for an administrator to fill in USER information.  So far, I have a database in MySQL that has a USER table, with a user_type field and two user_type "child" tables: STUDENT_USER and FACULTY_USER.

 

I am setting up a drop-down list for the administrator to select whether the new USER is a "student" or "faculty."  When they select either one and submit the form to create the new USER, I want the database to automatically create a new row in the corresponding child table of the selected type with a foreign key of user_id from the parent USER table.

 

 

As stated  above, I'm thinking it should look somewhat along the lines of:

 

 
If(user_type == student){
INSERT INTO student_user WHERE user_id IS user.user_id;
else {
INSERT INTO faculty_user WHERE user_id IS user.user_id;
}
 
I realized that create_table creates an entirely new table, not just a row in an already existing table...

 

Any help would be greatly appreciated!



#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 25 April 2013 - 03:21 PM

You can't use WHERE in an insert query.

 

The syntax is

 

INSERT INTO tablename (colname1, colname2, ... , colnameN) VALUES (val1, val2, ..., valN)


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 25 April 2013 - 08:57 PM

Hey Barand,

 

Thanks for the help... I got further with it, but still no luck connecting to the child table.  So far I have this:

 

//initial form to post USER info

 

if(isset($_POST['add'])){
          $AddQuery = "INSERT INTO user (user_id, user_name, password, user_type, 
                                         last_name, first_name, address, phone, 
                                         email, committee_id, degree_program) 
                                         
                                         VALUES ('$_POST[uuserID]', '$_POST[uuserName]',
                                                 '$_POST[upassWord]', '$_POST[uuserType]',
                                                 '$_POST[ulastName]', '$_POST[ufirstName]', 
                                                 '$_POST[uaddRess]', '$_POST[upHone]',
                                                 '$_POST[ueMail]', '$_POST[ucommitteeId]',
                                                 '$_POST[udegreeProgram]')";
          mysql_query($AddQuery, $connect);                                
          
 
          //where I think the new 'if' statement should go to tell it to connect to HONOR_STUDENT table and insert a new row...
          if($AddQuery.'$_POST[uuserType]' == "Student"){
              $con=mysqli_connect('localhost','root','');
              // Check connection
              if (mysqli_connect_errno()){
                  echo "Failed to connect to MySQL: " . mysqli_connect_error();
              }
 
              $studentSql="INSERT INTO honor student(student_id, user_id)
                                                     VALUES ('','$_POST[uuserID]')";
 
              if (!mysqli_query($con,$studentSql)){
                  die('Error: ' . mysqli_error($con));
              }
              echo "1 record added";
 
              mysqli_close($con);  
          }
        }


#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 27 April 2013 - 05:29 AM

 

Hey Barand,

 

Thanks for the help... I got further with it, but still no luck connecting to the child table.  So far I have this:

 

You don't connect to tables, only to databases which you would only need to do once per script (unless your tables are are on separate servers/databases)

 

You specify the table in the insert statement

 

INSERT INTO tableA .....

or

INSERT INTO tableB ....


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 27 April 2013 - 09:11 PM

Ok,

 

That makes sense, since I'm already connected to the database, I don't need a separate connection...

 

I changed it to the following and am getting an error on the line with "$user_type = $_POST[uuserType];"  Use of undefined constant uuserType - assumed 'uuserType'

I tried to google help for that and nothing comprehensible to me came up... what am I not doing right here?

 

 

if(isset($_POST['add'])){
          $AddQuery = "INSERT INTO user (user_id, user_name, password, user_type, 
                                         last_name, first_name, address, phone, 
                                         email, committee_id, degree_program) 
                                         
                                         VALUES ('$_POST[uuserID]', '$_POST[uuserName]',
                                                 '$_POST[upassWord]', '$_POST[uuserType]',
                                                 '$_POST[ulastName]', '$_POST[ufirstName]', 
                                                 '$_POST[uaddRess]', '$_POST[upHone]',
                                                 '$_POST[ueMail]', '$_POST[ucommitteeId]',
                                                 '$_POST[udegreeProgram]')";
          mysql_query($AddQuery, $connect);                                
 
 
          $uuser_type = $_POST[uuserType];
          if($uuser_type == "Student"){
              
              mysql_query("INSERT INTO honor student(student_id, user_id)
                                       VALUES (' ','$_POST[uuserID]')");
          } else{
              mysql_query("INSERT INTO faculty member(faculty_id, user_id)
                                       VALUES (' ','$_POST[uuserID]')");
          }
              
          echo "1 record added";
  
        }


#8 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 28 April 2013 - 06:14 AM

String values should be in quotes, even when used as array keys, otherwise PHP thinks they are undefined constants.

 

$_POST['uuserType'] and not $_POST[uuserType] etc


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#9 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 April 2013 - 09:05 AM

Changing that got rid of the error, but did not INSERT anything into the proper child table... is my INSERT INTO statement still not correct?



#10 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 28 April 2013 - 11:09 AM

Faculty_id is defined as your primary key but you always give it an empty string as a value. If it is auto_increment leave that column out of the query. If it is not auto_increment you must supply a unique value. Also, don't put numeric values in quotes. Note, you table name is faculty_user, not "faculty member" (don't have spaces in table names.)

 

Checking your query for errors would have told you some of this

 

So you insert query would be

$result = mysql_query("INSERT INTO faculty_user(user_id)
             VALUES ($_POST['uuserID'])");
if (!$result) die(mysql_error());

Edited by Barand, 28 April 2013 - 11:09 AM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#11 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 April 2013 - 11:29 AM

Barand,

 

Thank you for the help!  I was able to piece together an "if" statement that inputs the new user_id into the child table, however it only works the first time... it won't do it once there's a record in the child table...  any reason why?  Do i need to refresh the page or reset a value to null somewhere once the INSERT is run?

 

 

if(isset($_POST['add'])){
          $AddQuery = "INSERT INTO user (user_id, user_name, password, user_type, 
                                         last_name, first_name, address, phone, 
                                         email, committee_id, degree_program) 
                                         
                                         VALUES ('$_POST[uuserID]', '$_POST[uuserName]',
                                                 '$_POST[upassWord]', '$_POST[uuserType]',
                                                 '$_POST[ulastName]', '$_POST[ufirstName]', 
                                                 '$_POST[uaddRess]', '$_POST[upHone]',
                                                 '$_POST[ueMail]', '$_POST[ucommitteeId]',
                                                 '$_POST[udegreeProgram]')";
          mysql_query($AddQuery, $connect);  
          
          
          if($_POST['uuserType'] = "Student"){
              $uuser_type = "INSERT INTO `honor student` (user_id)
                                       SELECT `user_id` 
                                       FROM `user` 
                                       ORDER BY `user_id`
                                       DESC LIMIT 1";
          } else{
              $uuser_type = "INSERT INTO `faculty member`(user_id)
                                       SELECT `user_id`
                                       FROM `user` 
                                       ORDER BY `user_id`
                                       DESC LIMIT 1";
          }
            mysql_query($uuser_type);
            echo "1 record added";

}



#12 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 28 April 2013 - 12:06 PM

See what mysql_error() has to tell you. That's what it is for.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#13 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 28 April 2013 - 12:22 PM

Can  you post the results of

 

SHOW CREATE TABLE tablename

 

for each of the three tables. I suspect things have changed since you posted that image in your original post and I'd like to see how the columns are defined.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#14 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 April 2013 - 01:33 PM

I guess my original description was not clear, I do not want a CREATE TABLE, just INPUT INTO as you clarified.  I have existing tables that I just want the fields automatically filled when a `user` of type "Student" is created.

 

I am not CREATING new tables.

 

When I tried adding  "if (!$uuser_type) die(mysql_error());" it did not display anything either...



#15 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 April 2013 - 01:57 PM

Here is my full code for the "if" statement that only allows one addition to the child table:

<html>

    <head>
    </head>
    <body>
      
    <?php
         
        $connect = mysql_connect('localhost', 'root', '');
        if (!$connect){
            die("Cannot connect: " . mysql_error());
        }
        mysql_select_db('honors thesis', $connect);
        
        if(isset($_POST['update'])){
          $UpdateQuery = "UPDATE user SET user_id='$_POST[userID]',
                                          user_name='$_POST[userName]',
                                          password='$_POST[passWord]',
                                          user_type='$_POST[userType]',
                                          last_name='$_POST[lastName]',
                                          first_name='$_POST[firstName]',  
                                          address='$_POST[addRess]',
                                          phone='$_POST[pHone]',
                                          email='$_POST[eMail]',
                                          committee_id='$_POST[committeeId]',
                                          degree_program='$_POST[degreeProgram]' 
                                              
                                          WHERE 
                                          
                                          user_id='$_POST[hidden]'";
          mysql_query($UpdateQuery, $connect);                                
            
        }
        
        if(isset($_POST['delete'])){
          $DeleteQuery = "DELETE FROM user WHERE user_id='$_POST[hidden]'";
          mysql_query($DeleteQuery, $connect);                                
            
        }
        
        
        if(isset($_POST['add'])){
          $AddQuery = "INSERT INTO user (user_id, user_name, password, user_type, 
                                         last_name, first_name, address, phone, 
                                         email, committee_id, degree_program) 
                                         
                                         VALUES ('$_POST[uuserID]', '$_POST[uuserName]',
                                                 '$_POST[upassWord]', '$_POST[uuserType]',
                                                 '$_POST[ulastName]', '$_POST[ufirstName]', 
                                                 '$_POST[uaddRess]', '$_POST[upHone]',
                                                 '$_POST[ueMail]', '$_POST[ucommitteeId]',
                                                 '$_POST[udegreeProgram]')";
          mysql_query($AddQuery, $connect);  
          
          
          if($_POST['uuserType'] = "Student"){
              $uuser_type = "INSERT INTO `honor student` (user_id)
                                       SELECT `user_id` 
                                       FROM `user` 
                                       ORDER BY `user_id`
                                       DESC LIMIT 1";
          } else{
              $uuser_type = "INSERT INTO `faculty member`(user_id)
                                       SELECT `user_id`
                                       FROM `user` 
                                       ORDER BY `user_id`
                                       DESC LIMIT 1";
          }
            mysql_query($uuser_type);
            echo "1 record added";
          
        }
          
        $sql = "SELECT * FROM user";
        $myUsers = mysql_query($sql, $connect);
        echo "<table border=1>
            <tr>
            <th>User ID</th>
            <th>User Name</th>
            <th>Password</th>
            <th>User Type</th>
            <th>Last Name</th>
            <th>First Name</th>
            <th>Address</th>
            <th>Phone</th>
            <th>Email</th>
            <th>Committee ID</th>
            <th>Degree Program</th>
            </tr>";
        
        while($userRecord = mysql_fetch_array($myUsers)){
            echo "<form action=index.php method=post>";
            echo "<tr>";
            echo "<td>" . "<input type=text name=userID value='{$userRecord['user_id']}'>" . " </td>";
            echo "<td>" . "<input type=text name=userName value='{$userRecord['user_name']}'>" . " </td>";
            echo "<td>" . "<input type=text name=passWord value='{$userRecord['password']}'>" . " </td>";
            echo "<td>" . "<input type=text name=userType value='{$userRecord['user_type']}'>" . " </td>";
            echo "<td>" . "<input type=text name=lastName value='{$userRecord['last_name']}'>" . " </td>";
            echo "<td>" . "<input type=text name=firstName value='{$userRecord['first_name']}'>" . " </td>";
            echo "<td>" . "<input type=text name=addRess value='{$userRecord['address']}'>" . " </td>"; 
            echo "<td>" . "<input type=text name=pHone value='{$userRecord['phone']}'>" . " </td>";
            echo "<td>" . "<input type=text name=eMail value='{$userRecord['email']}'>" . " </td>";
            echo "<td>" . "<input type=text name=committeeId value='{$userRecord['committee_id']}'>" . " </td>";
            echo "<td>" . "<input type=text name=degreeProgram value='{$userRecord['degree_program']}'>" . " </td>";
            echo "<td>" . "<input type=hidden name=hidden value='{$userRecord['user_id']}'>" . " </td>";
            echo "<td>" . "<input type=submit name=update value=update" . " </td>";
            echo "<td>" . "<input type=submit name=delete value=delete" . " </td>";
            echo "</tr>";
            echo "</form>";
            
            if($userRecord['user_type'] === "Student"){
              $uuser_type = "INSERT INTO `honor student` (user_id)
                                       SELECT `user_id` 
                                       FROM `user` 
                                       ORDER BY `user_id`
                                       DESC LIMIT 1";
            } else{
              $uuser_type = "INSERT INTO `faculty member`(user_id)
                                       SELECT `user_id`
                                       FROM `user` 
                                       ORDER BY `user_id`
                                       DESC LIMIT 1";
            }
            
            mysql_query($uuser_type);
            echo "1 record added";
            
        }
        echo "<form action=index.php method=post>";
        
        echo "<tr>";
        echo "<td><input type=text name=uuserID></td>";
        echo "<td>" . "<input type=text name=uuserName></td>";
        echo "<td>" . "<input type=text name=upassWord></td>";
        echo "<td>" . "<select name=uuserType>
                        <option value=''> -Select: </option>
                        <option>Student</option>
                        <option>Faculty</option>
                        </select>
                        </td>";
        echo "<td>" . "<input type=text name=ulastName></td>";
        echo "<td>" . "<input type=text name=ufirstName></td>";
        echo "<td>" . "<input type=text name=uaddRess></td>"; 
        echo "<td>" . "<input type=text name=upHone></td>";
        echo "<td>" . "<input type=text name=ueMail></td>";
        echo "<td>" . "<input type=text name=ucommitteeId></td>";
        echo "<td>" . "<select name=udegreeProgram>
                        <option value=''> -Select Program: </option>
                        <option>Anthropology</option>
                        <option>Art</option>
                        <option>Art Studio</option>
                        <option>Art Education</option>
                        <option>Athletic Training</option>
                        <option>Biology</option>
                        <option>Business Administration</option>
                        <option>Chemistry</option>
                        <option>Communication</option>
                        <option>Communication Disorders</option>
                        <option>Computer Science</option>
                        <option>Earth Science</option>
                        <option>Economics</option>
                        <option>Education</option>
                        <option>English</option>
                        <option>Exercise Science</option>
                        <option>Exercise Science, K-12</option>
                        <option>French</option>
                        <option>Geography</option>
                        <option>German</option>
                        <option>History</option>
                        <option>History and Social Science, Secondary Edu</option>
                        <option>Information and Library Science</option>
                        <option>Italian</option>
                        <option>Journalism</option>
                        <option>Liberal Studies</option>
                        <option>Mathematics</option>
                        <option>Media Studies</option>
                        <option>Music</option>
                        <option>Nursing</option>
                        <option>Philosophy</option>
                        <option>Physics</option>
                        <option>Political Science</option>
                        <option>Psychology</option>
                        <option>Public Health</option>
                        <option>Recreation and Leisure</option>
                        <option>Secondary Education</option>
                        <option>Social Work</option>
                        <option>Sociology</option>
                        <option>Spanish</option>
                        <option>Special Education/Elementary Ed</option>
                        <option>Theatre</option>
                        </select>
                        </td>";
        echo "<td>" . "<input type=submit name=add value=add" . " </td>";
        
        
        
        
        echo "</form>";
        echo"</table>";
        
        
        
        
        mysql_close($connect);
        
    ?>
       
    </body>
</html>

Edited by fenway, 28 April 2013 - 03:57 PM.
code tags


#16 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,193 posts
  • LocationToronto, ON

Posted 28 April 2013 - 03:57 PM

Don't post the entire code; you've been asked to show errors as well as table structure, but you're provided neither.


Edited by fenway, 28 April 2013 - 03:58 PM.

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#17 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 28 April 2013 - 04:25 PM

When you update the user table, DON'T update the userid field - that should never change.

 

Similarly, when you add a new user you let the userid auto_increment and not include it in query (as previously stated in my last post). You should then call mysql_insert_id() to get the newly allocated id and use that when inserting to the other table.

 

At the moment you add a new user then add to the other tables depending on user type. Why do you then loop through all the users and then attempt to insert again into the other two table for every student?


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#18 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 April 2013 - 04:49 PM

Sorry for the previous post, I'm not sure how to obtain the table information for all three tables using code since I did not make the tables in php code, but through the phpMyAdmin

 

I have been discussing the issues with my group members and we came to the conclusion to disable the user_id field on the "Add" function, but hadn't thought about the "Update" function.

 

 

I did re-enter more USER inputs into the form with the following error script line:

"if (!mysql_query($uuser_type)){

              die('Error: ' . mysql_error($connect));"
 
and got the following response:
Error: Duplicate entry '0' for key 'PRIMARY'

 

I thought by having the  " if($_POST['uuserType'] === "Student"){"  statement  inside the "Add" function, it would only apply to the single addition of one user's info, not all prior entries...

 

 

Thanks for the repeatedly helpful responses, I will now try the  mysql_insert_id()  to INSERT INTO the new table.



#19 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 28 April 2013 - 04:57 PM

In PHPMyAdmin you just execute the query

 

SHOW CREATE TABLE user;

 

etc.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#20 k2sno311

k2sno311

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 April 2013 - 07:45 PM

Table Create Table user CREATE TABLE `user` (
 `user_id` int(8) NOT NULL AUTO_INCREMENT,
 `user_name` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `user_type` varchar(7) COLLATE utf8_unicode_ci NOT NULL COMMENT '"student" or "faculty"',
 `last_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `first_name` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `committee_id` int(4) NOT NULL,
 `degree_program` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`user_id`),
 UNIQUE KEY `email` (`email`),
 UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

 

Table

Create Table faculty member CREATE TABLE `faculty member` (
 `faculty_id` int(8) NOT NULL,
 `user_id` int(8) NOT NULL,
 `office` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`faculty_id`),
 UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 

 

 

Table Create Table honor student CREATE TABLE `honor student` (
 `student_id` int(8) NOT NULL,
 `user_id` int(8) NOT NULL,
 `orientation_date` date NOT NULL,
 PRIMARY KEY (`student_id`),
 UNIQUE KEY `user_id` (`user_id`),
 UNIQUE KEY `user_id_2` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com