MYSQL: Insert column if not exists

Question

I am trying to add a column by checking if it exists. If not it should give message that it already exists with the code below.

$prefix = 'vm_';
$col_name = 'checking';

$col = "SELECT ".$col_name." FROM ".$prefix."users";

if (!$col){
    $insert_col = "ALTER TABLE ".$table." ADD ".$col_name." DATETIME NOT NULL";

    mysql_query($insert_col);

    echo $col_name.' has been added to the database';
} else {
    echo $col_name.' is already exists';
}

But it doesn't add any column and directly displays message that the column already exists.

You have to run the query first. And please use mysqli as mysql is in the deprecation process @pixelngrain — Sid, Nov 08 '12 at 17:44
where to use mysqli? for $col=mysqli or everywhere? I am new to mysql and don't know if anything wrong — Code Lover, Nov 08 '12 at 18:08

score 8 · Answer 1 · answered Nov 08 '12 at 17:30

8

You never execute your query, your condition is instead your query string:

if (!$col) { //always false, since a nonempty string is always truthy

answered Nov 08 '12 at 17:30

Asad Saeeduddin

46,193
6
90
139

Ah give me a sec let me try one thing. In fact I just realized my stupid mistake I didn't user mysql_query for $col. – Code Lover Nov 08 '12 at 17:31
Its done. that was really stupid mistake.. I will ask to remove this question.. thanks.. or may be I can keep with my solution – Code Lover Nov 08 '12 at 17:33

score 5 · Accepted Answer · edited Feb 03 '17 at 21:31

5

Here is the final code. There was stupid mistake; I didn't use mysql_query for $col

$prefix = 'vm_';
$col_name = 'checking';

$col = mysql_query("SELECT ".$col_name." FROM ".$prefix."users");


if (!$col){
    //$insert_col = "ALTER TABLE ".$table." ADD ".$col_name." DATETIME NOT NULL";

    mysql_query("ALTER TABLE ".$prefix."users ADD ".$col_name." DATETIME NOT NULL");

    echo $col_name.' has been added to the database';
} else {
    echo $col_name.' is already exists';
}

edited Feb 03 '17 at 21:31

The Codesee

3,714
5
38
78

answered Nov 08 '12 at 17:35

Code Lover

8,099
20
84
154

please note this doesn't work. It'll will throw a fatal error and render the rest of the script useless. – Patrick Mutwiri Feb 10 '16 at 08:29

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

3

You have to run your query before you check statements with it. It's like you can't see whether the box is empty of not (i.e. result of your query) without opening the box (i.e. running the query)

Replace your line 04 with this

$col = mysql_query("SELECT ".$col_name." FROM ".$prefix."users");

Then your problem will be solved

For more information on mysqli, please read this article

You might consider moving on to PDO statements as well.

Hope this helps

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 09 '12 at 02:23

Sid

1,255
2
22
45

Yes that's true. Thanks a lot. However I realized after sometime of posting my stupid question and corrected can see in my own answer. Please know if anything wrong in that.. thanks again – Code Lover Nov 09 '12 at 06:05

score 2 · Answer 4 · answered Nov 08 '12 at 17:48

2

You haven't executed your query. First execute your query, then check the condition on it.

answered Nov 08 '12 at 17:48

Unpredictable

113
4

Try not to use any mysql_* function. Start using mysqli – Unpredictable Nov 08 '12 at 18:36

score 1 · Answer 5 · edited Feb 03 '17 at 21:21

1

This is what I did and it works:

$db= new mysqli('host','user','password','db');
$query = "SHOW COLUMNS FROM tablename LIKE 'columnname'";
$db->query($query);
if(empty(empty($db->num_rows)) { 
   $alter = "ALTER TABLE tablename ADD columnname varchar(50) NOT NULL"; 
   $db->query($alter); 
} else { 
   echo 'column exists';
}

edited Feb 03 '17 at 21:21

Jim DeLaHunt

10,960
3
45
74

answered Feb 10 '16 at 08:56

Patrick Mutwiri

1,301
1
12
23

MYSQL: Insert column if not exists

5 Answers5