MySQL error

Anon

hello,
i've been stuck trying to debug an SQL error i keep getting
ERROR 1064 you have an error in your SQL syntax near 'WHERE custid = 'scotty" at line 1

i've looked in my code but cant get rid of it.
can anyone please help as i've been stuck on this for 2 hours

the link is http://netlab.csd.anglia.ac.uk/~csd99rwp/project/userman_pr.php

you can view the records but when you VIEW them and try to change the details the error appears
many thanks

Richard

Anon

Send me your complete SQL statement and I will fix it. Just seeing the WHERE clause is not enough to correct this problem. If you have additional PHP code that is associated with this, it may be helpful to include as well. Also, I noticed that your ID field is text based and is related to the person's name. That is probably not the best way to go about it. I recommend using an MD5 key or a simple autonumber through MySQL. If you would like help with that, let me know.

Aaron Zoller

www.AaronZoller.com
"My website will be opening in June so check back then."

Anon

Two thoughts:

1) is your field called custid or CustomerID?

2) verify it's not a case sensitivity issue.

Hope that helps -

Dan H

Anon

here is the code for the edit record function you asked me to send.
hope it makes sense to you!!

function edit_record()
{
global $default_dbname, $cust_tablename;
global $custid, $new_custid, $custid, $firstname, $custpassword;
global $title, $email, $registerdate;

if(empty($custid)) error_message('Empty Customer ID!');

$link_id = db_connect($default_dbname);
if(!$link_id) error_message(sql_error());

$field_str = '';
if($custid != $new_custid) $field_str = " custid = '$new_custid', ";
if(!empty($custpassword))
{
$field_str .= " custpassword = password('$custpassword'), ";
}
$field_str .= " firstname = '$firstname', ";
$field_str .= " title = '$title', ";
$field_str .= " email = '$email', ";
$field_str .= " registerdate = '$registerdate', ";

$query = "UPDATE $cust_tablename SET $field_str WHERE custid = '$custid'";

$result = mysql_query($query);
if(!$result) error_message(sql_error());

$num_rows = mysql_affected_rows($link_id);
if(!$num_rows) error_message("Nothing changed!");

if($custid != $new_custid)
{
$query = "UPDATE $access_log_tablename SET custid = '$new_custid'
WHERE custid = '$custid'";
$result = mysql_query($query);
if(!$result) error_message(sql_error());

user_message("All records regarding $custid have been changed!", 
             "$PHP_SELF?action=view_record&custid=$new_custid");
}

else
{
user_message("All records regarding $custid have been changed!");
}

}

Anon

My field is set to binary.
is that going to cause any problems?

Anon

Here is the problem.

$field_str .= " firstname = '$firstname', ";
$field_str .= " title = '$title', ";
$field_str .= " email = '$email', ";
$field_str .= " registerdate = '$registerdate', ";

Look at the last thing you did to $field_str. You added a ", " to it but there is no additional field_str so that is why you get an error. Strip the last ", " from the field_str and it work fine. Let me know if not and I will look at the code some more.

Aaron Zoller
ww.AaronZoller.com