MySql is not updating column

Question

I am trying to add two columns into a database when a script is run. The problem is when the second column gets created it is of type int when I really want varchar2.

My query:

$query1 = "ALTER TABLE `keyword_table` ADD `".$currentWeek."` INT NOT NULL AFTER `".$previousWeek."_url`";
$query2 = "ALTER TABLE `keyword_table` ADD `".$currentWeek."_url` VARCHAR2(100) NOT NULL AFTER `".$currentWeek."`"

The query when the variables are put in:

ALTER TABLE `keyword_table` ADD `07_07_2015` INT NOT NULL AFTER `01_07_2015_url`
ALTER TABLE `keyword_table` ADD `07_07_2015_url` VARCHAR2(100) NOT NULL AFTER `07_07_2015`

These queries are how I wanted them however when the second query runs it creates the column where I want but as an INT not a VARCHAR2. I checked the syntax, I went on PhpMyAdmin and looked at the syntax it uses when inserting the column and it is the same as above.

Any ideas why it isn't making the column the correct type?

Edits

I ran the code like this:

$query = "ALTER TABLE `keyword_table` ADD `".$currentWeek."` INT NOT NULL AFTER `".$previousWeek."_url`";
mysqli_query($conn, $query);
echo $query.'<br>';
$query = "ALTER TABLE `keyword_table` ADD `".$currentWeek."_url` VARCHAR2(100) NOT NULL AFTER `".$currentWeek."`";
mysqli_query($conn, $query);
echo $query.'<br>';

So how did you run the above query? There could be problem with that. Better to add whole code... or do you want us to guess your code? — jimmypage
– jimmypage, Commented Sep 24, 2015 at 8:21
Added how I ran it, it creates the columns no problem however the second one gets created as an INT not a VARCHAR2 — MrHappySandwich
– MrHappySandwich, Commented Sep 24, 2015 at 8:25
No just php, sql and phpmyadmin, it is exactly how phpmyadmin would do it — MrHappySandwich
– MrHappySandwich, Commented Sep 24, 2015 at 8:30

Nimantha · Accepted Answer · 2021-10-29 16:07:01Z

2

VARCHAR2 is vendor specific for Oracle. If you're not using Oracle you should be using VARCHAR(100) instead.

For the most part there is little difference.

The difference is this...

VARCHAR(100) - the allocated memory is 100 regardless if there are only 50 characters.

VARCHAR2(100) - the allocated memory is only equal to the amount of characters input into the field.

edited Oct 29, 2021 at 16:07

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Sep 24, 2015 at 8:30

Kuya

7,3204 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrHappySandwich Over a year ago

Thank you, I always assumed that you have to use Varchar2 because Varchar was going to become something else, anyway again thank you!!

MaK · Accepted Answer · 2015-09-24 08:45:00Z

0

There's no datatype as VARCHAR2 in mysql. Please use VARCHAR instead.

For a list of datatype that mysql supports, please refer this: https://dev.mysql.com/doc/refman/5.0/en/data-types.html

answered Sep 24, 2015 at 8:45

MaK

1104 bronze badges

Collectives™ on Stack Overflow

MySql is not updating column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related