Writing to database (beginner)

Total beginner here. Trying to write to a database I created using phpMyAdmin.

I have 2 files. Here's the first, which calls the second on submit.

<form method='post' action='register2.php'>
Username: <input type='text' name='username' /><br />
Password: <input type='password' name='password' /><br />
Confirm Password: <input type='password' name='confirm' /><br />
<input type='submit' value='Register!' />
</form>

and here's the register2.php file

<?php
if($_POST) {
$username = $_Post['username'];
$password = $_POST['password'];
$confirm = $_POST['confirm'];
if($password != $confirm) { ?>
<span style='color:red'>Error: Passwords do not match!</span>
<?php } else {
$dbhost = 'localhost';
$dbuser = 'root@localhost';
$dbpass = '';
$dbname = 'gamedb';
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die ('Error connecting to mysql');
mysql_select_db($dbname);
$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s')",
mysql_real_escape_string($_POST['username']));
$result = mysql_query($query);
list($count) = mysql_fetch_row($result);
if($count >= 1) { ?>
<span style='color:red'>Error: that username is taken.</span>
<?php } else {
$query = sprintf("INSERT INTO users(username,password) VALUES ('%s','%s');",
mysql_real_escape_string($_POST['username']),
mysql_real_escape_string(md5($password)));
mysql_query($query) or die ('Error updating database');
?>
<span style='color:green'>Congratulations, you registered successfully!</span>
<?php
}
}
}
?>
<form method='post' action='register2.php'>Username: <input type='text' name='username' /><br />
Password: <input type='password' name='password' /><br />
Confirm Password: <input type='password' name='confirm' /><br />
<input type='submit' value='Register!' />

I successfully connect to the database, but neither query is working.

This code always produces a 0, even if I create an entry by hand in my database.
$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s')",
mysql_real_escape_string($_POST['username']));
$result = mysql_query($query);

and this code always returns 'Error updating database'
$query = sprintf("INSERT INTO users(username,password) VALUES ('%s','%s');",
mysql_real_escape_string($_POST['username']),
mysql_real_escape_string(md5($password)));
mysql_query($query) or die ('Error updating database');

I'm lost. Please help me find my way :).

Not to rain on your parade but this is not helpful in checking the errors:
mysql_query($query) or die ('Error updating database')
since it is only a string that you've entered to tell you something is wrong, but not actually what is wrong.

and this will help identify the errors:
mysql_query($query) or die ('Error updating database') . mysql_error()

the difference being that the function mysql_error will actually return the error message from the database for you.

The other thing that would be helpful is to add in some DEBUG.


$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s')",
mysql_real_escape_string($_POST['username']));
echo "<br>DEBUG SQL: " . $query . "<br>\n";
...
$query = sprintf("INSERT INTO users(username,password) VALUES ('%s','%s');",
mysql_real_escape_string($_POST['username']),
echo "<br>DEBUG SQL: " . $query . "<br>\n";
...

See if that shows you anything interesting.

Thanks guys! Just got home I'll put your responses to use. And you're not raining on my parade by helping :).

Alrighty we're getting somewhere. I've altered my register2.php to look like this:

<?php
if($_POST) {
$username = $_Post['username'];
$password = $_POST['password'];
$confirm = $_POST['confirm'];
if($password != $confirm) { ?>
<span style='color:red'>Error: Passwords do not match!</span>
<?php } else {
$dbhost = 'localhost';
$dbuser = 'root@localhost';
$dbpass = '';
$dbname = 'gamedb';
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die ('Error connecting to mysql');
mysql_select_db($dbname);
$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s')",
mysql_real_escape_string($_POST['username']));
echo "<br>DEBUG SQL: query = " . $query . "<br>\n";
$result = mysql_query($query);
echo "<br>DEBUG SQL: result = " . $result . "<br>\n";
list($count) = mysql_fetch_row($result);
if($count >= 1) { ?>
<span style='color:red'>Error: that username is taken.</span>
<?php } else {
echo "<br>DEBUG SQL: count = " . $count . "<br>\n";
$query = sprintf("INSERT INTO users(username,password) VALUES ('%s','%s');",
mysql_real_escape_string($_POST['username']),
mysql_real_escape_string(md5($password)));
echo "<br>DEBUG SQL: query = " . $query . "<br>\n";
mysql_query($query) or die ('Error updating database' .mysql_error());
?>
<span style='color:green'>Congratulations, you registered successfully!</span>
<?php
}
}
}
?>
<form method='post' action='register2.php'>Username: <input type='text' name='username' /><br />
Password: <input type='password' name='password' /><br />
Confirm Password: <input type='password' name='confirm' /><br />
<input type='submit' value='Register!' />
</form>

mysql_error() is saying "No database selected."

Do I need to change the path?

The title bar lists localhost / localhost / gamedb

I found the solution and wanted to share it with others. The solution is resetting your mysql root password. Follow these steps:

http://www.geekpedia.com/KB105_How-to-reset-your-MySQL-root-password-under-Windows.html

If you are not using windows, the steps may be different. Just make sure to stop all services if you are using WAMP (like me).

Hope this helps someone.

Why would resetting your root password have anything to do with the problem? If you had password issues you would have gotten an error at connection time, not on the query itself. You wouldn't have got either error message described in your first post if your password in your connection script was incorrect.

You might have, guelphdad, if you were not checking for an error when making the connection. "No database selected" would be correct, because indeed there's not even a connection. And where $connection was not a resource, that would also be correct, because after an unsuccessful connection attempt, $connection would be just false or null and indeed not a resource.

It may also be useful to know that I tried this solution first, in which the
FLUSH PRIVILEGES;
statement was used.

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-windows

But this didn't solve the problem (I think I messed up somewhere it kinda confused me lol). It actually made it so I couldn't get into phpMyAdmin.

Maybe a combination of the two solutions fixed the problem. Both resetting the password and flushing the privileges.