Strange problem with PHP5/MySQL update function

gabebster · 04-05-2006, 01:12 AM

I am having a strange problem with one of my scripts. I have a function that updates a user on my user table. The problem is that I call the function from one script and it executes fine, then I call it from another script and It gives me this error "DB Error: syntax error".

My function looks like this:

PHP Code:


			
static function updateUser($user)

    {



        $update = "UPDATE UserTable SET Password = '".$user->getPassword()."', Fname = '".$user->getFname()."', Lname = '".$user->getLname()."', EmailAdd = '".$user->getEmailAdd()."', Gender = '".$user->getGender()."', RegDate = '".$user->getRegDate()."', UserStatus = '".$user->getUserStatus()."', BanStatus = '".$user->getBanStatus()."', Notification = '".$user->getNotificationStatus()."', Image = '".$user->getUserImage()."', NumOfPosts = ".$user->getNumOfPosts().", MemberNum = ".$user->getMemberNum().", Privilege = '".$user->getPrivileges()."' WHERE Username = '".$user->getUsername()."'";

        echo "\$update: $update";



        $db = getConnection();



        $result = $db->query($update);

        // check that result was ok

        if(DB::isError($result))

        {

            echo $result->getMessage();

            exit;

        }



        $db->disconnect();



        return $result;

    }

This "echo "\$update: $update";" will output:

Code:

$update: UPDATE UserTable SET Password = 'password', Fname = 'First', Lname = 'Last', EmailAdd = 'email@email.com', Gender = 'M', RegDate = '2006-03-16 13:37:42', UserStatus = 'OFFLINE', BanStatus = 'N', Notification = 'Y', Image = 'none', NumOfPosts = 22, MemberNum = 2, Privilege = 'NONE' WHERE Username = 'username'

If I copy and paste that output it will execute just fine from the MySQL prompt. I also call this function from another script with no problems either.

Any help would be appreciated.

paulsm4 · 04-06-2006, 12:02 AM

Potentially dumb question on my part: but don't you need a semicolon (";") to terminate your mySQL line?

gabebster · 04-06-2006, 11:52 AM

You do when you are using the MySQL prompt directly. When you send a query to MySQL, like through php, a semicolon is not needed at the end of it.

paulsm4 · 04-06-2006, 12:18 PM

Yes, that's my experience too: the semicolon shouldn't be necessary (at least not in any of the cases I've used mySQL and PHP).

Nevertheless:
1. You've satisfied yourself that your PHP is actually connecting to the database (the right database, the right user, and the right permissions).

2. You've satisfied yourself that the PHP is generating the "update" string correctly (you tried cutting/pasting your "echo", and it worked).

3. You can't think of any other variables (neither can I)

... so ...

4. You did at least make the *effort* to try adding the semicolon. Didn't you?

xhi · 04-06-2006, 12:30 PM

how is the usertable setup? can you post a dump of the describe

graemef · 04-06-2006, 02:11 PM

What does your function getConnection() look like?

gabebster · 04-06-2006, 03:18 PM

Here is the dump of describe.

Code:

mysql> describe UserTable;
+--------------+-------------+------+-----+---------------------+-------+
| Field        | Type        | Null | Key | Default             | Extra |
+--------------+-------------+------+-----+---------------------+-------+
| Username     | varchar(15) |      | PRI |                     |       |
| Password     | varchar(40) | YES  |     | NULL                |       |
| Fname        | varchar(25) | YES  |     | NULL                |       |
| Lname        | varchar(25) | YES  |     | NULL                |       |
| MemberNum    | int(11)     |      |     | 0                   |       |
| EmailAdd     | varchar(50) |      |     |                     |       |
| Gender       | char(1)     |      |     |                     |       |
| RegDate      | datetime    |      |     | 0000-00-00 00:00:00 |       |
| Privilege    | varchar(5)  |      |     |                     |       |
| UserStatus   | varchar(7)  |      |     |                     |       |
| BanStatus    | char(1)     |      |     |                     |       |
| Notification | char(1)     |      |     |                     |       |
| Image        | varchar(20) |      |     |                     |       |
| NumOfPosts   | int(11)     |      |     | 0                   |       |
+--------------+-------------+------+-----+---------------------+-------+

My connect function looks like this:

PHP Code:


			
function getConnection()

{

        $user = ''; //for my eyes only

        $pass = '';

        $host = 'localhost';

        $db_name = 'Forums';



        //set up universal connection string

        $dsn = "mysqli://$user:$pass@$host/$db_name";



        // connect to database

        $db = &DB::connect($dsn);



        if(DB::isError($db))

        {

                echo $db->getMessage();

                exit;

        }

        else

        {

                return $db;

        }

}

I added a semicolon and no change, I still get the same error.

Thanks for the responses.

paulsm4 · 04-06-2006, 04:59 PM

Don't know - you've already been doing most of the stuff I would have suggested.

A few of other (clearly-grasping-at-straws ;-)) suggestions:

1. Please consider adding/removing columns from your "update" to figure out if there's any one, particular field mySQL is being cranky about.

2. Please consider writing a standalone test program (independent of the rest of your app) that
does a similar "connect" and "query".

3. If still no-go, consider modifying your test program to use the old-fashioned "mysql_connect()" and mysql_query()" APIs instead of DB.

4. Run "phpinfo()" to see if there's anything weird about your site configuration.

Sorry I can't be of more help (and thanx for humoring me with the semi-colon thing ;-))

Sincerely .. PSM

graemef · 04-06-2006, 05:12 PM

I can't see anything straight away, but maybe you could check the $dsn on a good and a bad run of the code, just to ensure that the data is consistent

gabebster · 04-06-2006, 11:48 PM

I tracked down what script was causing the error but I was looking at the wrong sql statement that caused the error. I fixed that statement, now it is working. Thanks for all of your time anyway.