Adding an ENCODE to my sql insert statement

I need a little help with encode.  I'm just learning how to work with ENCODE and DECODE.

I've written this sql query, which works just fine (minus the encode):

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("dbname") or die(mysql_error());

mysql_query("INSERT INTO mymembers

(ID, rec_no, email, PASSWORD, firstname) VALUES('', '', 'grr@t.com', 'pass2', 'dan' ) ")

or die(mysql_error());

Now I want to try and ENCODE the password, so I wrote this query:

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("dbname") or die(mysql_error());

mysql_query("INSERT INTO mymembers

(ID, rec_no, email, PASSWORD, firstname) VALUES('', '', 'grr@t.com', ENCODE('pw101', 'key101'), 'dan' ) ")

or die(mysql_error());

But that query fails and throws this error message:

"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 'pw101', 'key101')', 'pass2', 'dave' )' at line 2"

This seems to be a quotation mark issue to me.

Can anyone help me write a sql insert that will use ENCODE on the password?

Thanks in advance for any help.

The error message you posted does not match the query you posted.

Any chance the code you executed is not what you think it is?

Sorry about that.  I modified my code to remove real variable data.  Here's my code:

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("dbname") or die(mysql_error());

mysql_query("INSERT INTO members

(ID, rec_no, email, PASSWORD, firstname) VALUES('', '', 'ENCODE('pw101', 'key101')', 'pass2', 'dave' ) ")

or die(mysql_error());

I've modified the variables in the connect and select strings.

Here's my error message:

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 'pw101', 'key101')', 'pass2', 'dave' )' at line 2

Remove the single quotes around the ENCODE() function, and give it another try.

Well, removing the single quotes around the ENCODE removed the error message I was getting, but now the field in my db that should be populated is empty.

Here's my updated query:

mysql_connect("localhost", "user", "pass") or die(mysql_error());

mysql_select_db("dbname") or die(mysql_error());

mysql_query("INSERT INTO members

(ID, rec_no, email, PASSWORD, firstname) VALUES('', '', 'boo@somewhere.com', ENCODE('pw101', 'key101'), 'dave' ) ")

or die(mysql_error());

Query runs fine, but no insert into the PASSWORD field in my db.

Is anything at all being inserted? What do you get if you issue SELECT ENCODE('pw101', 'key101') either from the SQL tab in phpMyAdmin, or a MySQL console prompt?

I'm viewing the database via phpMyAdmin.

The field for PASSWORD is empty.

The field is populated for other records that I created during testing, so I know that it's possible to insert data into that field.

If I run the SELECT statement you mention, I get some odd looking characters:

�-*��

Those characters are what you should get. Now we at least know it works. What is the field type set as for the PASSWORD field? Also, you might try pasting the whole query above into phpMyAdmin and see if it successfully inserts it that way.

I ran the whole query via phpMyAdmin.  Same result.  No data in the PASSWORD field.

The field type for PASSWORD is    char(100)

I'm kind of at a loss now. I just set up a test table and ran it locally, and it works fine for me. Have you tried to SELECT the data from the table to see if for some strange reason it's there and just not presenting itself?

SELECT ID, rec_no, email, decode( `PASSWORD` , 'key101' ) , firstname FROM members

This is a bugger.

I ran the query you mentioned.  All the password fields that should have been populated with the ENCODE are empty.  All the other password fields that were not populated with encode do have text in them - though its mush.

I've attached a screen shot.

Any additional ideas?

[attachment deleted by admin]

Alright, this is getting annoying now, LOL. The only other thing I can think of a the moment is the collation may be having an effect on it. I've been using utf8_unicode_ci for these tests. What are you using?

The encryption and compression functions return binary strings. For many of these functions, the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

Strangely enough, I tested it with both CHAR and BLOB types, and it worked both ways for me, so I didn't give it a second thought. Could still end up being the problem though, I suppose.