哆啦的时光机
也许这可以帮助您理解我通过在循环外部创建和绑定准备好的语句所暗示的含义。<?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 ) ); ?>在不知道表模式的情况下,我无法确定以下内容是否正确,但根据显示的表设计,这似乎工作正常。<?php require 'db.php'; $user_id=!empty( $_GET['user_id'] ) ? $_GET['user_id'] : false; ?><!DOCTYPE html><html> <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>