MySql insert problem

Chuppa · 08-08-2006, 08:20 AM

Hi all,

I've made a newsletter subscription system for a website. It takes information from a small form and submits it into two tables in the database, one User table and one Userinfo table. It has always worked so far, but some time ago we moved to another hosting. Now, the script doesn't work properly anymore. I can't be completely sure it's because of the change of hosting, but it soiunds logical.

Anyway; here's the error message and code. I've thoroughly reviewed it and I can't see any mistakes or faulty code. Apparently; something's wrong with the first insertion. I've checked the second insertion and that one does work properly. Since both are the same, I have no idea why the first won't work.

Does anyone have a clue what the problem is? I'd be eternally grateful

.

Error message:

Code:

Something went wrong. Please try again. You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'User(Username,Password,Permission) VALUES( 'username','080f651e3f

PHP script:

PHP Code:

  // if the required fields have been filled out

if($_POST['form_user'] AND $_POST['form_pass'] AND $_POST['form_email']) {

 
    // if the passwords are equal

    if($_POST['form_pass'] == $_POST['form_pass2']) {

 
        // if user not already registered

        $q = "SELECT * FROM User WHERE Username = '" . $_POST['form_user'] . "'";

        $result = mysql_query($q);

        if(mysql_num_rows($result) == 0) {

????: NamePros.com http://www.namepros.com/programming/225527-mysql-insert-problem.html
            // insert the user information

            $pass = md5($_POST['form_pass']);

            $q = "INSERT INTO User(Username,Password,Permission) VALUES(

            '" . $_POST['form_user'] . "','" . $pass . "','1'

            )";

 
            if(mysql_query($q)) {

 
                $id = mysql_insert_id();

????: NamePros.com http://www.namepros.com/showthread.php?t=225527
 
                // insert the personal information

                $q = "INSERT INTO UserInfo(Name,UserSeq,Email,Country,Format,Newsletter,Promotion,UserType) VALUES(

                '" . $_POST['form_name'] . "','" . $id . "','" . $_POST['form_email'] . "','" . $_POST['form_country'] . "',

                '" . $_POST['form_format'] . "','" . $_POST['form_newsletter'] . "','" . $_POST['form_promotion'] . "','4'

                )";

                if(mysql_query($q)) {

                    // registration went ok. Email is sent.

TwistMyArm · 08-08-2006, 08:58 AM

Not knowing your database schema doesn't help, but I'm wondering: is your 'Permission' column a number type or a string type? I'm assuming it's a number type, in which case you shouldn't have those single quotes around the permission value you are trying to set. That could be a problem...

For example, change:

PHP Code:

  $q = "INSERT INTO User(Username,Password,Permission) VALUES(

            '" . $_POST['form_user'] . "','" . $pass . "','1'

            )";

to:

PHP Code:

  $q = "INSERT INTO User(Username,Password,Permission) VALUES(

????: NamePros.com http://www.namepros.com/showthread.php?t=225527
            '" . $_POST['form_user'] . "','" . $pass . "',1

            )";

If this fixes this particular issue, then I daresay you'll start getting errors elsewhere, whever you've added single quotes around number type values (eg. maybe your userType value?).

sdsinc · 08-08-2006, 09:59 AM

It could be a problem with magic_quotes not enabled on the new server
http://www.php.net/magic_quotes

Are you trying to insert a string which contains a ' by chance ?

iNod · 08-08-2006, 10:28 AM

Try printing your querys and see what you get?

I suspect there is a problem with your query somewhere.. Either a mis-named column or a missing one. I suggest you check your names and make sure everything is spelt right. Generally where it stops off is where the problem is.

- Steve

Chuppa · 08-08-2006, 05:45 PM

Thanks for replies guys.

I printed the query, it looked as follows:

PHP Code:

  INSERT INTO User(Username,Password,Permission) VALUES( 'tyu','af27bab84283536c346b97ced4bc5c58','1' )

The password, where the error messae stops, is simply an md5 code so it doesn't contain any quotes etc.

Twisty: the field is indeed an integer, so I shouldn't use the quotes. Still though, in other partsd of the script, I used quotes for integers as well and they don't give out an error. I'll check and see if it works, though.
????: NamePros.com http://www.namepros.com/showthread.php?t=225527

EDIT: Nope, that wasn't the problem. Using the code you suggested, it still gives out the same error message.

Any ideas?

BillyConnite · 08-08-2006, 06:38 PM

Well first off, MySQL considers columns with names Username and username to be different (notice the capitals, so I would siggest first removing the capitals from the column names, so your code would become this:
????: NamePros.com http://www.namepros.com/showthread.php?t=225527

Code:

 $q = "INSERT INTO User(username,password,permission) VALUES(
            '" . $_POST['form_user'] . "','" . $pass . "','1'
            )";

If that’s not the problem, try this, even though it may seem useless:

Code:

$pass = md5($_POST['form_pass']);
$fUser=$_POST['form_user']
$q = "INSERT INTO User(Username,Password,Permission) VALUES('" .$fUser . "','" . $pass . "','1')";

All the best, Rhett.

Noobie · 08-08-2006, 08:25 PM

if none of the above works, maybe password or permission is a reserved name

Chuppa · 08-18-2006, 05:40 PM

Thanks for the help guys, a friend of mine solved the problem. It turned out the User tablename is reserved on this server while it probably wasn't on the old.

Thanks for the support anyway!

08-08-2006, 08:20 AM	THREAD STARTER #1 (permalink)
Chuppa New Member Join Date: Jan 2006 Location: the Netherlands Posts: 7	MySql insert problem Hi all, I've made a newsletter subscription system for a website. It takes information from a small form and submits it into two tables in the database, one User table and one Userinfo table. It has always worked so far, but some time ago we moved to another hosting. Now, the script doesn't work properly anymore. I can't be completely sure it's because of the change of hosting, but it soiunds logical. Anyway; here's the error message and code. I've thoroughly reviewed it and I can't see any mistakes or faulty code. Apparently; something's wrong with the first insertion. I've checked the second insertion and that one does work properly. Since both are the same, I have no idea why the first won't work. Does anyone have a clue what the problem is? I'd be eternally grateful . Error message: Code: Something went wrong. Please try again. You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'User(Username,Password,Permission) VALUES( 'username','080f651e3f PHP script: PHP Code: // if the required fields have been filled out if($_POST['form_user'] AND $_POST['form_pass'] AND $_POST['form_email']) { // if the passwords are equal if($_POST['form_pass'] == $_POST['form_pass2']) { // if user not already registered $q = "SELECT * FROM User WHERE Username = '" . $_POST['form_user'] . "'"; $result = mysql_query($q); if(mysql_num_rows($result) == 0) { ????: NamePros.com http://www.namepros.com/programming/225527-mysql-insert-problem.html // insert the user information $pass = md5($_POST['form_pass']); $q = "INSERT INTO User(Username,Password,Permission) VALUES( '" . $_POST['form_user'] . "','" . $pass . "','1' )"; if(mysql_query($q)) { $id = mysql_insert_id(); ????: NamePros.com http://www.namepros.com/showthread.php?t=225527 // insert the personal information $q = "INSERT INTO UserInfo(Name,UserSeq,Email,Country,Format,Newsletter,Promotion,UserType) VALUES( '" . $_POST['form_name'] . "','" . $id . "','" . $_POST['form_email'] . "','" . $_POST['form_country'] . "', '" . $_POST['form_format'] . "','" . $_POST['form_newsletter'] . "','" . $_POST['form_promotion'] . "','4' )"; if(mysql_query($q)) { // registration went ok. Email is sent. __________________ www.theChuppa.com http://chuppa.3dcaffeine.com www.3i-design.nl

08-08-2006, 08:58 AM	#2 (permalink)
TwistMyArm NamePros Member Join Date: May 2006 Posts: 160	Not knowing your database schema doesn't help, but I'm wondering: is your 'Permission' column a number type or a string type? I'm assuming it's a number type, in which case you shouldn't have those single quotes around the permission value you are trying to set. That could be a problem... For example, change: PHP Code: `$q = "INSERT INTO User(Username,Password,Permission) VALUES( '" . $_POST['form_user'] . "','" . $pass . "','1' )";` to: PHP Code: `$q = "INSERT INTO User(Username,Password,Permission) VALUES( ????: NamePros.com http://www.namepros.com/showthread.php?t=225527 '" . $_POST['form_user'] . "','" . $pass . "',1 )";` If this fixes this particular issue, then I daresay you'll start getting errors elsewhere, whever you've added single quotes around number type values (eg. maybe your userType value?). __________________ Free Fuel Efficiency Calculator \| Buy CMS Templates

08-08-2006, 09:59 AM	#3 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,556	It could be a problem with magic_quotes not enabled on the new server http://www.php.net/magic_quotes Are you trying to insert a string which contains a ' by chance ? __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

08-08-2006, 10:28 AM	#4 (permalink)
iNod Eating Pie Join Date: Nov 2004 Location: Canada Posts: 2,272	Try printing your querys and see what you get? I suspect there is a problem with your query somewhere.. Either a mis-named column or a missing one. I suggest you check your names and make sure everything is spelt right. Generally where it stops off is where the problem is. - Steve __________________ I feel old.

08-08-2006, 05:45 PM	THREAD STARTER #5 (permalink)
Chuppa New Member Join Date: Jan 2006 Location: the Netherlands Posts: 7	Thanks for replies guys. I printed the query, it looked as follows: PHP Code: `INSERT INTO User(Username,Password,Permission) VALUES( 'tyu','af27bab84283536c346b97ced4bc5c58','1' )` The password, where the error messae stops, is simply an md5 code so it doesn't contain any quotes etc. Twisty: the field is indeed an integer, so I shouldn't use the quotes. Still though, in other partsd of the script, I used quotes for integers as well and they don't give out an error. I'll check and see if it works, though. ????: NamePros.com http://www.namepros.com/showthread.php?t=225527 EDIT: Nope, that wasn't the problem. Using the code you suggested, it still gives out the same error message. Any ideas? __________________ www.theChuppa.com http://chuppa.3dcaffeine.com www.3i-design.nl

08-08-2006, 06:38 PM	#6 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,456	Well first off, MySQL considers columns with names Username and username to be different (notice the capitals, so I would siggest first removing the capitals from the column names, so your code would become this: ????: NamePros.com http://www.namepros.com/showthread.php?t=225527 Code: $q = "INSERT INTO User(username,password,permission) VALUES( '" . $_POST['form_user'] . "','" . $pass . "','1' )"; If that’s not the problem, try this, even though it may seem useless: Code: $pass = md5($_POST['form_pass']); $fUser=$_POST['form_user'] $q = "INSERT INTO User(Username,Password,Permission) VALUES('" .$fUser . "','" . $pass . "','1')"; All the best, Rhett. __________________ Free Forums / GoDaddy Coupon Codes (NEW DOMAIN!) / Free Arcade Script / <?='Your computer is '.(1?fine:broken).'.'?>

08-08-2006, 08:25 PM	#7 (permalink)
Noobie NamePros Regular Join Date: Feb 2006 Location: Montreal, Quebec, Canada Posts: 324	if none of the above works, maybe password or permission is a reserved name __________________ Goldkey.com is a scam What's your BMI? \| Timestamp Generator

08-18-2006, 05:40 PM	THREAD STARTER #8 (permalink)
Chuppa New Member Join Date: Jan 2006 Location: the Netherlands Posts: 7	Thanks for the help guys, a friend of mine solved the problem. It turned out the User tablename is reserved on this server while it probably wasn't on the old. Thanks for the support anyway! __________________ www.theChuppa.com http://chuppa.3dcaffeine.com www.3i-design.nl

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)