submit a html form to mySQL using php

Question

very new to this, i am currently trying to create a log in system for my website. i have created a html log in form which i plan to use for users to create accounts. i have created a php page which has my code to connect to the server which is shown below.

when i fill the form i dont get any output. I'm not sure if the php code is in the wrong place (it is as a separate file) or no output is expected. when a form is submitted, the database doesn't seem to change when i submit it manually while testing.

My end goal is to be able to add users to the table called users in my database.

Here is my code for my log in form:

 <body>

        <h2>Sign Up</h2>

        <p></p>

        <form action="Create_User.php" method="post">
            <div class="imgcontainer">
                <img src="http://fc05.deviantart.net/fs70/f/2012/361/1/6/albert_einstein_by_zuzahin-d5pcbug.jpg" alt="Einstein the lad" class="img" />
            </div>

            <div class="container">
                <label><b>Username</b></label>
                <input type="text" placeholder="Please Enter your desired Username" name="username" required />

                <label><b>Password</b></label>
                <input type="password" placeholder="Please Enter Your Desired Password" name="password" required />

                <label><b>Email Address</b></label>
                <input type="email" placeholder="Please Enter Your Email Address" name="email" required />

                <label><b>Date Of Birth</b></label>
                <input type="date" name="date_of_birth" required />

                <label><b>First Name</b></label>
                <input type="text" placeholder="Please Enter your first name" name="first_name" required />

                <label><b>Surname</b></label>
                <input type="text" placeholder="Please Enter your surname" name="surname" required />

            </div>

            <div class="container" style="background-color: #f1f1f1">
                <button type="submit">Sign Up</button>
                <button class="signinbtn" onclick="location.href='/AccountRelatedPages/SignIn.aspx'">Already have an account? Sign in here</button>
            </div>
        </form>

    </body>

here is the code in my php file:

<?php
$servername = "localhost";
$username = "root";
$password = "rootpass";
$dbname = "synther_physics";


$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO users (username, password, email, date_of_birth, first_name, surname)
VALUES ('<?php echo $_POST[$username];', '<?php echo $_POST[$password];', '<?php echo $_POST[$email], <?php echo $_POST[$date_of_birth];, <?php echo $_POST[$first_name], <?php echo $_POST[$surname];')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Again very new to all this so im trying my best to get my head around so please bear that in mind.

Thanks.

your code failed; outright. Error reporting and error checking on the query would have said so. You have many errors. — Funk Forty Niner, Apr 03 '17 at 14:55
It matters less for this post because you're hosting locally, but keep in mind that it is not a good idea to post login credentials. — blackandorangecat, Apr 03 '17 at 14:55
There are a *variety* of potential problems here. But the most immediate one is probably that SQL query. Why are you putting PHP code *in SQL*? As a quick fix remove all of that PHP code in that string and just use the variables you're trying to use. But this is *open to **SQL injection***. So better yet, since you're using `mysqli`, take a look at a tutorial for using prepared statements with query parameters. — David, Apr 03 '17 at 14:56
Your code is unsafe to use; especially plain text passwords. Do not put this online. I'd call this a "blessing in disguise" that your code failed. Use a prepared statement and `password_hash()` / `password_verify()`. — Funk Forty Niner, Apr 03 '17 at 14:56
please do not directly post from your form. Read more on PDO and prepared statements.I really hope this is not in production — Rotimi, Apr 03 '17 at 14:57
Possible duplicate of [Canonical: How to save HTML form data into MySQL database](http://stackoverflow.com/questions/24245012/canonical-how-to-save-html-form-data-into-mysql-database) — Sergey Chizhik, Apr 03 '17 at 14:57
The actual SQL in your `$sql` variable is a mess. You got all kinds of PHP opening tags in it and you are missing some apostrophes... — Tom Udding, Apr 03 '17 at 14:58
Are you interested to know what is happening in this first bit in the query? `' — Funk Forty Niner, Apr 03 '17 at 14:58
As a debugging step, `echo` the value of `$sql` and look at what you're actually sending to your database. See if you can get that to execute manually on MySQL before trying to get it to execute from PHP code. — David, Apr 03 '17 at 14:59
this is just a test guys, no security issues, something that ill focus on later, just trying to get an idea of how to use the fundamentals — Daniel Turville, Apr 03 '17 at 15:01
this is the first piece of php ive ever written so not really sure how the html works with the php — Daniel Turville, Apr 03 '17 at 15:01
if my php is in a different place to my html, will it still output the 'echo $sql' — Daniel Turville, Apr 03 '17 at 15:07
@DanielTurville Daniel; if you're going to just "test" things out, I highly suggest that you don't start testing using unsafe practices. Plus, you'd only be doing more work / spending more time afterwards in rewriting your code using what I mentioned above. You plan on doing a register/login site; start off on the right foot. — Funk Forty Niner, Apr 03 '17 at 15:07
@DanielTurville: `"no security issues, something that ill focus on later"` - Translation: "First I want to learn how to do it wrong. Then I'll throw all that away and learn how to do it right." Why? Putting aside "security" for a moment, SQL-injectable code is error-prone code. So if your code has errors, *fixing it* is probably a good step. Do it right and you won't have to spend a whole lot of time trying to force yourself to do it wrong. — David, Apr 03 '17 at 15:08
*"Can someone please write an answer?"* - @Akin I'm not up to it since it may lead to something else; nope, I'm not up to this. The OP has been given enough to start with. They have the terminology and the functions they can look up. There is far too much work to be in this one. — Funk Forty Niner, Apr 03 '17 at 15:09
I need to replace the PHP code in the Values brackets with the names of the variables in html code, so if i want to submit the inputted username i need to replace the — Daniel Turville, Apr 03 '17 at 15:09
ok, ill start from scratch and try to get all the security stuff done too, can anyone show me where i might be able to find this? — Daniel Turville, Apr 03 '17 at 15:10
Possible duplicate of [PHP inserting values from the form into mysql](http://stackoverflow.com/questions/37367992/php-inserting-values-from-the-form-into-mysql) — Masivuye Cokile, Apr 03 '17 at 15:13
[Start here.](http://jayblanchard.net/demystifying_php_pdo.html) and then see [proper password prep](http://jayblanchard.net/proper_password_hashing_with_PHP.html). — Jay Blanchard, Apr 03 '17 at 15:16
Ok thankyou to everyone for their help, ive amde small changes but the code but ultimately i will probably start again, just to double check, is the code actually in the correct location, ie do i have the php as a seperate file? — Daniel Turville, Apr 03 '17 at 15:22
@DanielTurville welcome. *"do i have the php as a seperate file?"* - The choice is yours. If you plan on using both html/php together, you'll have to rename your (html form) file to `.php` if it is presently an `.html` extension, and use conditional statements around the entire php/mysql to check if a submit is set and that inputs are not empty. In any case; you should do that whether it's inside the same file or in separate files. In not doing that, you stand at either getting mysql errors and/or empty data inserted in db. — Funk Forty Niner, Apr 03 '17 at 15:25
oh i see, so the php code should be below the html code and not in a seperate file, should i change the action parameter of the form input to the new name of the page? i think ive made the page in asp. i think i really need to start again... — Daniel Turville, Apr 03 '17 at 15:30

Masivuye Cokile · Accepted Answer · 2017-04-03T15:51:45.520

Putting all together from the comments, sql injections, password_hash(). for sql injections protection then u need use prepared statements. I won't say much a lot of important things were said in the comments, hope you went through them all, because I did.

This is how your code should look :

<?php
$servername = "localhost";
$username   = "root";
$password   = "rootpass";
$dbname     = "synther_physics";



//Validate user inputs
$username = $_POST['username'];

$password = $_POST['password'];

$hash = password_hash($password, PASSWORD_DEFAULT);

$email = $_POST['email']; //VALIDATE the email

$dob   = $_POST['date_of_birth'];
$fname = $_POST['first_name'];


$sname = $_POST['surname'];

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

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

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

$stmt->bind_param("ssssss", $username, $hash, $email, $dob, $fname, $sname);

if ($stmt->execute()) {

    echo "New record created successfully";
} else {

    echo "Error : " . $conn->error; // on dev mode only

    // echo "Error, please try again later"; //live environment
}

$conn->close();
?>

Edit :

if your php is on the same file and the html, then to avoid undefined indexes notice, you will need to check if the form was submitted, before processing. what you need to do is to have a name attribute to your form button.

then check if form is submitted.

<?php
$servername = "localhost";
$username   = "root";
$password   = "rootpass";
$dbname     = "synther_physics";



//Validate user inputs
if(isset($_POST['buttonName'])){


$username = $_POST['username'];

$password = $_POST['password'];

$hash = password_hash($password, PASSWORD_DEFAULT);

$email = $_POST['email']; //VALIDATE the email

$dob   = $_POST['date_of_birth'];
$fname = $_POST['first_name'];


$sname = $_POST['surname'];

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

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

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

$stmt->bind_param("ssssss", $username, $hash, $email, $dob, $fname, $sname);

if ($stmt->execute()) {

    echo "New record created successfully";
} else {

    echo "Error : " . $conn->error; // on dev mode only

    // echo "Error, please try again later"; //live environment
}

$conn->close();
}
?>

Also you need to check if fields are set and not empty.

thankyou very much for this, however i think i need to make sure that i understand all of the aspects before using it. ill break it down and go through it now. thankyou again. — Daniel Turville, Apr 03 '17 at 15:34
You welcome, if anything you need clarity on feel free to ask me — Masivuye Cokile, Apr 03 '17 at 15:35
@MasivuyeCokile You decided to do a rewrite after all :-) Bit of a sidenote here, since the OP did comment up there on possibly using the entire code in one file. It would be best in either case to check if a submit is set and that any inputs are not left empty. This, as per [this comment I left...](http://stackoverflow.com/questions/43187822/submit-a-html-form-to-mysql-using-php#comment73450192_43187822) earlier. — Funk Forty Niner, Apr 03 '17 at 15:38
@MasivuyeCokile cool. Another thing though that the OP should be made aware of and included in the answer, is that the password column needs to be 60+ in length. As you may know, the manual says that 255 is a good bet ;-) They have a lot to learn. — Funk Forty Niner, Apr 03 '17 at 15:41
@Fred-ii- Totally off topic question hope you dont mind me asking, but ive created this demo 'project' website in asp.net but i am trying to code it like this, when i started i really wasnt sure what all of it meant but i feel like it doesnt really work together, am i correct in saying this or if i am actually doing it properly. my final goal is a revision website where people can log in to take tests and their scores can be saved in the database. will plain html pages work fine if the rest of the website is created using asp? — Daniel Turville, Apr 03 '17 at 15:46
also @Fred-ii- when you say that the password column needs to be 60+ in length, is that in the database table? to allow for values of that size? — Daniel Turville, Apr 03 '17 at 15:50

submit a html form to mySQL using php

1 Answers1