Checking table for existent columns, if not, add column

Question

I am trying to build a newsletter. The user has to fill a form where $emaila is his email.

<?php
$emaila="[email protected]";
$con=mysqli_connect("localhost","user","pass","dbname");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$val = mysqli_query("select $emaila from emails limit 1");
if($val !== FALSE)
{
mysqli_query($con,"ALTER TABLE emails ADD (".$emaila." VARCHAR(100))");
mysqli_close($con);
}
?>

It suposes to check if the email is already in the table "emails" as column, and if it isn't, it should put his email in the table as column. Not sure what I did wrong...

[email protected] is that really a column? $val = mysqli_query("select no connection here — Funk Forty Niner
– Funk Forty Niner, Commented Feb 22, 2017 at 19:57
select $emaila from emails translates to select [email protected] from emails. Check for errors on the queries mysqli_error($con) and you'll see. But that never happens because you didn't pass connection to the query. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 22, 2017 at 20:00
If you select like that you will get an error if the column doesn't exist. That doesn't make sense. You want to add a column for each email which there is no column? That also doesn't make sense. — Jay Blanchard
– Jay Blanchard, Commented Feb 22, 2017 at 20:01
This Q&A stackoverflow.com/q/22399477/1415724 could help you. You could then put that .csv file in Excel and modify it, and then reimport into phpmyadmin somehow, or parse it with PHP php.net/manual/en/function.fgetcsv.php. See also stackoverflow.com/q/16391528/1415724 and mysqltutorial.org/mysql-export-table-to-csv — Funk Forty Niner
– Funk Forty Niner, Commented Feb 22, 2017 at 20:38

Community · Accepted Answer · 2017-05-23 12:00:10Z

0

You don't use ALTER TABLE to add data to a table. You use INSERT

INSERT INTO `emails` (`column_name`) VALUES '$emaila'

Your test should be to see if the email exists in the table:

$val = mysqli_query($con,"select * from emails where email_column_name = '$emaila' ") or die (mysqli_error($con));
if($val)
{
mysqli_query($con,"INSERT INTO `emails` (`email_column_name`) VALUES '$emaila'")  or die (mysqli_error($con));
mysqli_close($con);
}

In addition: Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe! Don't believe it?

EDIT: Based on comments you will need to get the column names from the current table, loop through the result set and for each confirm the column name is an email address and then insert that address into a new table. Here is something to get you started:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='database_name' 
AND `TABLE_NAME`='emails';

This query will return a set of column names from the emails table (don't forget to change the database name) that you can loop through.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Feb 22, 2017 at 19:45

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

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

5 Comments

Sumutiu Marius Over a year ago

I am not used to MySQL improved that much and I tried to learn but can't find that much about it... I think there is a problem at $val = mysqli_query("select $emaila from emails limit 1"); too.

Jay Blanchard Over a year ago

Fix one thing and then chase down the other errors.

Sumutiu Marius Over a year ago

I tested and still doesn't work... No connection error. It doesn't add $emaila as column in the table "emails"...

Jay Blanchard Over a year ago

You're trying to add a column? Or you're trying to add data to a column?

Sumutiu Marius Over a year ago

I am trying to add a column.

Collectives™ on Stack Overflow

Checking table for existent columns, if not, add column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related