0

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

<?php
$emaila="test@test.com";
$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...

Sumutiu Marius
  • 421
  • 4
  • 18
  • `test@test.com` is that really a column? `$val = mysqli_query("select` no connection here – Funk Forty Niner Feb 22 '17 at 19:57
  • It should be a column. If it is not, it should be added. – Sumutiu Marius Feb 22 '17 at 19:59
  • `select $emaila from emails` translates to `select test@test.com 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 Feb 22 '17 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 Feb 22 '17 at 20:01
  • if you want to check if a row exists, then you need to do `select email_column from emails WHERE email_column='test@test.com'` and with `num_rows()` afterwards. – Funk Forty Niner Feb 22 '17 at 20:04
  • No. I want to add the $emaila as column if there isn't any column $emaila, so then I could get all the columns with implode to send an email to the entire columns (emails) – Sumutiu Marius Feb 22 '17 at 20:07
  • That is not the way to do that - that is ***bad design***. You would put the email addresses into one column, select from that column and loop through the results to send emails. Show us how you create the table. – Jay Blanchard Feb 22 '17 at 20:07
  • I know but as said, I did something like this in MySQL and it worked. That was some time ago, now it is not working only if I downgrade the PHP version. I don't quite know that much about MySQLi. I decided to do the script again but things are not quite the same as in mysql. Can you show me a better way? – Sumutiu Marius Feb 22 '17 at 20:11
  • I already did below. – Jay Blanchard Feb 22 '17 at 20:12
  • It gives an error: Unknown column 'email_column_name' in 'where clause' – Sumutiu Marius Feb 22 '17 at 20:14
  • Becasue you have to use the name of the column you have already setup to use for the email addresses. – Jay Blanchard Feb 22 '17 at 20:18
  • If you do not know how to do this go take some online tutorials before you write one more line of code. This is PHP/MySQL 101 – Jay Blanchard Feb 22 '17 at 20:20
  • The email adresses ARE the columns. – Sumutiu Marius Feb 22 '17 at 20:20
  • *"The email adresses ARE the columns."* - OMG, really? That isn't how it works. You need 1 column named `email` with separate "rows" holding the email addresses. You need to start over. – Funk Forty Niner Feb 22 '17 at 20:21
  • I know... It's an old database with over 500 emails as columns already... The above code should check if $emaila is already a column and if it isn't it should be added. – Sumutiu Marius Feb 22 '17 at 20:24
  • Who gave you that database? who built it? OMG – Funk Forty Niner Feb 22 '17 at 20:25
  • It was a database of an older newsletter. The owner of the newsletter was a friend of mine and he gave it to me. Now I decided to start the newsletter again but the older PHP code is not working properly on the newer PHP version. That's why I wanted to do it in MySQLi. – Sumutiu Marius Feb 22 '17 at 20:28
  • It would be better if you got all of the existing columns as rows in a new table and then go from there. – Jay Blanchard Feb 22 '17 at 20:28
  • I think that would be the best way... Thanks – Sumutiu Marius Feb 22 '17 at 20:30
  • You're going to have to figure out a way to save those columns as values and I honestly have no idea how to do that. I guess I could figure out a way, but that stands to take a lot of time. – Funk Forty Niner Feb 22 '17 at 20:30
  • Maybe I should get all the columns as string, and use explode. – Sumutiu Marius Feb 22 '17 at 20:34
  • 1
    This Q&A http://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 http://php.net/manual/en/function.fgetcsv.php. See also http://stackoverflow.com/q/16391528/1415724 and http://www.mysqltutorial.org/mysql-export-table-to-csv/ – Funk Forty Niner Feb 22 '17 at 20:38
  • I edited that a few times ^ you'll need to reload it. And Jay edited his answer also with an example. – Funk Forty Niner Feb 22 '17 at 20:42
  • This is the way it was done before: ` ` – Sumutiu Marius Feb 22 '17 at 22:28
  • I can't paste the code... https://s27.postimg.org/tkdvs5t5f/aaaaaaaaaaa.jpg – Sumutiu Marius Feb 22 '17 at 22:30

1 Answers1

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.

Community
  • 1
  • 1
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119