alter table problem mysql

[bull]

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';
}
?>
already saying thanks !

 

[bull]

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

 

[jmstacey]

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.

 

[jmstacey]

Something like this might work. I haven't actually tried it and it will probably require a little tweaking.

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

?>

 

[bull]

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

 

[jmstacey]

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)

 

[SuperSerdo]

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;