Page 1 of 2

Problem with Function

Posted: Tue Sep 05, 2006 4:53 am
by genista
Hi all,

I have a page that updates a user's details to the database, I have user validation etc, but the problem I am having is with the function that updates the database. I get a syntax error. I have laid out the code below for the page and then the function itself that gets called from an include:

Code: Select all

include details, session details above here.
if(isset($_POST["submit"])){    
    field_validator("username", $_POST["username"], "alphanumeric", 4, 15);
    field_validator("password", $_POST["password"], "string", 4, 10);
    field_validator("confirmation password", $_POST["password2"], "string", 4, 10);
    field_validator("first_name", $_POST["first_name"], "alphanumeric", 1, 15);
etc, etc. (Field Validator is a function, this works fine).

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

    //now we pass the query to the database 
	$result=mysql_query($query) or die("Could not get data.".mysql_error()); 

    //get the first (and only) row from the result 
    $row = mysql_fetch_array($result, MYSQL_ASSOC)

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

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'] : "", 
etc, etc

html below here.

The function that updates the database looks like so:

Code: Select all

function updateuser($username, $password, etc) {
$query = "UPDATE `users` SET `username` = '$username', `password` = '$password', `first_name`=$first_name, etc";

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

    return true;
} //

It is this last query that is causing the problem, any ideas on what I am doing wrong?

Posted: Tue Sep 05, 2006 4:58 am
by twigletmac
What is the error message?

Mac

Posted: Tue Sep 05, 2006 5:04 am
by genista
Here is the 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 ' `maiden_name`=, `last_name`=, `address_line1`=, address_line2=, town=, county=,' at line 1"

Posted: Tue Sep 05, 2006 5:06 am
by patrikG
use

Code: Select all

$query = "UPDATE `users` SET `username` = '$username', `password` = '$password', `first_name`='$first_name', etc";
make sure you have enclosed the values you want to write to the database with high-commata.[/syntax]

Posted: Tue Sep 05, 2006 5:08 am
by Weirdan
make sure you quote your data in sql query, i.e

Code: Select all

insert into .... `maiden_name`=$maiden_name /* <---- wrong */
insert into .... `maiden_name`='$maiden_name' /* <---- ok */

Posted: Tue Sep 05, 2006 6:15 am
by genista
Ok thats great!

The problem I have now though is that I seem to have my code on the updatedetails.php page in the wrong order as the details are not being posted to the database from this form.

Retrieve data:

Code: Select all

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

    //now we pass the query to the database 
	$result=mysql_query($query) or die("Could not get data.".mysql_error()); 

    //get the first (and only) row from the result 
    $row = mysql_fetch_array($result, MYSQL_ASSOC); 

    //now finally create variables with data we have got which will be used later 
    //we will set the value attribute of the appropriate form element to the value from the database 
    
        
    
    $username=$row['username']; 
    $password=$row['password']; 
    $first_name=$row['first_name'];
	$maiden_name=$row['maiden_name'];
Now post new data:

Code: Select all

if(isset($_POST["submit"])){    
field_validator("username", $_POST["username"], "alphanumeric", 4, 15);
    field_validator("password", $_POST["password"], "string", 4, 10);
    field_validator("confirmation password", $_POST["password2"], "string", 4, 10);
    field_validator("first_name", $_POST["first_name"], "alphanumeric", 1, 15);
    field_validator("maiden_name", $_POST["maiden_name"], "alphanumeric", 1, 15);


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'] : "");
Html under here.


So I have the data retrieve right, I am guessing I need to seperate this away from the update?

G

Posted: Tue Sep 05, 2006 6:25 am
by patrikG
judging by your previous post, you have already created a function updateuser() which does the SQL-update. To rectify the error you've had earlier, see Weirdan's an my post.
It's not quite clear what your current problem is: you are first retrieving user-details from the database and are then trying to post them?
Please be more specific about the problem you're encountering and what you actually want to do.

Posted: Tue Sep 05, 2006 9:39 am
by genista
The problem I have is that the page updatedetails.php displays the data from the database, but when I hit submit and then reload the page and look at the database nothing has been updated. In this case I suspect that the query itself maybe ok, but the page I posted at the top of this thread is the one causing the problem.

What was suggested by you has worked, I no longer have the mysql error.

G

Posted: Wed Sep 06, 2006 5:22 am
by genista
I have tried error reporting and that hasn't worked.

Any help would be appreciated,


Thanks,

G

Posted: Wed Sep 06, 2006 6:12 am
by rsmarsha
Echo out the variables you are trying to insert into the Db, to see if they are set and correct before insertion.

Posted: Wed Sep 06, 2006 6:36 am
by genista
Do you mean echo the updateuser function?

Posted: Wed Sep 06, 2006 6:51 am
by volka
$query = "UPDATE `users` SET `username` = '$username', `password` = '$password', `first_name`=$first_name, etc";
You have an error in your SQL syntax [...] near ' `maiden_name`=, `last_name`=, `address_line1`=, address_line2=, town=, county=,' at line 1"
...I wouldn't call that exactly etc ;)
Show us the complete update staterment if you may.

Posted: Wed Sep 06, 2006 6:55 am
by genista
Ok here is the query, but I have also posted below the first part of the function:

Code: Select all

function updateuser($username, $password, $first_name, $maiden_name, $last_name, $address_line1, $address_line2, $town, $county, $postcode, $daytime_phone, $mobile_phone, $evening_phone, $email_address) {

global $link;

$query = "UPDATE `users` SET `first_name`='$first_name', `maiden_name`='$maiden_name', `last_name`='$last_name', `address_line1`='$address_line1', `address_line2`='$address_line2', `town`='$town', `county`='$county', `postcode`='$postcode', `daytime_phone`='$daytime_phone', `mobile_phone`='$mobile_phone', `evening_phone`='$evening_phone', `email_address`='$email_address'";

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

    return true;
}

Posted: Wed Sep 06, 2006 7:07 am
by volka
This code should give you -if any- another error message.

Posted: Wed Sep 06, 2006 7:19 am
by genista
Ok so this code is not going to give me the error, can you please point me in a direction as all i want to do is validate the code and send it back to the database.

Thanks,

G