[RESOLVED] Updating Database Table

[obrienkev]

I want to update the completed column in database table details{course_ID, number_ID, completed}

First, the user ticks any number of checkboxes in a form: (number_ID is got from query-checked and it works.)

echo '<input type="checkbox" name="completed[]" id="completed[]" value="'.$row['number_ID'].'" />';

Then the user hits submit and the code performs a query

// Declare variable
$completed = $_POST['completed'];

// Perform Query
$query = "update details
                     set completed = 'Yes'
                    where number_ID = '$completed'";

$result = mysql_query($query) or die(mysql_error());

My problem is that the query does not update the completed column.
If I omit the 'where' clause it works.

Why is this not working?

Thanks,

Kevin.

[AcousticJames]

Probably because after the form is submitted, the variable $completed is an array, and you're not referencing an array index in your SQL statement. Since you have a group of checkboxes, it's possible to have more than one selection. Which means when you manipulate the results of the form, the variable $completed may have more than one index.

Try something like:

$query = "update details set completed = 'Yes' where number_ID = '$completed[0]'";

The above will use the value in the first index of the array. If you want to present a user with several choices but force them to make only one choice, use a radio cluster instead. This way, you're not dealing with arrays on the other side of the form, you only have one value to deal with. When you have checkboxes, you can have multiple values, which you will have to reference after the form has been processed.

[obrienkev]

OK did this and it works fine...

for ($j=0;$j<sizeof($completed);$j++)
{
$complete=$completed[$j];

// Perform Query
$query = "update details
                     set completed = 'Yes'
                    where number_ID = '$complete'";

$result = mysql_query($query) or die(mysql_error());
}

However, it updates all rows in completed column.
So I guess I want my query to be this:

$query = "update details
                     set completed = 'Yes'
                    where number_ID = '$complete'
                 and course_ID = '$course'";

How do I ensure that I get the correct $course value?
What can I do to the checkbox??

echo '<input type="checkbox" name="completed[]" id="completed" value="'.$row['number_ID'].'" />';

Thanks,

Kevin.

[obrienkev]

Anyone Please?

[saraadmin]

You could either combine the number_ID and course_ID values into the <checkbox> elements' values using some sequence that won't appear in either IDs (e.g. '_' or ':') and then split them up again when the form is submitted.

Or you may be able to use <input type="hidden"> elements:

echo '<input type="checkbox" name="completed[]" id="completed" value="'.$row['number_ID'].'" />\n<input type="hidden" name="_' . $row['number_ID'] . '_course_ID" value="' . $row['course_ID'] . '" />';

and

$course_ID_var_name = "_" . $complete . "_course_ID";

$query = "update details 
                     set completed = 'Yes' 
                    where number_ID = '$complete' 
                 and course_ID = '" . $$course_ID_var_name . "'";

[obrienkev]

Did this and it works...

Checkbox...

echo '<input type="checkbox" name="completed['.$row['course_ID'].']" id="completed" value="'.$row['number_ID'].'" />';

Loop...

foreach($completed as $k => $v){
$query = "update details set completed = 'Yes' where number_ID = '$v' and course_ID = '$k'";

$result = mysql_query($query) or die(mysql_error());
print_r($_POST['completed']);
}

Only problem now is that the print statement displays the array twice. How do I fix this?

Thanks guys!!