Unable to add (INSERT) user to database :(

[matthayzon89]

Hello,
I am new to php and mysql and I have been working on creating a form using php and my add_user function should work when I look through the code, but I am just unable to get it to add users to the database and cant understand why its not working.

I pasted my code below.

When I print out all the user form data using $_POST['whatever'] it prints out completely fine and I am able to get the variables to the function 100%. My mysql_query function looks fine I think. Is there anything I am doing wrong? any idea why this I am unable to insert users to my DB?

I am not getting any errors... however, in my code I have an if statement, that always evaluates to 'fail', it looks something like this:

if(mysql($query, $conn)){
 echo 'success';
}
else{
 echo 'fail';
}

Thanks for the help.

function add_user(){

$conn = mysql_connect('localhost', 'root', '1likebeans');

mysql_select_db('zees', $conn);

if((!isset($_POST['age']) and empty($_POST['age']))){
$_POST['age']="";
}
if((!isset($_POST['hobbies']) and empty($_POST['hobbies']))){
$_POST['hobbies']="";
}

echo $_POST['userid'] . ' ' . $_POST['password'] . ' ' . $_POST['email'] . ' ' . $_POST['age'] . ' ' . $_POST['hobbies'];


$query = "INSERT INTO users (username, password, email, age, hobbies)
VALUES ('$_POST[userid]', '$_POST[password]', '$_POST[email]', '$_POST[age]', '$_POST[hobbies]')";


mysql_query($query, $conn);

if(mysql_query($query, $conn)){
echo 'success';
}
else{
echo 'fail';
}
mysql_close($conn);
}

[penders]

mysql_query($query, $conn);
if(mysql_query($query, $conn)){

You are executing the query twice. The second call is going to fail. But the record is not actually in the DB after the first call?

[matthayzon89]

The record is NOT in the db with or without the second sql query call... I thought the same thing and I tried removing the first mysql query call but I get the same result... no record added...


thanks for the reply. I removed the first mysql_query permanently... but its still not working:( Any other ideas?

[penders]

I was going to suggest checking to see if any errors are returned from MySQL after attempting to execute your query, as you don't appear to be doing this...

echo mysql_error($conn);

But I guess from your other thread [webmasterworld.com] that you are now inserting new user records into your DB OK? What was the problem?

[rocknbil]

By all means add mysql_error() and also try this . . .

$query = "INSERT INTO users (username, password, email, age, hobbies)
VALUES ('"
mysql_real_escape_string($_POST['userid']) . "','" .
mysql_real_escape_string($_POST['password']) . "','" .
mysql_real_escape_string($_POST['email']) . "','" .
mysql_real_escape_string($_POST['age']) . "','" .
mysql_real_escape_string($_POST['hobbies'] . "')";

Two things relevant: if you have any quotes and possibly other special characters in your input, it can generate an error. For example if you have "don't know" in "hobbies" the eventual select will look like this:

.. ','don't know' ...

which will cause mysql to think the field ends at "don" and doesn't know what to do with the rest.

The second (and not quite as relevant but may still be a problem) is these may be interpreted as constants

$_POST[userid]

So you should use the concatenation methods above.

Although it's not the cause of the problem, you should always use mysql_escape_string() or mysql_real_escape_string() on any database queries to close one more point of mysql injection. In this case it might actually help the problem.

[matthayzon89]

Thanks a bunch for the advice everyone! I ended up finding the problem...

The age text area was set up to take in an integer... if it was left blank or if a character was inserted there it messed everything up.

I changed age it to a input setting to a varchar and now it all works perfecto.

Thanks again! ;)

[topr8]

>>I changed age it to a input setting to a varchar and now it all works perfecto.

not the right way to do it.
you should check for an integer, if a non integer is entered into the text field, then you should make them fill it in again.
if age is not required then if they don't enter anything you should insert NULL into that field in the db.
one way to help users in the original form would be to have a drop down with the ages rather than a text field (you should still test the submitted value though)

[penders]

Bit of a side issue, but why 'age' and not 'date of birth'? Just curious.

[matthayzon89]

topr8 I appreciate your advice but I tried to insert null in place of an empty age field before I changed it to a varchar. That does not work.

I planning on changing many things about my website in the near future, but as of right now I am just trying to make it work. This website isn't on the internet yet, I am just running it on localhost, sort of for practice..

Again thanks for the advice.

[penders]

topr8 I appreciate your advice but I tried to insert null in place of an empty age field before I changed it to a varchar. That does not work.

It should. Assuming the field in the database is configured to allow NULL (ie. has not been created as 'NOT NULL') then you should be able to assign NULL to it. Note that this is NULL, the keyword, without surrounding quotes (which would make it a string).