How to add new column to MYSQL table?

Question

I am trying to add a new column to my MYSQL table using PHP. I am unsure how to alter my table so that the new column is created. In my assessment table I have:

assessmentid | q1 | q2 | q3 | q4 | q5

Say I have a page with a textbox and I type q6 in to the textbox and press a button then the table is updated 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">

RTLM: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html — Marc B, Apr 19 '13 at 21:13
Im unsure how to word my query, I have this and it does not work.. $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{ //from here just continue the page as usual! echo 'Q6 already exists!'; — Steven Trainor, Apr 19 '13 at 21:18
@StevenTrainor comments are not the best place for source code. If you are showing where you are having your problem it should be a part of the question. Could you edit your question to include the source? — Nick Freeman, Apr 19 '13 at 21:20
bad code. mysql_query will return boolean false on ANY failure, not just when you're trying to add a duplicate field. always check `mysql_error()` to see what went wrong. e.g. `$result = mysql_query($sql) or die(mysql_error());`. — Marc B, Apr 19 '13 at 21:21

Dima · Accepted Answer · 2016-04-24T17:02:25.070

260

your table:

q1 | q2 | q3 | q4 | q5

you can also do

ALTER TABLE yourtable ADD q6 VARCHAR( 255 ) after q5

edited Apr 24 '16 at 17:02

answered Apr 19 '13 at 21:28

Dima

8,586
4
28
57

2

Thanks, It worked with - mysql_query("ALTER TABLE `assessment` ADD `q6` INT(1) NOT NULL AFTER `q5`"); – Steven Trainor Apr 19 '13 at 21:33
How can i name the column whatever name i type into a textbox? – Steven Trainor Apr 19 '13 at 21:43
if you mean a simple html form textbox, you can do it by getting the post or get parameters on the form target page – Dima Apr 19 '13 at 21:47
How do i do that? I will amend my code above to what i have now and if you could tell me where im going wrong that would be great :) – Steven Trainor Apr 19 '13 at 21:49
1

the value of the text box should be in $_POST['newq'] after you submit – Dima Apr 19 '13 at 21:53
11

@StevenTrainor: Do **not** use the string in your text box as such in the SQL statement. You must make sure you escape it in order to avoid an SQL injection vulnerability. – Costi Ciudatu Feb 01 '14 at 21:56
3

It's 2015 and people are still trying to set themselves up for sql injection vulnerabilities—:facepalm: – CommandZ Jul 08 '15 at 21:34
I use this command in a loop on an array which contains the name of the column but just the first column is created, however the array is full. any idea what I'm doing wrong? I could have ask it as a new question but first wanted to check it out here. thanks a lot. – nooshinha Jul 14 '15 at 08:04
@dima instead of "the value of the text box should be in $_POST['newq'] after you submit " should suggest to use securty safe way to bind values, so data is escaped, else can get sql injection ... i know its not part of the question but should be part of a good answer https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – tgkprog Sep 06 '21 at 06:32
@steven-trainor and anyone else do see https://www.php.net/manual/en/security.database.sql-injection.php – tgkprog Sep 06 '21 at 06:34

score 10 · Answer 2 · answered Apr 19 '13 at 21:24

10

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

answered Apr 19 '13 at 21:24

Abdullah Salma

560
7
20

Got it thanks, how can i name the column whatever name i type into a textbox? – Steven Trainor Apr 19 '13 at 21:45
1

@StevenTrainor what do you mean by `textbox`? if you mean an `input` what `type='text'` write `$column = $_POST['textbox'];` – Abdullah Salma Apr 19 '13 at 21:47
@StevenTrainor first you need to name your input `name='textbox'` or change textbox in `$column = $_POST['textbox'];` to the name of the input... – Abdullah Salma Apr 19 '13 at 21:49

amarnath · Answer 3 · 2015-05-06T06:49:56.923

9

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

edited May 06 '15 at 06:49

answered May 06 '15 at 06:44

amarnath

785
3
19
23

score 4 · Answer 4 · answered Apr 19 '13 at 21:25

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

Haven't tested it but should work.

answered Apr 19 '13 at 21:25

Glitch Desire

14,632
7
43
55

Thanks - How can i name the column whatever name i type into a textbox? – Steven Trainor Apr 19 '13 at 21:45
Replace my `$name` assignment with: `$name = $db->mysqli_real_escape_string($_GET['input']);` assuming you submit your form normally. If it's ajax it's a little more complex. – Glitch Desire Apr 19 '13 at 21:48

score 2 · Answer 5 · answered Jan 13 '15 at 16:16

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

Make 3 tables:

assessment
question
assessment_question (columns assessmentId, questionId)

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

score 1 · Answer 6 · answered Apr 19 '13 at 21:26

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

T.Todua · Answer 7 · 2015-07-20T16:00:02.123

1

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

edited Jul 20 '15 at 16:00

answered Jun 26 '15 at 08:52

T.Todua

53,146
19
236
237

score 1 · Answer 8 · answered May 11 '19 at 17:36

1

ALTER TABLE `stor` ADD `buy_price` INT(20) NOT NULL ;

answered May 11 '19 at 17:36

Shinwar ismail

299
2
9

score 0 · Answer 9 · answered Jan 19 '22 at 19:25

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]

How to add new column to MYSQL table?

9 Answers9

Linked

Related