Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Error inserting into database

[Jeanco]

Hello,

It's been a while since I've used a mySQL database, and unfortunately my handy reference book isn't with me. Basically, I'm wondering why I'm getting an error when I run the insert query seen below (note this is only the snippet):

PHP Code:

    //if all is good, send it to the database
    if ($valid) {
        $email = $_POST["email"];
        $password = md5($_POST["password"]);
        $alias = $_POST["alias"];
        $fanme = $_POST["fname"];
        $lname = $_POST["lname"];
        $address1 = $_POST["address1"];
        $address2 = $_POST["address2"];
        $city = $_POST["city"];
        $province = $_POST["province"];
        $postal = $_POST["postal"];
        $country = $_POST["country"];
        $bdate = $_POST["month"] . "/" . $_POST["day"] . "/" . $_POST["year"];
        $mailinglist = $_POST["mailinglist"];        

        echo $email . "<br>" . $password . "<br>" . $alias . "<br>" . $fname . "<br>" . $lname . "<br>" . $address1 . "<br>" . $address2 . "<br>" . $city . "<br>" . $province . "<br>" . $postal . "<br>" . $country . "<br>" . $bdate . "<br>" . $mailinglist . "<br>";
        
        $query = "INSERT INTO users (email, password, alias, fname, lname, address1, address2, city, province, postal, country, bdate, mailinglist) VALUES ('$email', '$password', '$alias','$fname', '$lname', '$address1', '$address2', '$city', '$province', '$postal', '$country', '$bdate', '$mailinglist')";
        
        mysql_query($query) or die('Error, insert query failed');

        $query = "FLUSH PRIVILEGES";
        mysql_query($query) or die('Error, flush failed');
         
        echo "User Added"; 
    
    } 


Please note that I connect to the database fine, its just inserting thats causing a problem at the moment. The database is set with the following fields. All fields are text and not null with the exception of ID, which is not null, primary key, auto-increment, and integer type.

ID (primary key and set to auto-increment)
email
password
alias
submissions
fname
lname
address1
address2
city
province
postal
country
bdate
mailinglist

Thanks in advance.

[ThatScriptGuy]

Not 100% sure on this one, but try removing the single quotes from the values. I don't think you need them since they are inside the interpreted double quotes of the query.

[Jeanco]

I'm afraid thats not it.

Any other ideas?

[zoid]

This is just a guess, but it might be the format of $bdate which might not be compatible with MySQL's date type (given its not a varchar column).

Which error do you actually get (-> mysql_error())?

[saidev]

Not sure how your server is setup, but here is something else to try, call addslashes() to take care of all the escape characters.
i.e.
$alias = addslashes($_POST["alias"]);
$fanme = addslashes($_POST["fname"]);

[kajakske]

Another hint is to actually display what error you get ... it will most likely tell you alot more than we can.

And right before executing it, print out $query, so you see what gets sent to the SQL server.

[Oras]

You forget the ID in the query
This code should work:

PHP Code:

//if all is good, send it to the database 
    if ($valid) { 
        $email = $_POST["email"]; 
        $password = md5($_POST["password"]); 
        $alias = $_POST["alias"]; 
        $fanme = $_POST["fname"]; 
        $lname = $_POST["lname"]; 
        $address1 = $_POST["address1"]; 
        $address2 = $_POST["address2"]; 
        $city = $_POST["city"]; 
        $province = $_POST["province"]; 
        $postal = $_POST["postal"]; 
        $country = $_POST["country"]; 
        $bdate = $_POST["month"] . "/" . $_POST["day"] . "/" . $_POST["year"]; 
        $mailinglist = $_POST["mailinglist"];         

        echo $email . "<br>" . $password . "<br>" . $alias . "<br>" . $fname . "<br>" . $lname . "<br>" . $address1 . "<br>" . $address2 . "<br>" . $city . "<br>" . $province . "<br>" . $postal . "<br>" . $country . "<br>" . $bdate . "<br>" . $mailinglist . "<br>"; 
         
        $query = "INSERT INTO users (ID,email, password, alias, fname, lname, address1, address2, city, province, postal, country, bdate, mailinglist) VALUES (NULL,'$email', '$password', '$alias','$fname', '$lname', '$address1', '$address2', '$city', '$province', '$postal', '$country', '$bdate', '$mailinglist')"; 
         
        mysql_query($query) or die('Error, insert query failed'); 

        $query = "FLUSH PRIVILEGES"; 
        mysql_query($query) or die('Error, flush failed'); 
          
        echo "User Added";  
     
    } 


[orbitz]

Originally Posted by Oras

You forget the ID in the query
This code should work:

This is an insert query - I suppose he set Id as a auto increment. No Id is needed.

For the code, print out $query and also the mysql_error() like others had previously suggested.

[Burhan]

No, no a few things wrong here - but not what you are guessing

Column names must be encapsed in ` ` if you use a reserved word (such as password) as a column name.

Also, instead of sitting here guessing about it, learn to love mysql_error().

You don't need to use FLUSH PRIVILEGES, as you are not modifying the MySQL user database, but your own. Also, FLUSH PRIVILEGES requires root-level permissions on MySQL.

Also -- and it seems I keep saying this on every MySQL-related thread -- ALWAYS USE mysql_real_escape_string() on all user submitted values!

[Jeanco]

Thanks fyre.

Read up on the escpae string function and its now in full use on the site.