php - MySQL的创建表与静态和动态混合列

我在使用foreach结果的动态列创建表时遇到问题。

准备列上的代码：

$fields = $wpdb->get_results("SELECT goal_id FROM wpgetyp_goalsettings WHERE goaltype IN('daily','weekly') AND user_id='$current_user->ID'");
foreach ($fields as $field) {
    $cfield = "field_$field->goal_id int(5) NOT NULL,";
}

结果：

field_1 int(5) NOT NULL,field_2 int(5) NOT NULL,field_3 int(5) NOT NULL,field_4 int(5) NOT NULL,field_5 int(5) NOT NULL,

创建表的代码：

$table_name = $wpdb->prefix.'user_'.$current_user->ID;
if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
     //table not in database. Create new table
     $charset_collate = $wpdb->get_charset_collate();

     $sql = "CREATE TABLE $table_name (
          id int(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          user_id varchar(15) NOT NULL,
          goals_type varchar(10) NOT NULL,
          goals_date date NOT NULL,
          start_time time NOT NULL,
          end_time time NOT NULL,
          $cfield
          lead_type varchar(50) NOT NULL,
          entry_date timestamp
     ) $charset_collate;";
     require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
     dbDelta( $sql );
}
else{
}

注意$cfield。创建表时，它将仅写入最后一个结果。在创建表期间仅包括field_5。

最佳答案

您正在用新值覆盖每个结果。相反，您需要连接所有这些值：

$fields = $wpdb->get_results("SELECT goal_id FROM wpgetyp_goalsettings WHERE goaltype IN('daily','weekly') AND user_id='$current_user->ID'");
$cfield = "";

foreach ($fields as $field) {
    // Long way of string concatenation
    $cfield = $cfield . "field_$field->goal_id int(5) NOT NULL, ";
    // Short way, mentioned by @FrankerZ in the comments (chose one)
    // $cfield .= "field_$field->goal_id int(5) NOT NULL, ";
}

关于php - MySQL的创建表与静态和动态混合列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48408759/