Array Help

[debz89uk]

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?

[schilly]

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.

[debz89uk]

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"

[schilly]

ooops. sorry. it's substr() not sub_str().

[debz89uk]

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?

[schilly]

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.

 

[debz89uk]

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

[debz89uk]

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?

[schilly]

is the field named "lecture_1"?

[debz89uk]

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:

 

 

 

 

[schilly]

weird. put echo $sql; before the mysql_query() call and look at the actual query.

[debz89uk]

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

[schilly]

np