1

I'm using the following code to check if the column exists, and if it doesn't, add it:

mysql_query("select $column from $table") or mysql_query("alter table $table add $column varchar (20)");

But there's no change in the database. Any suggestions why?

P.S. The database is connected.

Dezzie
  • 934
  • 3
  • 18
  • 35
  • 1
    ^ if that's the case, all the more reason to ask why there's no change. – Dave Chen May 07 '13 at 11:51
  • 8
    FOR THE LOVE OF /dev/null ... dont use the PHP mysql legacy api – Terence May 07 '13 at 11:51
  • Actually no, `mysql_query` returns false on error. – Dave Chen May 07 '13 at 11:53
  • so just be like `if (!mysql_query("select $column from $table")) mysql_query("alter table $table add $column varchar (20)");` – Dave Chen May 07 '13 at 11:54
  • Tried that too. No changes to the database. @DaveChen – Dezzie May 07 '13 at 11:56
  • What are the values of `$column` and `$table`? – Dave Chen May 07 '13 at 11:58
  • 3
    Using `OR` in this place is utter nonsense. A query that does not return any records is _not_ a failed query. – CBroe May 07 '13 at 12:03
  • 6
    It's surprising how in 1 line of code you managed to commit so many atrocities against programming, common sense and what not. I sincerely hope you're using that code for the purposes of learning / testing / having fun. – N.B. May 07 '13 at 12:09
  • Yea, I just started learning programming in PHP. Still getting there. This is indeed for learning purposes, thanks for the heads up. I didn't even know that PDO existed till now. – Dezzie May 07 '13 at 13:08

3 Answers3

6

You can change your SQL to...

//THIS IS BETTER BUT DONT USE THIS
$qry = "ALTER IGNORE TABLE {$table} ADD {$column} VARCHAR(20);"

Instead use PHP PDO or MySQLi with prepared statements. Instead of that legacy horror with concatinated unescaped strings.

MySQLi Solution:

$mysqli = new mysqli($cfg->host, $cfg->user, $cfg->password, $cfg->db);

if ($mysqli->connect_errno) {
    echo 'Connect failed: ', $mysqli->connect_error, '" }';
    exit();
}

if ($stmt = $mysqli->prepare("ALTER IGNORE TABLE ? ADD ? VARCHAR(20);")) {
    $stmt->bind_param("ss", $table, $column);
    $stmt->execute();
    $stmt->close();
}
$mysqli->close();
Terence
  • 10,533
  • 1
  • 14
  • 20
  • Are you sure? Doesn't `bind_param` make this query like `ALTER IGNORE TABLE "tableName" ADD "fieldName" VARCHAR(20);`? – Narek May 07 '13 at 12:20
  • Why worry about the case sensitivity of the table-/field-name? – Terence May 07 '13 at 12:26
  • No no, I mean quotes `ALTER IGNORE TABLE "tableName" AD...` instead of `ALTER IGNORE TABLE tableName AD...` – Narek May 07 '13 at 12:28
  • I understand the confusion, going to fix it. – Terence May 07 '13 at 12:29
  • `IGNORE` only applies to keys though... http://dev.mysql.com/doc/refman/5.1/en/alter-table.html#idm47658597150496 That said, qisho's solution is the one i'll be upvoting – Mavelo Dec 18 '13 at 19:22
3
$r=mysql_num_rows(mysql_query("SHOW columns from '".$table."' where field='".$column."'"));
if ($r==0){
    mysql_query("alter table $table add $column varchar (20)");
}
qisho
  • 364
  • 2
  • 7
0

For msqli users

$result = $mysqli->query("SHOW COLUMNS FROM tablename LIKE 'keyword");
$exists = (mysqli_num_rows($result))?TRUE:FALSE;
medicalbird
  • 97
  • 7