[RESOLVED] More Insert Problems

[system_critical]

Hi,

I am once again having INSERT INTO problems. Sorry, I really am a noob. I keep getting stumped. I am creating a basic user system for a project I'm working on. In previous script a private key was created and stored in a database and the user's email place holds their future username. So I have a table row like [e-mail], blank column for password and then randomly generated private key. They are sent the key with an invite email. When the users sign up for the first time, I want to search for the private key and fill in user name and password (provided username is unique). Username and private key are UNIQUE columns. My first question is if INSERT INTO will overwrite previous entries if directed so? Next I get a strange error in my INSERT INTO syntax, but I think I'm doing it correctly. Here is the code (private key is a string):

<?php
	$user_name = $_POST['user_name'];
	$user_pass = $_POST['password'];
	$private_key = $_POST['private_key'];

$my_id = "my_name";
$my_pass = "my_pass";
$my_db = "my_db";

$connect = mysql_connect('localhost', $my_id, $my_pass);
$select = mysql_select_db($my_db);

// FIRST WE CHECK THE PRIVATE KEY

	if($select){
$query = "INSERT INTO my_table (user_id, password) VALUES('$user_name', '$password') WHERE private_id = '$private_key'";
$result = mysql_query($query);
	if($result){
print("Registration Sucess");
}
		else{
	echo $result;
	$error = mysql_error();
	echo $error;

if(mysql_errno() == 1062){ // duplicate key error 
print("<span class='warning'>Username is already taken.</span>");
   }
   else{
   print("<span class='warning'>Private Key is invalid.</span>"); 	


}
  }
}
?>  

This Flags error:

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 'WHERE private_id = '7162a14b52457'' at line 1

When I manually re-entered the query in PHP MyAdmin with the values replacing the variables I still got the same error. I don't know what's wrong.

Thank you very much for the patience and help. Perhaps one day I will not be a noob anymore.

[Weedpacket]

You don't want INSERT. Look up UPDATE instead.

[djjjozsi]

$query = "UPDATE my_table SET user_id='$user_name', SET password='$password' WHERE private_id = '$private_key'";

but $username rather not used to say user_id !

And what if the username doesn't exist in the database? lets handle the update result...

system_critical;10890311 wrote:

Hi,

I am once again having INSERT INTO problems. Sorry, I really am a noob. I keep getting stumped. I am creating a basic user system for a project I'm working on. In previous script a private key was created and stored in a database and the user's email place holds their future username. So I have a table row like [e-mail], blank column for password and then randomly generated private key. They are sent the key with an invite email. When the users sign up for the first time, I want to search for the private key and fill in user name and password (provided username is unique). Username and private key are UNIQUE columns. My first question is if INSERT INTO will overwrite previous entries if directed so? Next I get a strange error in my INSERT INTO syntax, but I think I'm doing it correctly. Here is the code (private key is a string):

<?php
	$user_name = $_POST['user_name'];
	$user_pass = $_POST['password'];
	$private_key = $_POST['private_key'];

$my_id = "my_name";
$my_pass = "my_pass";
$my_db = "my_db";

$connect = mysql_connect('localhost', $my_id, $my_pass);
$select = mysql_select_db($my_db);

// FIRST WE CHECK THE PRIVATE KEY

	if($select){
$query = "INSERT INTO my_table (user_id, password) VALUES('$user_name', '$password') WHERE private_id = '$private_key'";
$result = mysql_query($query);
	if($result){
print("Registration Sucess");
}
		else{
	echo $result;
	$error = mysql_error();
	echo $error;

if(mysql_errno() == 1062){ // duplicate key error 
print("<span class='warning'>Username is already taken.</span>");
   }
   else{
   print("<span class='warning'>Private Key is invalid.</span>"); 	


}
  }
}
?>  

This Flags error:

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 'WHERE private_id = '7162a14b52457'' at line 1

When I manually re-entered the query in PHP MyAdmin with the values replacing the variables I still got the same error. I don't know what's wrong.

Thank you very much for the patience and help. Perhaps one day I will not be a noob anymore.

[system_critical]

Using the above syntax for an update, which I think is correct, I got the following error when running the script and manually executing the query in PHP MyAdmin.

check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET password='longshort' WHERE private_id = '7162a14b52457'' at line 1

While running the script, the variables seem to be coming through just fine and I looked up the syntax and this looks right. I'm inclined to think it's something to do with my table.

I have four columns:

id_num

• INT

• PRIMARY KEY

• NOT NULL

• auto_increment

user_id

• VARCHAR(15)

• NOT NULL

• UNIQUE

password

• VARCHAR(15)

• NOT NULL

private_id

• VARCHAR(35)

• NOT NULL

• UNIQUE

Any ideas? Thanks.

[Weedpacket]

The second SET is a typo.

[system_critical]

Thank you once again Weedpacket. You're right their is no second SET in the MYSQL manual. Now it works.