debz89uk Posted March 22, 2010 Share Posted March 22, 2010 I have a table that looks like this : created by the code : <?php $sql = mysql_query("SELECT * FROM ProgrammingFoundations"); $i = 0; while($row = mysql_fetch_array($sql)) { ?> <table align="center" border="1"> <tr> <form method="POST" action="ProgrammingFoundationsAdd.php"> <td width="100"><input type="text" name="data[<?php echo $i; ?>][student_id]" value=<?php echo $row['student_id'];?> size="10"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_1]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_2]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_3]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_4]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_5]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_6]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_7]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_8]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_9]" size="1"></td> <td width="30"><input type="checkbox" name="data[<?php echo $i; ?>][lecture_10]" size="1"></td> </tr> </table> <?php $i++; ?> When I fill out the form I get (using the print_r($_POST); ) something along the lines of : Array ( [data] => Array ( [0] => Array ( [student_id] => 200727669 [lecture_1] => on [lecture_2] => on [lecture_3] => on [lecture_4] => on [lecture_5] => on ) [1] => Array ( [student_id] => 200727668 [lecture_1] => on [lecture_2] => on [lecture_3] => on [lecture_4] => on [lecture_5] => on [lecture_6] => on [lecture_7] => on [lecture_8] => on [lecture_9] => on [lecture_10] => on ) [2] => Array ( [student_id] => 200712312 [lecture_2] => on [lecture_3] => on [lecture_4] => on [lecture_6] => on [lecture_7] => on ) [3] => Array ( [student_id] => 200755922 [lecture_1] => on [lecture_2] => on [lecture_3] => on [lecture_4] => on [lecture_8] => on ) [4] => Array ( [student_id] => 200812345 [lecture_3] => on [lecture_4] => on [lecture_5] => on [lecture_7] => on [lecture_8] => on [lecture_9] => on [lecture_10] => on ) [5] => Array ( [student_id] => 200828765 [lecture_1] => on [lecture_2] => on [lecture_3] => on [lecture_4] => on [lecture_5] => on [lecture_6] => on [lecture_7] => on [lecture_8] => on [lecture_9] => on ) [6] => Array ( [student_id] => 200812345 [lecture_3] => on [lecture_4] => on [lecture_5] => on [lecture_6] => on [lecture_7] => on [lecture_8] => on [lecture_9] => on [lecture_10] => on ) ) ) (Depending on what boxes I tick of course). How do I go about putting this information into my database? Quote Link to comment Share on other sites More sharing options...
schilly Posted March 22, 2010 Share Posted March 22, 2010 foreach($_POST['data'] as $student){ $sid = $student['student_id']; foreach($student as $key => $value){ if(sub_str($key,0,strlent($key)-2) == 'lecture' && $value == 'on') $set_clause .= "$key = $value,"; } $set_clause = sub_str($set_clause, 0, strlen($set_clause)-1); $sql = "UPDATE students SET $set_clause WHERE student_id = $sid"; } something like that should do the trick. quick write up. untested. echo $sql to make sure it looks right for your table. Quote Link to comment Share on other sites More sharing options...
debz89uk Posted March 23, 2010 Author Share Posted March 23, 2010 I cant seem to get it to work, I get : "Fatal error: Call to undefined function sub_str() in /home/student/cs2007/dwright/DEVWEB/2009/WAE/Project/11th/ProgrammingFoundationsAdd.php on line 33" Quote Link to comment Share on other sites More sharing options...
schilly Posted March 23, 2010 Share Posted March 23, 2010 ooops. sorry. it's substr() not sub_str(). Quote Link to comment Share on other sites More sharing options...
debz89uk Posted March 23, 2010 Author Share Posted March 23, 2010 so I have : foreach($_POST['data'] as $student){ $sid = $student['student_id']; foreach($student as $key => $value){ if(substr($key,0,strlen($key)-2) == 'lecture' && $value == 'on') $set_clause .= "$key = $value,"; } $set_clause = substr($set_clause, 0, strlen($set_clause)-1); $con = mysql_connect("devweb2009.cis.strath.ac.uk","dwright","*******"); if (!$con){ die ('Could not connect: '. mysql_error()); } mysql_select_db("dwright", $con); $sql = "UPDATE ProgrammingFoundations SET $set_clause WHERE student_id = $sid"; mysql_query($sql); } which produces no errors but puts nothing into my database? Quote Link to comment Share on other sites More sharing options...
schilly Posted March 23, 2010 Share Posted March 23, 2010 couple adjustments. need to reset the set clause each time and add an error check on the query. foreach($_POST['data'] as $student){ $sid = $student['student_id']; $set_clause = ""; foreach($student as $key => $value){ if(substr($key,0,strlen($key)-2) == 'lecture' && $value == 'on') $set_clause .= "$key = $value,"; } $set_clause = substr($set_clause, 0, strlen($set_clause)-1); $con = mysql_connect("devweb2009.cis.strath.ac.uk","dwright","*******"); if (!$con){ die ('Could not connect: '. mysql_error()); } mysql_select_db("dwright", $con); $sql = "UPDATE ProgrammingFoundations SET $set_clause WHERE student_id = $sid"; mysql_query($sql) or die(mysql_error() . " - $sql"); } post any error info from mysql. Quote Link to comment Share on other sites More sharing options...
debz89uk Posted March 23, 2010 Author Share Posted March 23, 2010 I got this : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on WHERE student_id = 200727669' at line 1 - UPDATE ProgrammingFoundations SET lecture_1 = on WHERE student_id = 200727669 so this must mean i need to have quotations around "on" and "200727669" is that correct? I cant seem to add them in though without taking away $value Quote Link to comment Share on other sites More sharing options...
debz89uk Posted March 23, 2010 Author Share Posted March 23, 2010 Nevermind, solved the quotations problem, unfortunately its not working in the database, it just seems to put inserting random fields with "on" and not the ones I've selected? Quote Link to comment Share on other sites More sharing options...
schilly Posted March 23, 2010 Share Posted March 23, 2010 is the field named "lecture_1"? Quote Link to comment Share on other sites More sharing options...
debz89uk Posted March 23, 2010 Author Share Posted March 23, 2010 Yes, what it seems to be doing is only entering the last field ticked for each student_id? So it must be getting overidden somewhere. E.g. If I do this : It produces this result in my database table: Quote Link to comment Share on other sites More sharing options...
schilly Posted March 23, 2010 Share Posted March 23, 2010 weird. put echo $sql; before the mysql_query() call and look at the actual query. Quote Link to comment Share on other sites More sharing options...
debz89uk Posted March 23, 2010 Author Share Posted March 23, 2010 Got it, was just a matter of moving the curly bracket from before the set clause, to the very end of the code. Thank you VERY much for your help Quote Link to comment Share on other sites More sharing options...
schilly Posted March 24, 2010 Share Posted March 24, 2010 np Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.