How can I create a new column in a MYSQL table?

Problem

I’m trying to use PHP to add a new column to my MYSQL table. I’m not sure how to change my table to add the additional column. In my evaluation table, I have:

assessmentid | q1 | q2 | q3 | q4 | q5 

If I have a page with a textbox and write q6 into it and then hit a button, the table will be modified to:

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

My code:

<?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">

Asked by Steven Trainor

Solution #1

your table:

q1 | q2 | q3 | q4 | q5

you might also

ALTER TABLE yourtable ADD q6 VARCHAR( 255 ) after q5

Answered by Dima

Solution #2

 $table  = 'your table name';
 $column = 'q6'
 $add = mysql_query("ALTER TABLE $table ADD $column VARCHAR( 255 ) NOT NULL");

VARCHAR(255) NOT NULL can be changed to any datatype you choose.

Answered by Abdullah Salma

Solution #3

and there are more alternatives here

Answered by amarnath

Solution #4

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";
}

I haven’t tried it yet, but it should work.

Answered by Glitch Desire

Solution #5

According to your reply, you only add the new column if mysql query(“SELECT * FROM assessment”) returns false. That’s probably not what you were looking for. Remove the ‘!’ from the beginning of $sql in the first ‘if’ statement. As a result, your code will look like this:

$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...

Answered by Ryan Epp

Post is based on https://stackoverflow.com/questions/16113570/how-to-add-new-column-to-mysql-table