I want to add the value of a checkbox to the specific row in the database. It adds the value to the wrong rows each time. I tried adding a hidden input with a different value.
if (isset($_POST['save_task'])) {
$count=count($_POST["pro_id"]);
$query = "INSERT INTO client_program_task_activities (task_id, activity_type, type_id, show_status)
VALUES(?,?,?,?)";
$stmt = mysqli_prepare($db, $query);
mysqli_stmt_bind_param($stmt, 'ssss', $_POST['task_id'][$i], $_POST['type'][$i], $_POST['pro_id'][$i], $_POST['show_status'][$i]);
for($i=0;$i<$count;$i++){
mysqli_stmt_execute($stmt);
}
}
$sql = "SELECT * FROM tasks WHERE user_id='$user_id' ORDER BY date_added DESC ";
$result = mysqli_query($db, $sql);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
echo ' <form action="task_day.php" method="post" >
<table >
<tr>
<th> ADD TASK</th>
<th> TASK NAME</th>
<th>KCAL</th>
</tr>';
while ($row = mysqli_fetch_assoc($result)) {
echo '<input type="hidden" name="task_id[]" value="'; echo $task_id; echo '">';
echo '<input type="hidden" name="pro_id[]" value="'.$row['pro_id']. '">';
echo '<input type="hidden" name="type[]" value="test">';
echo '<input name=show_status[] type="hidden" value="0">
<td><input name=show_status[] type="checkbox" value="1"></td>
<td>'.$row['task_name'].'</td>
</tr>';
}
echo ' </table>
<button name="save_task" type="submit" >Save task</button>
</form>';
}
?>
Advertisement
Answer
perhaps this might help you understand what I was implying by creating and binding the prepared statements outside the loop.
<?php
# test that ALL fields are present before trying to
# assign them to variables or process them further.
if( isset(
$_POST['save_task'],
$_POST['task_id'],
$_POST['type'],
$_POST['pro_id'],
$_POST['show_status']
)) {
# prepare the statement ONCE - and bind placeholders to variables.
# in `mysqli` these variables do not need to exist at this point
# whereas in PDO they do.
$sql='insert into `client_program_task_activities` ( `task_id`, `activity_type`, `type_id`, `show_status`) values ( ?, ?, ?, ? )';
$stmt=$db->prepare( $sql );
$stmt->bind_param( 'ssss', $tid, $type, $pid, $status );
foreach( $_POST['pro_id'] as $i => $pid ){
$tid=$_POST['task_id'][$i];
$type=$_POST['type'][$i];
$status=$_POST['show_status'][$i];
$stmt->execute();
}
$stmt->close();
}
# query to find data for table display
$sql = 'select `task_id`, `task_name`, `pro_id` from `tasks` where `user_id`=? order by `date_added` desc';
$stmt=$db->prepare( $sql );
$stmt->bind_param( 's', $user_id );
$stmt->execute();
$stmt->bind_result( $tid, $task, $pid );
# prepare output for table rows
$rows=[];
while( $stmt->fetch() ){
$rows[]=sprintf(
'<tr>
<td>
<input type="hidden" name="task_id[]" value="%s" />
<input type="hidden" name="pro_id[]" value="%s" />
<input type="hidden" name="type[]" value="test" />
<input name="show_status[]" type="hidden" value="0" />
<input name="show_status[]" type="checkbox" value="1" />
</td>
<td>%s</td>
<td>### ENERGY ###</td>
</tr>',
$tid,
$pid,
$task
);
}
$stmt->free_result();
$stmt->close();
# print the formatted table
printf('
<form action="task_day.php" method="post">
<table>
<tr>
<th>ADD TASK</th>
<th>TASK NAME</th>
<th>KCAL</th>
</tr>
%s
</table>
<button name="save_task" type="submit" >Save task</button>
</form>',
implode( PHP_EOL, $rows )
);
?>
Without knowing the table schema I could not be sure if the following is correct but based upon the table design shown this appears to work OK.
<?php
require 'db.php';
$user_id=!empty( $_GET['user_id'] ) ? $_GET['user_id'] : false;
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8' />
<title></title>
</head>
<body>
<?php
/*
based upon following rudimentary table schema
mysql> describe tasks;
+------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| task_id | int(10) unsigned | NO | MUL | 0 | |
| task_name | varchar(50) | NO | | 0 | |
| pro_id | int(10) unsigned | NO | MUL | 0 | |
| user_id | varchar(50) | NO | MUL | 0 | |
| date_added | timestamp | YES | | CURRENT_TIMESTAMP | |
+------------+------------------+------+-----+-------------------+----------------+
mysql> describe client_program_task_activities;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| task_id | int(10) unsigned | NO | | NULL | |
| activity_type | varchar(50) | NO | | NULL | |
| type_id | int(10) unsigned | NO | | 0 | |
| show_status | tinyint(3) unsigned | NO | | 0 | |
+---------------+---------------------+------+-----+---------+----------------+
*/
# test that ALL fields are present before trying to
# assign them to variables or process them further.
if( isset(
$user_id,
$_POST['save_task'],
$_POST['task_id'],
$_POST['type'],
$_POST['pro_id'],
$_POST['show_status']
) && !empty( $user_id ) ) {
# prepare the statement ONCE - and bind placeholders to variables.
# in `mysqli` these variables do not need to exist at this point
# whereas in PDO they do.
try{
$sql='insert into `client_program_task_activities` ( `task_id`, `activity_type`, `type_id`, `show_status`) values ( ?, ?, ?, ? )';
$stmt=$db->prepare( $sql );
$stmt->bind_param( 'ssss', $tid, $type, $pid, $status );
foreach( $_POST['task_id'] as $key => $tid ){
$status = in_array( $tid, array_keys( $_POST['show_status'] ) ) ? 1 : 0;
$pid=$_POST['pro_id'][ $key ];
$type=$_POST['type'][ $key ];
$stmt->execute();
}
$stmt->close();
}catch( mysqli_sql_exception $e ){
echo $e->getMessage(); # Do NOT display errors in final, production code!!!
#printf('<pre>%s</pre>',print_r( $_POST, true ) );
}
}
# query to find data for table display
try{
$sql = 'select `task_id`, `task_name`, `pro_id` from `tasks` where `user_id`=? order by `date_added` desc';
$stmt=$db->prepare( $sql );
$stmt->bind_param( 's', $user_id );
$stmt->execute();
$stmt->bind_result( $tid, $task, $pid );
}catch( mysqli_sql_exception $e ){
echo $e->getMessage(); # Do NOT display errors in final, production code!!!
}
# prepare output for table rows
/*
Removed hidden field `show_status` and assigned
the task ID as the index to `show_status`
*/
$rows=[];
while( $stmt->fetch() ){
$rows[]=sprintf(
'<tr>
<td>
<input type="hidden" name="task_id[]" value="%1$d" />
<input type="hidden" name="pro_id[]" value="%2$d" />
<input type="hidden" name="type[]" value="%3$s" /><!-- using task name as value rather than "test" -->
<input name="show_status[%1$d]" type="checkbox" value="1" /><!-- using task ID as index -->
</td>
<td>%3$s</td>
<td>### ENERGY ###</td><!-- unclear what this content will be ~ assumed energy due to column name KCAL -->
</tr>',
$tid,
$pid,
$task
);
}
$stmt->free_result();
$stmt->close();
# print the formatted table
# action="task_day.php"
printf('
<form method="post">
<table>
<tr>
<th>ADD TASK</th>
<th>TASK NAME</th>
<th>KCAL</th>
</tr>
%s
</table>
<button name="save_task" type="submit">Save task</button>
</form>',
implode( PHP_EOL, $rows )
);
?>
</body>
</html>