alter table problem mysql

bull

Verified User
Joined
Feb 3, 2005
Messages
20
i've tried to add a collumn to a table but it just
won't happen,van anybody tell me wy?
this is my script thingie



$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);
echo "success in database connection.";
// select the specific database name we want to access.
$dbname=$usnam."_".$dbname;
if (!mysql_select_db($dbname)) die(mysql_error());
echo "success in database selection.";
$result="ALTER TABLE cv ADD COLUMN nationaliteit char(15)";
if (mysql_query($result)){
echo 'de tabel is uitgebreid met een kolom';
} else {
echo 'de tabel is niet uitgebreid met een kolom genaamd nationaliteit';
}
?>:D
already saying thanks !
 
Problem solved script can only run once, the second time
i naturally earn a error because the column already exist ?!..

My script should first check if the column exist or not
if not only then I should use altertable,else
echo 'column already exists';

you might send me such a script ,or you might not


by the way zero replies is not really hopefull for my future
use of this forum or was my question so difficult for you??
 
You can either have it check for the column and if it exists skip creating it OR you can use a @ in front of the command which will suppress the error of creating it. I would recommend the first option.

If I get some time later today I'll try and get an example for you.
 
Something like this might work. I haven't actually tried it and it will probably require a little tweaking.

PHP:
<?php

mysql_connect($mysql_host, $mysql_user, $mysql_pass)
	or die(mysql_error());
mysql_select_db($mysql_db)
	or die(mysql_error());

$res = mysql_query("SELECT nationaliteit FROM cv");
if (!$res)
{
	mysql_query("ALTER TABLE cv ADD COLUMN nationaliteit char(15)");
}
// else
// {
//	die("Table already exists");
// }

?>
 
okay

Thanks for reply,

I wrote earlier somthing like this;


$result="ALTER TABLE perspag ADD datum VARCHAR(30) NOT NULL";

if (!mysql_query($result)) {
echo 'table already seems to exist';
}
elseif (mysql_query($result)){
echo 'table has a new column';
}
else{
echo 'something else went wrong';
}
?>


I'm not sure if the query actually looks for an
existing column or not, mayby could the query
be empty for another reason ?!,or returnng false
for a mysql error ,or do you think this is more than
effective ?!

I meen does it actually check the extistance of the
column ?!..
 
I don't think there is a mysql command to check for a column. Trying to select the column is the best way I can think of. The only reason the column could not be selected would be because:
1. Database connection failed (should have aborted already anyway)
2. It does not exist

(At least what I can think of right now)
 
In case it can be useful to someone, here's how I did it in a MySQL script
that can be run more than once without giving any errors if the column
already exist:

DO @var:=NULL;
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'pepe' INTO @var;
SELECT IF ((SELECT @var iS NOT NULL),'DO @nothing:=NULL;','ALTER TABLE pet ADD COLUMN pepe INT;') INTO @exec;
PREPARE exec FROM @exec;
EXECUTE exec;
DEALLOCATE PREPARE exec;

:)
 
Back
Top