We help IT Professionals succeed at work.

Weird problem when adding to mySQL database

Robert_
Robert_ asked
on
Ok I'll try to explain this.
I've just started (yesterday) learning PHP/mySQL and I've done a simple "add comments" page and now I'm trying to do a account creation page where users can create a new account. The source code for that page is below.

I'm running Apache as a service in win2k together with PHP och mySQL. Any user that comes to the site from outside can add their names etc to the database without any problems. I however when accessing the page from the computer running the server run into weird problems. If the database is completely empty when I try to add a user to the database it works, kinda. That is, password and email is added to the database but the username remains blank. And then if I try to add another user it says that this username allready exist although I'm absolutely sure that it doesn't. The page where I you can add comments works fine from this computer, it adds everything to the database just as it should. But this new "create account" page is giving me a hard time.

I can't give you a link to try it out because my computer wont be online over the weekend. But below is the source code for the page. Oh and don't bother telling me it looks like crap cause I know that. I've only been doing this for 15 hours or so :)

Hope someone can make sense of what I'm trying to say here...

Source code:
__________________________________________________________
<!--
All sourcecode and scripts are (c) Robert Orneteg 2002
//-->

<html>
<head>
     <title></title>

<?php
if($submitNewUser=="Create"){


     $connect=mysql_connect("???????", "????????", "??????????");
          if(!$connect){
               echo("Could not connect to database server at this time, please try again later.");
               exit();
          }
          if(!@mysql_select_db("gbook")){
               //echo("<P>Databasen g?r inte att n?... FAN!</P>");
               exit();
          }else{
               $checkUser = "SELECT * FROM users WHERE username='".$username."' LIMIT 1";
               $users = "SELECT * FROM users";
               $resultat = mysql_query($users);
                    while($row = mysql_fetch_array($resultat)){
                         echo("user: ".$row['username']."<br>pass: ".$row['userpass']."<br>email: ".$row[eMail]."<br><br>");
                    }
               $result = mysql_query($checkUser);
               $num_rows = mysql_num_rows($result);
                    if($num_rows!=0){
                         ?><script language="javascript">alert("User already exist");</script><?php
                    }else{
                         $addUser = "INSERT INTO users SET username='".$username."', userpass='".$userpass."', eMail='".$eMail."', joinDate='".time()."'";
                              if(mysql_query($addUser)){
                                   echo("User Added Succesfully");
                              }else{
                                   echo("User could not be added".mysql_error());
                                   exit();
                              }
                    }
               }
}
?>
     
</head>
<body>
<form method="get" action="<?php echo($PHP_SELF); ?>">
     User Name: <input type="text" name="username"><br>
     User Password: <input type="text" name="userpass"><br>
     E-mail: <input type="text"  name="eMail" name="eMail"><br>
     <input type="submit" name="submitNewUser" value="Create">
</form>


</body>
</html>
Comment
Watch Question

Commented:
Change the line:
$addUser = "INSERT INTO users SET username='".$username."', userpass='".$userpass."',
eMail='".$eMail."', joinDate='".time()."'";

to:

$addUser = "INSERT INTO users(username, userpass, eMail, joinDate) VALUES(  '".$username."', '".$userpass."',
'".$eMail."', '".time()."'");
Richard QuadlingSenior Software Developer
BRONZE EXPERT

Commented:
This is a total guess, but is there ANY chance that username exists within the server environment already?

Could you try using user_name/user_pass?

Also, maybe a typo, you've got ...

E-mail: <input type="text"  name="eMail" name="eMail"><br>

I assume this should be ...

E-mail: <input type="text" name="eMail"><br>


Besides that, try showing the list of accounts AFTER you've added the account. Does it get added?

Regards,

Richard Quadling.

Author

Commented:
Thanks for the input.

logan69: That line just caused a parse error

RQuadling: It shouldn't be any duplicates this is basically all I've got :) As I said earlier I've just started learning this stuff.
I added the output of the names for that reason, to check if they got added properly. And they do get added, for everyone but me...

Maybe I wasn't clear enough. It works absolutely fine for everyone else using it. It just doesn't work when I try to use it on the machine running the server.
I have another similar script that ads comments/username/time to a database that looks basically the same. The only major difference is the check to see that the user doesn't already exist. And this can be run on this machine without any troubles. It's weird...

Author

Commented:
Forgot to mention. I tried echoing the values of $username $eMail and so forth after submitting the form. And every variable contains the value I've put into the form except for the username variable which remains empty.
I say again, it works fine for other people, just not for me.
Richard QuadlingSenior Software Developer
BRONZE EXPERT

Commented:
What happens if you try <form method="put" ...> instead?

Richard.

Commented:
Sorry I had an extra "

$addUser = "INSERT INTO users (username, userpass, eMail, joinDate) VALUES ( '".$username."', '".$userpass."', '".$eMail."', '".time()."');

Author

Commented:
No none of those worked. And logan69 that line should include an " at the end, the syntax highlighting in my editor showed me that, still it gives me parse errors.

Here's a link to the form: http://bender.homeftp.net:4224/testing/createAccount.php
It may or may not be up since it's on my machine and it's not on 24/7 (but close).

Thanks for the tips so far.

Commented:
Try

$addUser = "INSERT INTO users (username, userpass, eMail, joinDate) VALUES ( '".$username."', '".$userpass."',
'".$eMail."', now())";
Richard QuadlingSenior Software Developer
BRONZE EXPERT

Commented:
I did mean POST. Sorry!

Author

Commented:
Dang, you guys are fast! :)

logan, that line worked, so far as it did not create any errors.

And post is what I've been using earlier.

I saw that you had tried the script and obviously it worked since your names are there. It's really weird that it wont work for me, the username variable is always empty when I submit the form.
Senior Software Developer
BRONZE EXPERT
Commented:
Can you try changing the name of the field on the form and then using that field name in the SQL, rather than using "username".

Richard.

Commented:
You might want to addslashes on the username to stop quotes.

Author

Commented:
Ok, tried changing the column name to user_name in the database and changed all the corresponding values in the sourcecode. Still no luck... There's something really weird here.

Author

Commented:
logan, addslashes?
Richard QuadlingSenior Software Developer
BRONZE EXPERT

Commented:
Addslashes allows you to pass what would normally be considered string delimiters to an SQL server.

So, the name, Patrick O'Connor would have a problem if the server expected single quoted strings.

So, using addslashes creates an escaped string ...

Patrick O\'Connor

Which is much happier.

There are some settings in PHP for this.


get_magic_quotes_gpc
(3.0.6 - 3.0.18 only, PHP 4 >= 4.0.0)

get_magic_quotes_gpc --  Get the current active configuration setting of magic quotes gpc.
Description

long get_magic_quotes_gpc (void)


Returns the current active configuration setting of magic_quotes_gpc. (0 for off, 1 for on).

See also get_magic_quotes_runtime(), set_magic_quotes_runtime().


Richard.

Author

Commented:
magic_quotes_gpc == on
magic_quotes_runtime & magic_quotes_sybase == off

Still this shouldn't be the problem since O'Connor worked fine, and all the fake names I've put in have been in the format "123" "blaaah" etc. Good for future reference though.

Seems like I should dump the database/sourcecode and start over.

Author

Commented:
magic_quotes_gpc == on
magic_quotes_runtime & magic_quotes_sybase == off

Still this shouldn't be the problem since O'Connor worked fine, and all the fake names I've put in have been in the format "123" "blaaah" etc. Good for future reference though.

Seems like I should dump the database/sourcecode and start over.

Author

Commented:
Ok, I got it to work eventually. I dumped the database content, changed a few variable names and so on. I think it might have had something to do with the variable names that's why I give RQuadling the cred. Not a high grade though, since I'm not sure exactly what fixed it :)

I you feel this is not right feel free to contact me at: robert_orneteg@hotmail.com

Thanx for the input guys!
Richard QuadlingSenior Software Developer
BRONZE EXPERT

Commented:
Out of interest, can you add a phpinfo(); to the end of the test file?

Is there a username variable which is ALWAYS blank when you run the script and not present when a remote user access the script?

You may find that username is present and is readonly (at least from the POV of a form).

Suggestion only though.

Thanks for the points, sorry it was not THE answer.

Regards,

Richard Quadling.

Author

Commented:
Aah, you hit the right answer there Richard! Have a look under cookies and such here:
http://bender.homeftp.net:4224/testing/test.php

Author

Commented:
Heh, removed the link there, realised that posting all of that information on the net like that wasn't a bright idea...
Anyways, there seems to be a cookie somewhere that sets username to "". Guess that's what causes the trouble.

Author

Commented:
Just thought I'd post just what the h**l it was that went wrong here. I guess I made a stupid newbie mistake. I didn't know that PHP automatically "imported" cookies and assigned variables with values corresponding to the content of the cookies. Because of this a variable in my code got overwritten by the cookie and hence made my script behave strangely.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.