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

php mysql check if column exists
mysql add column if not exists
mysql add column if not exists without stored procedure
mysql drop column if exists
mysql check if column exists in all tables
mysql select column if exists
check if key exists mysql
mysql alter table column if exists

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.

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

Check if column exist in Mysql table via php, 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  Many thanks to Mfoo who has put the really nice script for adding columns dynamically if not exists in the table. I have improved his answer with PHP. The script additionally helps you find how many tables actually needed 'Add column' mysql comand. Just taste the recipe. Works like charm.

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

Check if column exists, if not, add in MySQL through PHP, You were on the right track. Add one thing before your code: USE YourDb; -- You need to be in _your_ db to make `DATABASE()` work right SELECT COUNT(*)  MySQL Forums Forum List » Newbie. Advanced Search. New Topic. Select column if exists. Posted by: Mihsah Nahk Date: October 26, 2008 06:27AM Hi I wonder if it is

For msqli users

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

How to determine if a column already exists in a MySQL table before , In some situations, you want to check whether a column already exists in a table before adding it. However, there is no statement like ADD COLUMN IF NOT  The following link gives an example of this: Code to Check if a MySQL Table Exists Using a Variable for the Table Name. Actual PHP Output One of the tables that I have listed on my database is Files.

How to Add Columns To A Table Using MySQL ADD COLUMN, mysql check if column exists in all tables displaying mysql column names and values via php mysql add column if not exists php check if table exists mysqli This is a tutorial on how to add columns to a MySQL table using PHP. Please note that we will be using the PDO object in this tutorial (although the SQL queries will remain the same, regardless of whether you’re using PDO, mysql or mysqli).

Check if column exist in Mysql table via php, Find answers to Check if column exists in mysql table with php from the expert Ok, got it, it's probably not the best to do it, but it works, if you know better way,  I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. This the query I was attempting to use.

Check if column exists in mysql table with php Solutions, To understand whether a column exist or not, we have the following approaches − With the help of DESC command Using SHOW command  if not exists (select * from information_schema.columns where table_name = ‘test’ and column_name = ‘test_date’) begin alter table test add test_date datetime end Or you can do: Show columns from table like 'string';

Comments
  • ^ if that's the case, all the more reason to ask why there's no change.
  • FOR THE LOVE OF /dev/null ... dont use the PHP mysql legacy api
  • Actually no, mysql_query returns false on error.
  • Using OR in this place is utter nonsense. A query that does not return any records is not a failed query.
  • 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.
  • Are you sure? Doesn't bind_param make this query like ALTER IGNORE TABLE "tableName" ADD "fieldName" VARCHAR(20);?
  • Why worry about the case sensitivity of the table-/field-name?
  • No no, I mean quotes ALTER IGNORE TABLE "tableName" AD... instead of ALTER IGNORE TABLE tableName AD...
  • I understand the confusion, going to fix it.
  • 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