
k2sno311
Members-
Posts
13 -
Joined
-
Last visited
k2sno311's Achievements

Newbie (1/5)
0
Reputation
-
Wow, something that simple and yet I overlooked it a hundred times!! Thanks so much for all of your help and continuous responses, Barand
-
those were in charts before I submitted and then changed to only text when I clicked "post" sorry for that
-
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
-
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.
-
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>
-
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...
-
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"; }
-
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?
-
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"; }
-
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); } }
-
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!
-
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; }
-
Hi, My name is Brent and I am a new student to Computer Science as of last year. I have a basic understanding of OOP, my first language was Java. I have taken one database course, using Microsoft Access. I am currently in a software development class using HTML, PHP and MySQL in Codeigniter/Agile setup. I enjoy it very much and am learning quite a bit every day! Thanks for your help so far on this forum! Looking forward to a summer internship and graduating as soon as possible to start my career Regards, Brent
-
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> <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>