php - I cant get the form data to go into database. What am I doing wrong?

Question

CODE UPDATED, STILL NOT WORKING. I know I′m apparently using mysql function which will be outdated. But for now all I want is for this code to work. I want to know what I′m doing wrong:(

I′m very new to php and databases... I have been struggling to get simple html form data to go into the database table. And I just can′t get it to work:( Can anyone help and see what is wrong with my code? I′ve just done a simple table in the database with the fields ID, FIRSTNAME and SURNAME. Here is the code:

    <?php 
    //connect to database
    $mysql_host = 'localhost';
    $mysql_user = 'root';
    $mysql_pass = '';

    $mysql_db = 'test';

    if (!mysql_connect ($mysql_host, $mysql_user, $mysql_pass)||!mysql_select_db ($mysql_db) ) {
        die(mysql_error());

    }   

    // Code     
    if (isset($_POST['firstname'])&&
    isset($_POST['surname'])) {

    $firstname = $_POST['firstname'];
    $surname = $_POST['surname'];

    if (!empty($username)&&!empty($password)) {
    $query = "INSERT INTO `test`.`test_tabell` 
    VALUES ('', '" . mysql_real_escape_string($firstname) . "', '" . mysql_real_escape_string($surname) . "')";
    /*$query = "INSERT INTO `test`.`test_tabell` VALUES (``, `.$firstname.`, `.$surname.`)"; */
    $query_run = mysql_query($query);
if (!$query_run) echo mysql_error(); 
}
}
    ?>

    <form action="add.php" method="POST">
    Firstname:<br> <input type="text" name="firstname" value="<?php if (isset($firstname)) { echo $firstname; } ?>"><br><br>
    Surname:<br> <input type="text" name="surname" value="<?php if (isset($surname)) { echo $surname; } ?>"><br><br>
    <input type="submit" value="Submit">
    </form> 

Thank you!

Answer 1

Don't use mysql specific syntax, It's outdated and it begins to be annoying when you need to do some high level stuff, and you can't switch to sqlite or postgresql.

I recommend using PDO, you can do something like:

// Usage:   $db = connectToDataBase($dbHost, $dbName, $dbUsername, $dbPassword);
// Pre:     $dbHost is the database hostname, 
//          $dbName is the name of the database itself,
//          $dbUsername is the username to access the database,
//          $dbPassword is the password for the user of the database.
// Post:    $db is an PDO connection to the database, based on the input parameters.
function connectToDataBase($dbHost, $dbName, $dbUsername, $dbPassword)
{
    try
    {
         return new PDO("mysql:host=$dbHost;dbname=$dbName;charset=UTF-8", $dbUsername, $dbPassword);
    }
    catch(PDOException $PDOexception)
    {
        exit("<p>An error ocurred: Can't connect to database. </p><p>More preciesly: ". $PDOexception->getMessage(). "</p>");
    }
}

And then init the variables (I think you forgot to define the name of the database);

$host = 'localhost';
$user = 'root';
$dataBaseName = 'databaseName';
$pass = '';

Now you can access your database via

$GLOBALS['db'] = connectToDataBase($host , $databaseName, $user, $pass);

Now you have an instance of a PDO database donnection.

One thing I want to point out is that you're vonurable to sql injections, you want to use prepared statements in your query, like:

$query = "INSERT INTO test(first_name, sur_name) VALUES (:firstname, :surname);";

Where we will execute two variables $firstName and $surName on the query, making them replace the values of :firstName and :surName, let me show you by first creating a simple insertion function:

function insertFunction($db, $query, $firstName, $surName)
{
    $statement = $db->prepare($query);
    return $statement->execute(array(":firstName" => $firstName, ":surName" => $surName));
}

So It's easy for you to do something like

$firstName = 'Smith';
$surName = 'John';
$db = $GLOBALS['db'];

$success = insertFunction($db, $query, $firstName, $surName);

Now you can check if it was successful or not, by checking whether $success is true or false.

If you want to see more advanced use of PDO (multiple rows etc) then you can check out one of my comments here: Javascript function as php? (Not the top comment).

I hope this helps. Please comment if anything is odd.