php/mysql: can't write to mysql database [SOLVED]

Lexion · 2010-01-10 16:00:52

I'm writing a login script using php and mysql. I got it to work on my server about a week ago, and then I set up apache, php and mysql on my netbook so that I could take my code with me and test it. Now it doesn't work. My registration script doesn't write to the mysql database but has no errors. Here is register.php:

<?php
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "swordfish");
define("DB_NAME", "users");
define("TBL_USERS", "users");

$connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME, $connection) or die(mysql_error());

function addUser($username, $password)
{
    global $connection;
    $password = md5($password);

    echo("adding $username,$password<br />");

    $q = "INSERT INTO " . TBL_USERS . " VALUES ('$username', '$password')";
    echo("query: $q<br />");
    $result = mysql_query($q, $connection);
    echo("$result<br />");
}

if (isset($_POST["reg"]))
{
    addUser($_POST["username"], $_POST["password"]);
    echo("<a href='index.php'>click here to login</a>");
}
?>

<html>
<head>
    <title>Register</title>
</head>
<body>
    <form method="Post" name="login">
        <input type="text", name="username" /> Username<br />
        <input type="text", name="password" /> Password<br />
        <input type="submit" name="reg", value="Register" />
    </form>
</body>
</html>

and here is the output (without the form):

adding lexion,6f1ed002ab5595859014ebf0951522d9
query: INSERT INTO users VALUES ('lexion', '6f1ed002ab5595859014ebf0951522d9')

Also, I tried manually adding the content to the database:

$ mysql -p -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version 5.1.42 Source distribution

Type 'help;' or '\h' for help.  Type '\c' to clear the current input statement.

mysql> users
       -> INSERT INTO users VALUES('lexion', 'foo')
       -> ^D
       -> Bye

I would assume that I got something wrong with the last bit, but the php script seems like it should work. Does anybody know why it doesn't?

Last edited by Lexion (2010-01-10 19:04:15)

Barrucadu · 2010-01-10 17:21:26

What is wrong with your PHP? Why do you think it is failing? An INSERT query doesn't return anything. Also, it's a good idea to specify which fields you are inserting into, unless you want to have to provide something for every field (tedious for tables with many fields with default values). eg:

$q = "INSERT INTO `" . TBL_USERS . "`(`username`, `password`) VALUES ('$username', '$password')";

As for your experiment with the mysql prompt; queries have to end with a semicolon. PHP is nice and hides that little detail from you.

edit: Also, you're echoing text out before the HTML starts. That won't produce valid HTML. I also noticed a few other things which I corrected; look at my comments:

<?php
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "swordfish");
define("DB_NAME", "users");
define("TBL_USERS", "users");

$connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME, $connection) or die(mysql_error());

function addUser($username, $password)
{
    global $connection;
    $password = md5($password);

    // echo("adding $username,$password<br />"); - Don't echo stuff before HTML starts.

    // Also, clean up user-supplied data before plugging it into a query unless you want to be vulnerable to SQL injection.
    $cleanusername = mysql_real_escape_string($username, $connection);
    $cleanpassword = mysql_real_escape_string($password, $connection); // Obviously you'd generally use some hashing algorithm like md5 or sha1 for passwords

    $q = "INSERT INTO `" . TBL_USERS . "`(`username`, `password`) VALUES ('{$cleanusername}', '{$cleanpassword}')"; // The backticks tell MySQL not to interpret any text within as a keyword (good for field names, eg a field called `date`. The curly brackets tell PHP that the stuff within refers to a variable; it's nice as PHP knows exactly what the variable name is with no possible ambiguity.
    // echo("query: $q<br />"); 
    $result = mysql_query($q, $connection);
    // echo("$result<br />"); - This won't do anything; in addition to INSERT queries not returning anything, the $result variable doesn't contain the results of the query, it's a pointer to them for use with mysql_result().
}
?>

<html>
<head>
    <title>Register</title>
</head>
<body>

<?php
if (isset($_POST["reg"]))
{
    addUser($_POST["username"], $_POST["password"]);
    echo("<a href='index.php'>click here to login</a>");
}
?>

    <form method="Post" name="login">
        <input type="text" name="username" /> Username<br />
        <input type="text" name="password" /> Password<br />
        <input type="submit" name="reg" value="Register" />
    </form>
</body>
</html>

<?php
mysql_close($connection); // Not strictly needed, as PHP will tidy up for you if you forget.
?>

Last edited by Barrucadu (2010-01-10 17:34:20)

Lexion · 2010-01-10 19:04:01

Thanks for the quick reply, it works now.

Arch Linux

#1 2010-01-10 16:00:52

php/mysql: can't write to mysql database [SOLVED]

#2 2010-01-10 17:21:26

Re: php/mysql: can't write to mysql database [SOLVED]

#3 2010-01-10 19:04:01

Re: php/mysql: can't write to mysql database [SOLVED]

Board footer