如何添加新的列到 MYSQL 表？

我正在尝试使用 PHP 向 MYSQL 表中添加一个新列。我不确定如何更改表以便创建新列。在我的评估表中，我有:

assessmentid | q1 | q2 | q3 | q4 | q5

假设我有一个带有文本框的页面，我在文本框中键入 q6，然后按下一个按钮，这个表就会更新为:

assessmentid | q1 | q2 | q3 | q4 | q5 | q6

我的代码:

<?php
mysql_query("ALTER TABLE `assessment` ADD newq INT(1) NOT NULL AFTER `q10`");
?>
<form method="post" action="">
<input type="text" name="newq" size="20">
<input type="submit" name="submit" value="Submit">
281006 次浏览
小开 
 $table  = 'your table name';
$column = 'q6'
$add = mysql_query("ALTER TABLE $table ADD $column VARCHAR( 255 ) NOT NULL");

you can change VARCHAR( 255 ) NOT NULL into what ever datatype you want.

小开

Something like:

$db = mysqli_connect("localhost", "user", "password", "database");
$name = $db->mysqli_real_escape_string($name);
$query = 'ALTER TABLE assesment ADD ' . $name . ' TINYINT NOT NULL DEFAULT \'0\'';
if($db->query($query)) {
echo "It worked";
}

Haven't tested it but should work.

小开

Based on your comment it looks like your'e only adding the new column if: mysql_query("SELECT * FROM assessment"); returns false. That's probably not what you wanted. Try removing the '!' on front of $sql in the first 'if' statement. So your code will look like:

$sql=mysql_query("SELECT * FROM assessment");
if ($sql) {
mysql_query("ALTER TABLE assessment ADD q6 INT(1) NOT NULL AFTER q5");
echo 'Q6 created';
}else...
小开
最佳答案

your table:

q1 | q2 | q3 | q4 | q5

you can also do 

ALTER TABLE yourtable ADD q6 VARCHAR( 255 ) after q5
小开

You should look into normalizing your database to avoid creating columns at runtime.

Make 3 tables:

  1. assessment
  2. question
  3. assessment_question (columns assessmentId, questionId)

Put questions and assessments in their respective tables and link them together through assessment_question using foreign keys.

小开

  • You can add a new column at the end of your table

    ALTER TABLE assessment ADD q6 VARCHAR( 255 )

  • Add column to the begining of table

    ALTER TABLE assessment ADD q6 VARCHAR( 255 ) FIRST

  • Add column next to a specified column

    ALTER TABLE assessment ADD q6 VARCHAR( 255 ) after q5

and more options here

小开

for WORDPRESS:

global $wpdb;




$your_table  = $wpdb->prefix. 'My_Table_Name';
$your_column =                'My_Column_Name';


if (!in_array($your_column, $wpdb->get_col( "DESC " . $your_table, 0 ) )){  $result= $wpdb->query(
"ALTER     TABLE $your_table     ADD $your_column     VARCHAR(100)     CHARACTER SET utf8     NOT NULL     "  //you can add positioning phraze: "AFTER My_another_column"
);}
小开 
ALTER TABLE `stor` ADD `buy_price` INT(20) NOT NULL ;
小开

The problem with the ALTER TABLE in the PHP code is in this line:

mysql_query("ALTER TABLE assessment ADD newq INT(1) NOT NULL AFTER q10");

It should be AFTER q5 since there is no q10 in your table sample. So, it becomes ALTER TABLE assessment ADD newq INT(1) NOT NULL AFTER q5;

Tried the same logic in my own table in [Skyvia] and it should work as seen below. I added the description column after the To column in the Title table.

[Skyvia] - the link to https://skyvia.com/query/online-mysql-query-builder

[image alter-table-add-column]enter image description here


提交答案