mikeabe Posted December 3, 2008 Share Posted December 3, 2008 The following code is from a php drop down menu: presently this code sends both the student id and student name to the Student column in Mysql: (this isn't the complete file, just the problem area) $query="SELECT snames, sid FROM studnames ORDER BY snames ASC"; $result = mysql_query ($query); echo "<select name=Student>"; while($nt=mysql_fetch_array($result)){ echo "<option value='$nt[sid] $nt[snames]'>$nt[snames]</option>"; } echo "</select>"; ?> Here's the action file: <?php $con = mysql_connect("localhost","root","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("DisciplineIncoming", $con); $sql="INSERT INTO incoming (Teacher, sid, Student, Date, Tardy, Comment, Absent, Inhouse, Suspension, Allpresent) VALUES ('$_POST[Teacher]','$_POST[sid]','$_POST[student]','$_POST[Date]','$_POST[Tardy]','$_POST[Comment]','$_POST[Absent]','$_POST[inhouse]','$_POST[suspension]','$_POST[Allpresent]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } mysql_close($con) ?> Both the student id and student name are going to the Student column in Mysql What I'd like to happen is to separate the id from the student name, having the id go to the column sid and the student name go to the column Student Is there any way to do that? Thanks, Mike Link to comment https://forums.phpfreaks.com/topic/135382-solved-separate-id-from-student-name-in-mysql/ Share on other sites More sharing options...
rhodesa Posted December 3, 2008 Share Posted December 3, 2008 you could split on the first space...but if you have a table of Students already, you should only need the 'sid' field in the 'incoming' table. you can always JOIN the tables to get the student name from the 'sid' Link to comment https://forums.phpfreaks.com/topic/135382-solved-separate-id-from-student-name-in-mysql/#findComment-705157 Share on other sites More sharing options...
mikeabe Posted December 3, 2008 Author Share Posted December 3, 2008 Thanks for the response. I'll try that and let you know how it goes. Thanks, Mike Link to comment https://forums.phpfreaks.com/topic/135382-solved-separate-id-from-student-name-in-mysql/#findComment-705231 Share on other sites More sharing options...
mikeabe Posted December 3, 2008 Author Share Posted December 3, 2008 I do have an 'sid' field in the 'incoming' table. Rhodesa, I'm not exactly sure what you mean by "split on the first space" Split sounds good. I'm trying split the student id's from the student names in the Mysql 'Student' field. Thanks, Mike Link to comment https://forums.phpfreaks.com/topic/135382-solved-separate-id-from-student-name-in-mysql/#findComment-705316 Share on other sites More sharing options...
rhodesa Posted December 3, 2008 Share Posted December 3, 2008 hum...let's back up a little. so you have a form right? and it has a select list with all the students? and when it is submitted, it does that INSERT? and in your table called incoming you have a column called sid for the ID of the student, and a column called Student that holds the name of the student? what i am saying, is do away with the Student column in the incoming table. you can get the name of the student by joining the table with the studnames table. after removing that column, your form would look like: $query="SELECT snames, sid FROM studnames ORDER BY snames ASC"; $result = mysql_query ($query); echo "<select name=sid>"; while($nt=mysql_fetch_array($result)){ echo "<option value='$nt[sid]'>$nt[snames]</option>"; } echo "</select>"; ?> and your INSERT code would be: $sql="INSERT INTO incoming (Teacher, sid, Date, Tardy, Comment, Absent, Inhouse, Suspension, Allpresent) VALUES ('$_POST[Teacher]','$_POST[sid]','$_POST[Date]','$_POST[Tardy]','$_POST[Comment]','$_POST[Absent]','$_POST[inhouse]','$_POST[suspension]','$_POST[Allpresent]')"; p.s. - on another note, to avoid SQL Injection, all your POST values should be passed through the mysql_real_escape_string() function before being used in the INSERT command Link to comment https://forums.phpfreaks.com/topic/135382-solved-separate-id-from-student-name-in-mysql/#findComment-705326 Share on other sites More sharing options...
mikeabe Posted December 3, 2008 Author Share Posted December 3, 2008 That worked GREAT!! I really appreciate you helping me out and taking the time to fix up my code. Thanks, Mike Link to comment https://forums.phpfreaks.com/topic/135382-solved-separate-id-from-student-name-in-mysql/#findComment-705427 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.