if within mysql show to create or insert [closed]

Navigation
-1

Good afternoon, I need to understand how an if inside mysql works so I can use it as follows.

I have a foreach which only executes after a select database because it leaves an array for this foreach, in this foreach it will run a condition, if a column exists it will insert information into it, if it does not exist it will create the column and then insert information in it, the problem is, if I send two columns of the same name it will create the first and insert already in the second will generate error because the column exists, how to see if the column exists before creating without have to do another query and another loop? 

SELECT column_name FROM information_schema.COLUMNS
WHERE column_name = 'telefone' AND TABLE_NAME = 'usr_9f8b498a8e2976d3'

SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'usr_9f8b498a8e2976d3' AND COLUMN_NAME = 'telefone'

In these two selects I can bring in whether or not the column exists, the bottom one is faster, in case I would like at the end of these selects, in the same query, if TRUE does not create the column but if it gives false then create the column

More or less like this link

something like this, only in mysql that works 

IF (SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'usr_9f8b498a8e2976d3' AND COLUMN_NAME = 'telefone') THEN
BEGIN
        select * from usr_9f8b498a8e2976d3
END;
ELSE
BEGIN
    select * from usr_9f8b498a8e2976d3
END;
END IF;

I found this code which is exactly how I want it, however I can not execute alter table command and or insert into within true and false, but true and false work exactly like desire, there is how to run alter tabble inside true and insert into false? in the case in false it has to rotate both alter table and the insert into and in true only the insert into 

SELECT IF((SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'usr_9f8b498a8e2976d3' AND COLUMN_NAME = 'sobrenome'),(
'true'
),(
'false'
))
    
asked by anonymous 20.03.2016 / 22:03

1 answer

0

Looking for a lot, a lot of tests, I found an alternative, but not yet what I want 

SET @preparedStatement = (SELECT IF((
SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$usr_' AND COLUMN_NAME = '" . acent_remove($value) . "'
) > 0,
' ',
'ALTER TABLE $usr_ ADD " . acent_remove($value) . " VARCHAR(99) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

This option works but I do not know how to apas within the quotation mark because it requires that the alter table be between '' and I need to rotate '' '' 

"ALTER TABLE $usr_ ADD " . acent_remove($value) . " VARCHAR(99) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '" . $array_form_a_index[$index] . "'"

At the end of this section '$ array_form_a_index [$ index].' 'I need it to understand the quote quotation without affecting the double quotation mark because it is within php with PDO, complicated 

$statement = $conect -> prepare("SET @preparedStatement = (SELECT IF((
SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$usr_' AND COLUMN_NAME = '" . acent_remove($value) . "'
) > 0,
' ',
'ALTER TABLE $usr_ ADD " . acent_remove($value) . " VARCHAR(99) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL'));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists; ");

I do not know exactly if in terms of speed and or other technical terms this would be the best way, but heredoc is giving syntax error so I figured out how to "escape string" and now it is working fine

follow completed code 

$statement = $conect -> prepare("SET @preparedStatement = (SELECT IF((
SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$usr_' AND COLUMN_NAME = '" . acent_remove($value) . "') > 0,
' ',
'ALTER TABLE $usr_ ADD " . acent_remove($value) . " VARCHAR(99) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT \'" . $array_form_a_index[$index] . "\''
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;");

If anyone knows how to do this correctly or if that is correct, I accept suggestions. grateful

    
22.03.2016 / 09:10
Repeat loop is in C Display only landline and cell phones with digit 9 in front