Create two user_type tables from a user table

k2sno311 · April 24, 2013

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?

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',

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>

<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>

k2sno311 · April 24, 2013

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;

}

k2sno311 · April 25, 2013

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!

Barand · April 25, 2013

You can't use WHERE in an insert query.

The syntax is

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

k2sno311 · April 26, 2013

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);

}

Barand · April 27, 2013

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 ....

k2sno311 · April 28, 2013

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";

}

Barand · April 28, 2013

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

k2sno311 · April 28, 2013

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?

Barand · April 28, 2013

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 April 28, 2013 by Barand

k2sno311 · April 28, 2013

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";

}

Barand · April 28, 2013

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

Barand · April 28, 2013

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.

k2sno311 · April 28, 2013

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...

k2sno311 · April 28, 2013

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 April 28, 2013 by fenway
code tags

fenway · April 28, 2013

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

Edited April 28, 2013 by fenway

Barand · April 28, 2013

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?

k2sno311 · April 28, 2013

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.

Barand · April 28, 2013

In PHPMyAdmin you just execute the query

SHOW CREATE TABLE user;

etc.

k2sno311 · April 29, 2013

Table Create Table user CREATE TABLE `user` (

`user_id` int( 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( NOT NULL,

`user_id` int( 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( NOT NULL,

`user_id` int( 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

k2sno311 · April 29, 2013

those were in charts before I submitted and then changed to only text when I clicked "post"

sorry for that

Barand · April 29, 2013

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

k2sno311 · April 29, 2013

Wow, something that simple and yet I overlooked it a hundred times!!

Thanks so much for all of your help and continuous responses, Barand

Sign In

Create two user_type tables from a user table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information