PHP: Inserting Values from the Form into MySQL

Question

I created a users table in mysql from the terminal and I am trying to create simple task: insert values from the form. This is my dbConfig file

<?php
$mysqli = new mysqli("localhost", "root", "pass", "testDB");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

and This is my Index.php .

<!doctype html>
<html>
 <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="description" content="$1">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <link rel="stylesheet" type="text/css" href="style.css">

    <title>test</title>

    <?php
    include_once 'dbConfig.php';
    ?>

</head>
<body>
     <?php
    if(isset($_POST['save'])){
        $sql = "INSERT INTO users (username, password, email)
        VALUES ('".$_POST["username"]."','".$_POST["password"]."','".$_POST["email"]."')";
    }

    ?>

    <form method="post"> 
    <label id="first"> First name:</label><br/>
    <input type="text" name="username"><br/>

    <label id="first">Password</label><br/>
    <input type="password" name="password"><br/>

    <label id="first">Email</label><br/>
    <input type="text" name="email"><br/>

    <button type="submit" name="save">save</button>
    <button type="submit" name="get">get</button>
    </form>

</body>
</html>

After hitting my save button, nothing happens, database is still empty. I tried echo'ing the INSERT query and it takes all values from the form as it is supposed to. After I try to check if this worked from terminal, I login into my sql try to return all data from users table and I get empty set.

You need to mysqli_query($mysqli, $sql); the query. You are currently just echoing the query, which basically just outputs it to the screen. — Qirel
– Qirel, Commented May 21, 2016 at 21:07

Dharman · Accepted Answer · 2021-03-24 01:46:40Z

50

The following code just declares a string variable that contains a MySQL query:

$sql = "INSERT INTO users (username, password, email)
    VALUES ('".$_POST["username"]."','".$_POST["password"]."','".$_POST["email"]."')";

It does not execute the query. In order to do that you need to use some functions but let me explain something else first.

NEVER TRUST USER INPUT: You should never append user input (such as form input from $_GET or $_POST) directly to your query. Someone can carefully manipulate the input in such a way so that it can cause great damage to your database. That's called SQL Injection. You can read more about it here

To protect your script from such an attack you must use Prepared Statements. More on prepared statements here

Include prepared statements to your code like this:

$sql = "INSERT INTO users (username, password, email)
    VALUES (?,?,?)";

Notice how the ? are used as placeholders for the values. Next you should prepare the statement using mysqli_prepare:

$stmt = $mysqli->prepare($sql);

Then start binding the input variables to the prepared statement:

$stmt->bind_param("sss", $_POST['username'], $_POST['email'], $_POST['password']);

And finally execute the prepared statements. (This is where the actual insertion takes place)

$stmt->execute();

NOTE Although not part of the question, I strongly advice you to never store passwords in clear text. Instead you should use password_hash to store a hash of the password

edited Mar 24, 2021 at 1:46

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered May 21, 2016 at 21:48

dimlucas

5,1417 gold badges41 silver badges54 bronze badges

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

6 Comments

notrealme Over a year ago

Thank you for such a detailed answer. I will defenetly look into that, as this is my first attempt on PHP I'm focusing only on functionality, but I will try to improve in the future.

miken32 Over a year ago

@notrealme if you're just getting started I would strongly suggest looking at PDO. It's much easier to use than mysqli, especially with prepared statements.

João Ciocca Over a year ago

I tried this and got an error complaining that mysqli_prepare requires 2 parameters.

A. Volg Over a year ago

@dimlucas what is "sss" in parameters binding?

dimlucas Over a year ago

@A.Volg 's' stands for string. If one of the params was a number we would use 'd' to indicate a decimal

|

Syed Muneeb Ul Hasan · Accepted Answer · 2016-09-23 07:04:51Z

There are two problems in your code.

No action found in form.
You have not executed the query mysqli_query()

dbConfig.php

<?php

$conn=mysqli_connect("localhost","root","password","testDB");

if(!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}

?>

index.php

 include('dbConfig.php');

<!Doctype html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="description" content="$1">
<meta name="viewport" content="width=device-width, initial-scale=1">

<link rel="stylesheet" type="text/css" href="style.css">

<title>test</title>


</head>
<body>

 <?php

  if(isset($_POST['save']))
{
    $sql = "INSERT INTO users (username, password, email)
    VALUES ('".$_POST["username"]."','".$_POST["password"]."','".$_POST["email"]."')";

    $result = mysqli_query($conn,$sql);
}

?>

<form action="index.php" method="post"> 
<label id="first"> First name:</label><br/>
<input type="text" name="username"><br/>

<label id="first">Password</label><br/>
<input type="password" name="password"><br/>

<label id="first">Email</label><br/>
<input type="text" name="email"><br/>

<button type="submit" name="save">save</button>

</form>

</body>
</html>

Collectives™ on Stack Overflow

PHP: Inserting Values from the Form into MySQL

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related