Jump to content

k2sno311

Members
  • Posts

    13
  • Joined

  • Last visited

k2sno311's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Wow, something that simple and yet I overlooked it a hundred times!! Thanks so much for all of your help and continuous responses, Barand
  2. those were in charts before I submitted and then changed to only text when I clicked "post" sorry for that
  3. 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
  4. 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.
  5. 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>
  6. 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...
  7. 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"; }
  8. 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?
  9. 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"; }
  10. 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); } }
  11. 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!
  12. 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; }
  13. 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
  14. 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>
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.