Check if column exists, if not, add in MySQL through PHP

Question

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.

^ if that's the case, all the more reason to ask why there's no change. — Dave Chen
– Dave Chen, Commented May 7, 2013 at 11:51
FOR THE LOVE OF /dev/null ... dont use the PHP mysql legacy api — Terence
– Terence, Commented May 7, 2013 at 11:51
Using OR in this place is utter nonsense. A query that does not return any records is not a failed query. — C3roe
– C3roe, Commented May 7, 2013 at 12:03
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.
– N.B., Commented May 7, 2013 at 12:09

Terence · Accepted Answer · 2013-05-07 12:33:58Z

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();

edited May 7, 2013 at 12:33

answered May 7, 2013 at 11:57

Terence

11.2k1 gold badge17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Narek Over a year ago

Are you sure? Doesn't bind_param make this query like ALTER IGNORE TABLE "tableName" ADD "fieldName" VARCHAR(20);?

Terence Over a year ago

Why worry about the case sensitivity of the table-/field-name?

Narek Over a year ago

No no, I mean quotes ALTER IGNORE TABLE "tableName" AD... instead of ALTER IGNORE TABLE tableName AD...

Terence Over a year ago

I understand the confusion, going to fix it.

Mavelo Over a year ago

IGNORE only applies to keys though... dev.mysql.com/doc/refman/5.1/en/… That said, qisho's solution is the one i'll be upvoting

qisho · Accepted Answer · 2013-05-07 12:23:28Z

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

edited May 7, 2013 at 12:23

answered May 7, 2013 at 12:00

qisho

3642 silver badges7 bronze badges

Comments

medicalbird · Accepted Answer · 2019-10-17 13:48:10Z

0

For msqli users

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

answered Oct 17, 2019 at 13:48

medicalbird

971 silver badge7 bronze badges

Collectives™ on Stack Overflow

Check if column exists, if not, add in MySQL through PHP

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related