LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 04-05-2006, 01:12 AM   #1
gabebster
Member
 
Registered: Jun 2003
Location: Oklahoma
Distribution: Gentoo
Posts: 117

Rep: Reputation: 15
Strange problem with PHP5/MySQL update function [SOLVED]


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.

Last edited by gabebster; 04-06-2006 at 11:50 PM.
 
Old 04-06-2006, 12:02 AM   #2
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Potentially dumb question on my part: but don't you need a semicolon (";") to terminate your mySQL line?
 
Old 04-06-2006, 11:52 AM   #3
gabebster
Member
 
Registered: Jun 2003
Location: Oklahoma
Distribution: Gentoo
Posts: 117

Original Poster
Rep: Reputation: 15
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.
 
Old 04-06-2006, 12:18 PM   #4
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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?
 
Old 04-06-2006, 12:30 PM   #5
xhi
Senior Member
 
Registered: Mar 2005
Location: USA::Pennsylvania
Distribution: Slackware
Posts: 1,065

Rep: Reputation: 45
how is the usertable setup? can you post a dump of the describe
 
Old 04-06-2006, 02:11 PM   #6
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
What does your function getConnection() look like?
 
Old 04-06-2006, 03:18 PM   #7
gabebster
Member
 
Registered: Jun 2003
Location: Oklahoma
Distribution: Gentoo
Posts: 117

Original Poster
Rep: Reputation: 15
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.
 
Old 04-06-2006, 04:59 PM   #8
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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
 
Old 04-06-2006, 05:12 PM   #9
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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
 
Old 04-06-2006, 11:48 PM   #10
gabebster
Member
 
Registered: Jun 2003
Location: Oklahoma
Distribution: Gentoo
Posts: 117

Original Poster
Rep: Reputation: 15
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.

Last edited by gabebster; 04-06-2006 at 11:50 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
php5 compile woes: undefined function imagettftext BCarey Slackware 5 05-18-2007 11:42 PM
strange problem after kernel update sharonenoch Linux - Newbie 3 02-21-2006 03:47 AM
strange problem after kernel update quiereme Linux - Laptop and Netbook 2 02-13-2006 06:53 AM
I have problem to update with the update function under fedora core 2 babyboss Fedora 4 08-10-2005 07:34 AM
Slack 10.0 PHP4 -> PHP5 update pdxluddite Slackware 2 03-29-2005 01:37 AM


All times are GMT -5. The time now is 01:05 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration