How do I get the value of the form into a MySQL table?

Question

All I want is to get the var1 from the input into my SQL table. It always creates a new ID, so this is working, but it leaves an empty field in row Email. I never worked with SQL before and couldn't find something similar here. I thought the problem could also be in the settings of the table, but couldn't find anything wrong there.

<input name="var1" id="contact-email2" class="contact-input abo-email" type="text" placeholder="Email *" required="required"/>
<form class="newsletter-form" action="newsletter.php" method="POST">
             <button class="contact-submit" id="abo-button" type="submit" value="Abonnieren">Absenden
             </button>
</form>

<?php

$user = "user";
$password = "password";
$host = "localhost:0000";
$dbase = "base";
$table = "table";

// Connection to DBase
$con = new mysqli($host, $user, $password, $dbase) or die("Can't connect");
$var1 = $_POST['var1'];

$sql = "INSERT INTO table (id, Email) VALUES ('?', '_POST[var1]')";

$result = mysqli_query($con, $sql) or die("Not working");

echo 'You are in!' . '<br>';

mysqli_close($con);

please read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and use **Prepared statements with parameters** — nbk, Oct 31 '20 at 21:47
Is this really how your form looks like? Your input is outside of your form. — Dharman, Oct 31 '20 at 22:29
@Dharman Yes, putting it inside doesn't really change anything as far as I know. — noah222, Oct 31 '20 at 22:39
@noah222 It has to be inside otherwise the value will not be sent to the server. Have you inspected what is actually sent? Are you aware of the browser inspector? — Dharman, Oct 31 '20 at 22:40
@Dharman Yes I am! I tried it with the input inside of the form and outside. It worked both ways! I does work now properly. — noah222, Oct 31 '20 at 22:50
I used the answer from @Steven. I had the `$con = new mysqli($host, $user, $password, $dbase) or die("Can't connect");` and didn't close the $con connection, but the $mysqli. Thats why it didn't execute. — noah222, Oct 31 '20 at 22:55

waheed rahman · Answer 1 · 2020-10-31T22:46:17.123

0

is the id a unique id? that's auto-incremented?? if so you should do something like this

    <?php

    $user = "user";  
    $password = "password";  
    $host = "localhost:0000";  
    $dbase = "base";  
    $table = "table";  

    $mysqli = new mysqli($host,$user,$password,$dbase);
    $email = $_POST['var1'];

    // you might want to make sure the string is safe this is escaping any special characters

    $statment = $mysqli->prepare("INSERT INTO table (Email) VALUES (?)");
    $statment->bind_param("s", $email);

    if(isset($_POST['var1'])) {
            $statment->execute();
    }

    $mysqli->close();
    $statment->close();

edited Oct 31 '20 at 22:46

answered Oct 31 '20 at 21:54

waheed rahman

91
4

Thanks for your answer! I probably still got something wrong, because I get a fatal error: Uncaught Error: Call to a member function real_escape_string() on null (on line 8). The Id is a primarykey and auto-incremented! – noah222 Oct 31 '20 at 22:12
send me your code will have a look at it for you, always welcome to help. – waheed rahman Oct 31 '20 at 22:25

Steven · Accepted Answer · 2020-10-31T22:34:54.037

Simple answer

There are a few things wrong here; but the simple answer is that:

 $sql = "INSERT INTO table (id, Email) VALUES ('?', '_POST[var1]')";

...should be:

 $sql  = "INSERT INTO {$table} (id, Email) VALUES ('?', '{$var1}')";

...OR assuming id is set to auto-increment etc. etc.

$sql  = "INSERT INTO {$table} (Email) VALUES ('{$var1}')";

More involved answer

You should really take the time to use prepared statements with SQL that has user inputs. At the very least you should escape the strings yourself before using them in a query.

mysqli

$user     = "user";  
$password = "password";  
$host     = "localhost:0000";  
$dbase    = "base";  
$table    = "table";  

$mysqli = new mysqli($host, $user, $password, $dbase); // Make connection to DB

if($mysqli->connect_error) {
    die("Error: Could not connect to database.");
}

$email  = $_POST["var1"];                              // User input from form

$sql    = "INSERT INTO {$table} (Email) VALUES(?)";    // SQL query using ? as a place holder for our value
$query  = $mysqli->prepare($sql);                      // Prepare the statement
$query->bind_param("s", $email);                       // Bind $email {s = data type string} to the ? in the SQL
$query->execute();                                     // Execute the query

PDO

$user     = "user";  
$password = "password";  
$host     = "localhost:0000";  
$dbase    = "base";  
$table    = "table";



try {
  $pdo = new pdo( "mysql:host={$host};dbname={$dbase}", $user, $password); // Make connection to DB
}
catch(PDOexception $e){
  die("Error: Could not connect to database.");
}

$email  = $_POST["var1"];                           // User input from form

$sql    = "INSERT INTO {$table} (Email) VALUES(?)"; // SQL query using ? as a place holder for our value
$query  = $pdo->prepare($sql);                      // Prepare the statement
$query->execute([$email]);                          // Execute the query binding `(array)0=>$email` to place holder in SQL

@noah222 Yes, error reporting. Both mysqli and PDO have error reporting which this answer does not show how to enable. — Dharman, Oct 31 '20 at 22:25
Can you please roll back your last edit? There should never be any try-catch around `new PDO` and you should never manually check for connection errors. [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Oct 31 '20 at 22:39

How do I get the value of the form into a MySQL table?

2 Answers2

Simple answer

More involved answer