Skip to content
Advertisement

MySql is not updating column

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>';

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement