Adding Table Column

[Ken2k7]

Say I have this:

 

$query = "CREATE TABLE test(id mediumint(, name varchar(255), date varchar(20))";

mysql_query($query) or die(mysql_error());

 

So that table's created already. But I want to write a php script to change the already created table to this:

 

$query = "CREATE TABLE test(id mediumint(, name varchar(255), password varchar(255), date varchar(20))";

mysql_query($query) or die(mysql_error());

 

How would I do this without having to drop the table and then re-write the entire table again?

[phpSensei]

ADD COLUMN

 

mysql_query("ALTER table_name ADD COLUMN new_column Varchar(225)");

 

MODIFY COLUMN

 

mysql_query("ALTER table_name MODIFY COLUMN new_column Varchar(227)");

 

 

http://php.about.com/od/mysqlcommands/g/sql_add_column.htm

[Ken2k7]

So the lines are pretty identical except the add and modify column part...

[revraz]

You can get all the parameters from the mysql page, just look for the ALTER function.

[Ken2k7]

You can get all the parameters from the mysql page, just look for the ALTER function.

What mysql page?

[revraz]

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

[Ken2k7]

Thank you.

 

Sorry to have to keep bumping this, but is there a way to check if a table row already exists before adding/removing it? Can this also check for a table column?

 

Examples:

- Remove table ROW `id` from table `member` ONLY if it exists

- Add table ROW `id` to table `member` ONLY if it DOESN'T exist

- Remove table COLUMN `gender` from table `member` ONLY if it exists

- Add table COLUMN `gender` to table `member` ONLY if it DOESN'T exists

 

Could someone give me examples on how I would do those 4 entries?

 

Thank you in advance,

Ken

[phpSensei]

Thank you.

 

Sorry to have to keep bumping this, but is there a way to check if a table row already exists before adding/removing it? Can this also check for a table column?

 

Examples:

- Remove table ROW `id` from table `member` ONLY if it exists

- Add table ROW `id` to table `member` ONLY if it DOESN'T exist

- Remove table COLUMN `gender` from table `member` ONLY if it exists

- Add table COLUMN `gender` to table `member` ONLY if it DOESN'T exists

 

Could someone give me examples on how I would do those 4 entries?

 

Thank you in advance,

Ken

 

 

mysql_query("UPDATE") or die(mysql_error());

 

will tell if you a row already exists.

[Ken2k7]

mysql_query("UPDATE") or die(mysql_error());

 

will tell if you a row already exists.

So that returns a boolean?

 

So I would have to do something like:

<?php

$exists = mysql_query("UPDATE") or die(mysql_error());

if ($exists){
$sql = "ALTER TABLE `member` DROP COLUMN `id`";
mysql_query($sql, $this->db_connect) or die(mysql_error());
}

?>

 

I was thinking of something along the lines of:

<?php

$sql = "ALTER TABLE `member` DROP COLUMN IF EXISTS `id`";
mysql_query($sql, $this->db_connect) or die(mysql_error());

?>

[Ken2k7]

*bump*

[PHP_PhREEEk]

There are specific MySQL commands like EXISTS, NOT EXISTS, and when used with IF can do much of what you are asking about. You can also do it 'long hand' using PHP hooks and checking num_rows or num_fields or whatever. The short answer is, there are many many ways of doing what you ask here... you need to absorb a bit more information is all. It appears you 'get it' to the point of where you're generating some code in your head, but you seem to be asking "will this work?" The answer is: test it and let us know! If you get an error and simply cannot figure out why the error is happening, then post the code with the error and we can help.

 

Just create a test database with test fields and values, and have at it. Part of learning is experimenting; you can't hurt anything.

 

Anyways, last minute holiday stuff going on here, so I gotta get moving along.

 

Good luck!

 

PhREEEk