INSERT INTO table doesn't work???

Question

I found a tutorial from nettuts and it has a source code in it so tried implementing it in my site.. It is working now. However, it doesn't have a Registration system so I am making one. The thing is, as I have expected, my code is not working... It doesn't seem to know how to INSERT into the database. Here's the function that inserts data into the db.

function register_User($un, $email, $pwd) {
$query = "INSERT INTO users( username, password, email )
                VALUES(:uname, :pwd, :email)
                LIMIT 1";

if($stmt = $this->conn->prepare($query)) {
    $stmt->bind_param(':uname', $un);
    $stmt->bind_param(':pwd', $pwd);
    $stmt->bind_param(':email', $email);
    $stmt->execute();

    if($stmt->fetch()) {
        $stmt->close();
        return true;
    } else return "The username or email you entered is already in                      
                  use...";
}

}

I have debugged the connection to the database from within the class, it says it's connected. I tried using this method instead:

function register($un, $email, $pwd)
{
    $registerquery = $this->conn->query(
               "INSERT INTO users(uername, password, email) 
               VALUES('".$un."', '".$pwd."', '".$email."')");  
    if($registerquery)  
    {  
        echo "<h4>Success</h4>";  
    }  
    else  
    {  
        echo "<h4>Error</h4>";  
    }
}

And it echos "Error"... Can you please help me pen point the error in this??? :(

Answer 1

In the first example, it looks like this line might be the issue:

if ($stmt->fetch()) {

Usually, data manipulation queries (INSERT, UPDATE, DELETE, etc) do not return a result set which can be fetched. Usually, the $stmt->execute(); function would return the true or false value you're looking for.

In the second example, you spelled "username" incorrectly:

INSERT INTO users(uername, password, ...

Answer 2

I hope there is no error in the above code... better write your query as a string and send it as a parameter. some time there may be a problem happen in concatenation of string and its parameter variables.

also please check the field name is miss-spelled.

Answer 3

First example,

$query = "INSERT INTO users( username, password, email )
            VALUES(:uname, :pwd, :email)
            LIMIT 1";

Remove the "LIMIT 1", should be

$query = "INSERT INTO users( username, password, email )
            VALUES(:uname, :pwd, :email)";

"LIMIT" is not part of the query construct. Refer to the INSERT USAGE DOCUMENTATION for more info.

Second Example,

As already mentioned, you have a misspelling on the "username" field. You used "uername" instead. This will trigger an "Unknown column" error.

As a final note, make sure you read the error message cause it's where you first find clues on the problem. Also, try to test your query construct through a database client just to make sure you don't have errors on it.

Answer 4

Hi thanks to all who helped! :-) I will look into my first method and try to apply your corrections if I find time... For now I am using the second method which, apparently, I have misspelled a field.

Thanks again!