PHP not inputting into mysql database

Question

I'm trying to insert a username, password and email into a mysql database. The database works fine, as I can pull data out from it and use on the login page, but I can't seem to create new rows and insert data. The sql database is built up like this:

Table: peter
Primary Key: user_id - Int(5), Not Null, Auto Increment
Attributes:
username - VarChar(35), Not Null
password - VarChar(35), Not Null
email - VarChar(35), Not Null

This is my code

<?php
include("config.php");
if($_SERVER["REQUEST_METHOD"] == "POST") {
   $myusername = mysqli_real_escape_string($db,$_POST['username']);
   $mypassword = mysqli_real_escape_string($db,$_POST['password']); 
   $myemail = mysqli_real_escape_string($db,$_POST['email']); 
   $tbl_name = "peter";

   $sql = "INSERT INTO $tbl_name (`username`, `password`, `email`) VALUES ($username, $password, $email)";

   header("location: login.php");
}
?>
<html>
    <body> 
        <form action = "" method = "post">
            <label>UserName: </label><input type = "text" name = "username" class = "box" required/><br /><br />
            <label>Password: </label><input type = "password" name = "password" class = "box" required/><br/><br />
            <label>Email: </label><input type = "email" name = "email" class = "box" required/><br/><br />
            <input type = "submit" value = " Submit "/><br />
        </form>
    </body>
</html>

I've tried a lot of fixes, including from other Q&A threads, but none of them have worked. So I hope someone else sees the problem.

Edit: This is the config.php file:

<?php
   define('DB_SERVER', 'xxx');
   define('DB_USERNAME', 'xxx');
   define('DB_PASSWORD', 'xxx');
   define('DB_DATABASE', 'xxx');
   $db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>

You need to do more than just write a query into a text variable. That query must be executed i.e. issued to the database to be run! [Read The Manual for either PDO](http://php.net/manual/en/book.pdo.php) or [MYSQLI_](http://php.net/manual/en/book.mysqli.php) _SO is not a tutorial provider_ **Do not be tempted to use the MYSQL_ Database extension** — RiggsFolly, Feb 23 '16 at 11:45
Can I ask, Do you actually make a connection to a database somewhere earlier in your code? — RiggsFolly, Feb 23 '16 at 11:52

Jenz · Answer 1 · 2016-02-23T11:49:41.333

1

Your fields are of varchar type. So enclose them in quotes. Also you have to execute the query using mysqli_query().

$sql = mysqli_query("INSERT INTO $tbl_name (`username`, `password`, `email`) VALUES ('$username', '$password', '$email')");

edited Feb 23 '16 at 11:49

answered Feb 23 '16 at 11:43

Jenz

8,280
7
44
77

Please dont use the `mysql_` database extension, it is deprecated (gone for ever in PHP7) **and certainly do not answer questions suggesting its use** Especially if you are just learning PHP, spend your energies learning the `PDO` or `mysqli_` database extensions, [and here is some help to decide which to use](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – RiggsFolly Feb 23 '16 at 11:47
The use of parameterised query would also be a good idea as this answer is suceptable to [SQL Injection attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – RiggsFolly Feb 23 '16 at 11:54
1

Tried this fix, but it still doesn't insert it into the table. – Sirpedro Feb 23 '16 at 11:59

RiggsFolly · Accepted Answer · 2016-02-23T12:19:34.157

It is not enough to just code a query into a variable. Once that is done you must issues that query to the database for execution.

As your query is an INSERT that is basically all you would need to do

Also you need to use the correct variable names for the data you want to pass to the query i.e. the variables from the result of the mysqli_real_escape_string()

<?php
include("config.php");
if($_SERVER["REQUEST_METHOD"] == "POST") {
   $myusername = mysqli_real_escape_string($db,$_POST['username']);
   $mypassword = mysqli_real_escape_string($db,$_POST['password']); 
   $myemail = mysqli_real_escape_string($db,$_POST['email']); 
   $tbl_name = "peter";


   $sql = "INSERT INTO $tbl_name 
                  (`username`, `password`, `email`) 
           VALUES ('$myusername', '$mypassword','$myemail')";

   $res = mysqli_query($db,$sql);

   if ( $res ) {
       header("location: login.php");
       exit;
   } else {
       echo mysqli_error( $db );
       exit;
   }
}
?>
<html>
    <body> 
        <form action = "" method = "post">
            <label>UserName: </label><input type = "text" name = "username" class = "box" required/><br /><br />
            <label>Password: </label><input type = "password" name = "password" class = "box" required/><br/><br />
            <label>Email: </label><input type = "email" name = "email" class = "box" required/><br/><br />
            <input type = "submit" value = " Submit "/><br />
        </form>
    </body>
</html>

A better way would be to use parameterized queries like this

<?php
include("config.php");
if($_SERVER["REQUEST_METHOD"] == "POST") {
   $tbl_name = "peter";

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

   $stmt = mysqli_prepare($db,$sql);

   mysqli_bind_param($stmt, 'sss', $_POST['username'],
                                   $_POST['password'],
                                   $_POST['email']
                    );

   $res = mysqli_stmt_execute($stmt);

   if ( $res ) {
       header("location: login.php");
       exit;
   } else {
       echo mysqli_stmt_error ( $stmt );
       exit;
   }
}
?>
<html>
    <body> 
        <form action = "" method = "post">
            <label>UserName: </label><input type = "text" name = "username" class = "box" required/><br /><br />
            <label>Password: </label><input type = "password" name = "password" class = "box" required/><br/><br />
            <label>Email: </label><input type = "email" name = "email" class = "box" required/><br/><br />
            <input type = "submit" value = " Submit "/><br />
        </form>
    </body>
</html>

Solved my problem nicely, registering now works perfectly, thanks! :) — Sirpedro, Feb 23 '16 at 13:59

score 0 · Answer 3 · edited May 23 '17 at 11:59

0

As RiggsFolly suggested in his comment, you have wrongly used variable names in your query.

Your variable names should be '$myusername', '$mypassword', '$myemail'

Write your code as below:-

// Use single quote '' around variables in query
$sql = "INSERT INTO $tbl_name(`username`, `password`, `email`) VALUES('$myusername', '$mypassword', '$myemail')";
$result = mysqli_query($db,$sql);

// Check for query errors
if(!$result){
   printf("Error: %s\n", mysqli_error($db));
   die;
}

header("location: login.php");
exit; // write exit after header tag to stop executuon of below code

Hope it will help you :)

edited May 23 '17 at 11:59

Community

1
1

answered Feb 23 '16 at 12:00

Ravi Hirani

6,511
1
27
42

@downvoter: You should pointed error. I have edited my answer. – Ravi Hirani Feb 23 '16 at 12:06
@RiggsFolly: Ohh!! I had completely missed it. Thanks for pointing it out. I have edited answer. – Ravi Hirani Feb 23 '16 at 12:12
@RiggsFolly: You are welcome :) I have mentioned your comment in answer also. Thanks again. – Ravi Hirani Feb 23 '16 at 12:17

PHP not inputting into mysql database

3 Answers3