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!

What errors are you getting? You're setting $query after you run it. Reverse it and also echo $query so you can see what it is doing. — Matt, Mar 06 '13 at 19:29
First of all, you're using the deprecated `mysql_*` functions. — hjpotter92, Mar 06 '13 at 19:29
@mkaatman I managed to get rid of all errors, now the data is just not getting into the database table... — Lisa, Mar 06 '13 at 19:31
Review [this question](http://stackoverflow.com/questions/11321491/mysql-when-to-use-single-quotes-double-quotes-and-backticks) to understand the issue with your variable quoting in the query. — Michael Berkowski, Mar 06 '13 at 19:31
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Naftali, Mar 06 '13 at 19:31
But you are calling `mysql_query()` before you actually set the `$query` variable?? You need some error checking. `if (!$query_run) echo mysql_error()`. You'll need to do some work to protect this from SQL injection. — Michael Berkowski, Mar 06 '13 at 19:32
@Tucker What exactly do you mean? It´s easyphp database, no password, the connection with the database should be working properly. Database is called: TEST table is called: test_table and there is just 3 table columns: ID, FIRSTNAME and SURNAME — Lisa, Mar 06 '13 at 19:33
@MichaelBerkowski Should I put that code before calling mysql_query? I know about SQL injection, this is just a test and I just want to get it to work:( — Lisa, Mar 06 '13 at 19:38
@Karolina Yes, you must set the variable `$query = "INSERT ..."` before you attempt to call `mysql_query()`. The error checking should go _after_ you call `mysql_query()` to test if it succeeded. (you stored the success/failure result in `$query_run`. Best to do your injection escaping in your test code, as test code has a bad tendency to move into production :) — Michael Berkowski, Mar 06 '13 at 19:41
@MichaelBerkowski hm I can´t get it to work. I did exactly like you said! — Lisa, Mar 06 '13 at 20:15
@Karolina Hard to say. Do you get an error from `mysql_error()`? Do you get PHP errors? — Michael Berkowski, Mar 06 '13 at 21:51
@MichaelBerkowski No errors... everything looks fine except that the information is not going into database.. I found another little snippet of code on the web and I´m getting that to atleast get the data into the database. But I´m still wondering why this code is not working... — Lisa, Mar 06 '13 at 21:55
@Karolina a last look makes me think it's because the variables `$username, $password` are not set, so the subsequent query isn't run. You have set `$firstname,$surname` from the form, but not those two vars. — Michael Berkowski, Mar 07 '13 at 02:33

score 3 · Answer 1 · edited May 23 '17 at 12:11

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.

You're welcome. Good luck! Feel free to come back at me. The example that I pointed out was specifically made for ajax, but the database functions can be resued for any database query'ing. — Jonast92, Mar 06 '13 at 19:56

SeanWM · Answer 2 · 2013-03-06T19:37:47.513

1

Hard to tell without seeing your schema but try this:

$query = "INSERT INTO `test`.`test_tabell` VALUES ('', '$firstname', '$surname')";
$query_run = mysql_query($query);

You're using backticks instead of apostrophes. Also, you're trying to execute a query before defining what the query is.

edited Mar 06 '13 at 19:37

answered Mar 06 '13 at 19:32

SeanWM

16,789
7
51
83

hjpotter92 · Answer 3 · 2013-03-07T08:03:29.913

1

Your insert query is wrong and also open to SQL injections. Here's how it should be:

$query = "INSERT INTO `test`.`test_tabell` 
    VALUES ('', '" . mysql_real_escape_string($firstname) . "', '" . mysql_real_escape_string($surname) . "')";

Notice the changing of all backticks to apostrophe.

Also, you're trying to execute the query before defining it.

EDIT

As per your information related to table definition, you can skip the id field from your table. The INSERT query will become:

$query = "INSERT INTO `test`.`test_tabell` (`FIRSTNAME`, `SURNAME`)
    VALUES ('" . mysql_real_escape_string($firstname) . "', '" . mysql_real_escape_string($surname) . "')";
$query_run = mysql_query( $query );

edited Mar 07 '13 at 08:03

answered Mar 06 '13 at 19:32

hjpotter92

78,589
36
144
183

To the -1'er, care to explain reason? – hjpotter92 Mar 06 '13 at 19:37
I'd like to know why mine is too. – SeanWM Mar 06 '13 at 19:38
3

@SeanWM Neither was mine, but based on long experience here I'm guessing your -1 is for not addressing the SQL injection and DreamEater's was for missing the fact that the query was executed before it was built. – Michael Berkowski Mar 06 '13 at 19:42
@MichaelBerkowski Yea. That is probably it. – Naftali Mar 06 '13 at 19:59
@DreamEater Thank you for your comment! I tried to do this exactly like you did but the data will still not go into the database:( – Lisa Mar 06 '13 at 20:16
@Karolina Please put your table schema in the question. To get the table schema, use `SHOW CREATE TABLE \`test\`.\`test_tabell\`` in terminal. – hjpotter92 Mar 06 '13 at 20:18
@DreamEater Hi, I´m feeling very stupid right now but I don´t know how to do that:( It´s just the simplest table really. ID field unsigned, INT, Auto indent and then the FIRSTNAME and SURNAME is just Varchar utf8 swedish ci. I´m going crazy, the data just wont go into the database... – Lisa Mar 06 '13 at 21:23
@Karolina Then you don't need to supply **ANYTHING** for the `id` field. Check the edit. – hjpotter92 Mar 07 '13 at 08:01

score 0 · Answer 4 · answered Mar 06 '13 at 19:38

0

As posted in the comments, you REALLY SHOULD NOT use/learn/practice using any function that starts with "mysql_" since it will NOT work as soon as PHP is updated. These functions are on their way out. Best of luck with learning to use PHP and SQL databases - just make sure you're learning something that will be useful in the future. Make sure to read up on Object Oriented Programming (OOP) in relation to PHP and both the PDO and mysqli_* functions.

answered Mar 06 '13 at 19:38

thatthatisis

783
7
18

Thank you! I will read up on that for sure. But for now all I want is for this code to do what I want it to do. And it just doesn´t work:( – Lisa Mar 06 '13 at 20:12

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

4 Answers4

EDIT

Linked