Function change (new user to update user)

genista

Hi all,

I have a function that helps to create a new user, the query looks like so:

 $query="INSERT INTO users (username, password, etc, etc)

Now I need a function that updates the user details, so when I call update user it updates the database with all of the vlaues. The problem I have is that when I run this updateuser function in any of my scripta I get:

"Died inserting login info into db. Error returned if any: 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 '(username, password"

Off the following change:

$query="UPDATE users (username, password, etc, etc where username=$username)

$username is defined in the script as I have already run a query to select * from the user table.

ANy ideas? (I hope it is a simple one)

Installer

You might want to check your update query syntax against that specified in the manual.

Piranha

UPDATE users SET password = $password, phone = $phone WHERE username = $username;

If you need to change the primary key the database is bad designed, and you should think about redesigning it. It seems that way since you use the username to decide where to change.

bradgrafelman

After you fix your UPDATE syntax, try surrounding column/table names in backticks () and string values in single quotes, as in:

INSERT INTO `example` (`username`, `column2`, `column3`) VALUES ('value1', 'value2', 35132, 45.12, 'etc.')

genista

Ok, as an update I have tried:

$query="UPDATE users SET(username, password,

and I have tried

$query="UPDATE users SET(username, password) where 'username' = $username;

I am a bit of noob so forgive me for not understanding you Piranha, all I want to do is update the user's details, does the code you posted relate to changing the primary key or is it the solution I am looking for?

bradgrafelman

Installer wrote:
You might want to check your update query syntax against that specified in the manual.

Did you not read Installer's post?

Here's an example from the manual (the page Installer linked to) even though I would add the backticks as suggested in my previous post:

UPDATE persondata SET age=age+1;

genista

Ok I tried that but it does not work and I think I need to be more specific with my post, the complete function looks like this:

function updateuser($username, $password, $first_name, $maiden_name, etc, etc)
global $link;

VALUES('$username', '$password', '$first_name', '$maiden_name', '$last_name',$query="UPDATE users SET(username, password, first_name, maiden_name, last_name, etc)";

$result=mysql_query($query, $link) or die("Died inserting login info into db.  Error returned if any: ".mysql_error());

return true;
} //

Sorry for any problem caused,

bradgrafelman

I really hate spoonfeeding code to people, but here's an example of what your query should look like:

$query = "UPDATE `users` SET `username` = '$username', `password` = '$password', ...";

You can fill in '...' with the rest.

EDIT: Also, if any of these variables has been taken from user input, I hope that it has been validated/sanitized... else you're probably vulnerable to SQL injection attacks.

genista

I appreciate you help, but.... entering:

$query = "UPDATE `users` SET `username` = '$username', `password` = '$password', `first_name`=$first_name, `maiden_name`=$maiden_name, `last_name`=$last_name, etc;

Gets me:
"check the manual that corresponds to your MySQL server version for the right syntax to use near ' maiden_name=, last_name=, address_line1=, address_line2=, town=,' at line 1"

Roger_Ramjet

Looks like you're doing a lot a validation on the user input there - I don't think.

Don't take any notice of people who tell you to use back-ticks around table and column names either. If you need them then it means that you have named your tables and columns badly to begin with. There is never any need to use reserved worrds or oddball characters in table and column names so there should never be any need to quote them.

There is a very definite need to put '' around string values though - and your script is not doing that either or it would not have failed with this error: it would have overwritten the existing values with empty strings instead so maybe you are lucky there.

Try reading about isset() to find out what you are doing wrong.

bradgrafelman

It also looks like the variables aren't being set. Where do you define $first_name, $maiden_name, etc. ?

genista

The code you are looking at is in the functions include, the variables are defined in updatedetails.php like so:

Retrieve data from database:

$id = $_SESSION['username'];
$query = "select * from users where username='$id'";


$result=mysql_query($query) or die("Could not get data.".mysql_error()); 


$row = mysql_fetch_array($result, MYSQL_ASSOC); 





$username=$row['username']; 
$password=$row['password']; 
$first_name=$row['first_name'];
$maiden_name=$row['maiden_name'];
etc, etc

Now I want to define variables and update database:

if(empty($messages)) {
        // registration ok, get user id and update db with new info:
        updateuser($strUsername = isset($_POST['username']) ? $_POST['username'] : "", 
		$strPassword = isset($_POST['password']) ? $_POST['password'] : "", 
		$strfirst_name = isset($_POST['first_name']) ? $_POST['first_name'] : "",
		$strmaiden_name = isset($_POST['maiden_name']) ? $_POST['maiden_name'] : "",

Please excuse the crude last bit that will be changed as I have a field validator above all of this...