C API mysql Add columns if they don't exist incorrect syntax

Question

I have searched everything I could ) Truly. But I can't find the correct way to add new columns only after checking if the column doesn't exist. I am writing a program in C. Here is what I am doing, and I can't find my mistake in syntax. I will be very grateful for your help! I get an error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

//create buffer to store the query
char buff[1024];
//store query in the buffer                
snprintf(buff, sizeof buff, "IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME = '%i' AND TABLE_NAME = '%s' AND TABLE_SCHEMA = '%s') THEN ALTER TABLE `%s`.`%s` ADD COLUMN `%i` INT; END IF;", value1, table, database, database, table, value1);

EDIT

I am editing the post to show what I am trying to achieve. Using nested if statement in the main function, I have created the database and the table, and have populated the table with column names; my code is designed in a way that all functions are interrelated: only if connection is established, the program calls "create database" function; only if database is created, the program calls "create table" function; only if the table is created and initially only two columns are added (id and Names), the program calls the function to alter table in order to add other columns.

I do so because I need a for loop to loop those additional column names, which were created previously by my previous C program. So the table should look like this:

id   name   1988   1977   1966   1955
1    name1  value  value  value  value
2    name2  value  value  value  value
3    name3  value  value  value  value

Each time the program is called, each function checks if database exists, then it is not created from scratch, if table exists, it is not created, and now I am stumbled on how to check of columns exist, because if they do, I get an error and the program can't move on.

Answer 1

To add a column you can do it like this

snprintf(
    buff, 
    sizeof buff, 
    "ALTER TABLE `%s`.`%s` ADD COLUMN IF NOT EXISTS `%s` `%s`", 
    database,
    table, 
    column_name, 
    column_type
);

Note that in your format string there is a %i that doesn't look right.

After giving you the answer, because this is what you asked for, I want to say that adding a column in code like that looks like a bad sign. SQL databases are pretty static in their structure, you should never need to add or remove columns from it. If you have to, then there is a problem either in the database design or the way you are handling it.

According to the comments below you need something like this

CREATE TABLE `names` (
    `name` VARCHAR(128) PRIMARY KEY
) ENGINE = InnoDB;

CREATE TABLE `entries` (
    `name` VARCHAR(128) NOT NULL,
    `year` INTEGER NOT NULL,
    -- Or the required type (FLOAT perhaps?)
    `value` INTEGER NOT NULL,
    -- All names MUST come from the `names` table
    CONSTRAINT `name_fk` FOREIGN KEY (`name`) REFERENCES `names` (`name`),
    -- Allow only one entry per `name`/`year`
    CONSTRAINT `entry_pk` PRIMARY KEY (`name`, `year`)
) ENGINE = InnoDB;

And then you can insert each name in the names table, and one entry per year in the entries table, you can have the combinations you want and you can query all years for a given name

SELECT * FROM `entries` WHERE `name` = ?

Creating a database schema dynamically is wrong, it's just against the whole idea of a schema, a database has a schema so you can write queries an rely on them working, the language is called Structured Query Language for a reason.