Add new date column to table

blackbox

Hi all,

I am working on a script and I need to add a new column to a table. I have this working apart from when I try and add a date column.

This is what I have:


$column = "SeqID".$_POST['SeqNo'];
$comments = "Comments".$_POST['TableID'];
$fixdate = "SeqID".$_POST['SeqNo']."RD";
$add = mysql_query("ALTER TABLE ".$_SESSION['hotel']."_Audit ADD ".$column." VARCHAR( 5 ), ADD ".$comments." TEXT, ADD ".$fixdate." DATE");

Can anyone see where I am going wrong.

Many thanks in advance for your time.

Cheers

laserlight

Wait. Before we tackle the nitty gritty of what you're facing, let's do a recap:

Why are you using the legacy MySQL extension?
Why do you append $POST['SeqNo'] and $POST['TableID']? It looks suspiciously like numbered columns. Perhaps you should be relooking your database design, e.g., another table or two (for a join table) with rows to be added might be more appropriate than altering the $_SESSION['hotel'] table.

blackbox

Hi laserlight,
Thanks for your reply.

"Why are you using the legacy MySQL extension?"
At the moment I do not have the php extenions for using MySQLi loaded on the server. This is because I am told that I can't have the legacy "MySQL" and the new "MySQLi" active on the server at the same time.
Because I have a number of applications still using "MySQL" I am worried that if I install the extenions for "MySQLi" the legacy application will not run. I am right?

"Why do you append $POST['SeqNo'] and $POST['TableID']? It looks suspiciously like numbered columns. Perhaps you should be relooking your database design, e.g., another table or two (for a join table) with rows to be added"
The columns are a mix, for example, I have a column named "Commentsxx" the xx is a record group identifier which I use when performing lookups. It may not be the best way but I am working on that.

Many thanks.

laserlight

blackbox wrote:
At the moment I do not have the php extenions for using MySQLi loaded on the server. This is because I am told that I can't have the legacy "MySQL" and the new "MySQLi" active on the server at the same time.
Because I have a number of applications still using "MySQL" I am worried that if I install the extenions for "MySQLi" the legacy application will not run. I am right?

You should be able to have both extensions active on the server at the same time: they are after all accessed differently in their API, even for wrappers like PDO. So, while it might be true with respect to your hosting provider's policy, it appears to be a rather arbitrary restriction that makes migration more difficult.

blackbox wrote:
The columns are a mix, for example, I have a column named "Commentsxx" the xx is a record group identifier which I use when performing lookups. It may not be the best way but I am working on that.

Then you do indeed have numbered columns, and this makes your life more difficult and your database less efficient. If you would give us your database schema, perhaps we could help you find a better way.

NogDog

While I agree you're ultimately better off normalizing the DB design to eliminate the need for this, as a general pattern to help debug, I might do something like:

$sql = "ALTER TABLE ".$_SESSION['hotel']."_Audit ADD ".$column." VARCHAR( 5 ), ADD ".$comments." TEXT, ADD ".$fixdate." DATE";
$add = mysql_query($sql);
if($add == false) {
    // quick-and-dirty way to get debug info, not suitable for production
    die("<pre>Query failed: ".mysql_error()."\n$sql</pre>");
}