MySql insert problem

[Chuppa]

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:

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:

// 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) {
			// 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();

				// 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]

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:

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

to:

$q = "INSERT INTO User(Username,Password,Permission) VALUES(
            '" . $_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?).

 

[Kate]

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]

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]

Thanks for replies guys.

I printed the query, it looked as follows:

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.

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

Any ideas?

 

[yeppers]

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:

 $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:

$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]

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

 

[Chuppa]

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!