Re: Copying data stored in different field formats from one table to another
After reading through the MySQL manuals, as suggested here, I attempted
to construct the following statement to copy data from the table
members to the table forum_members:
INSERT INTO forum_members( MD5(passwd) , dateRegistered, realName,
ID_MEMBER, memberName, emailAddress, active, keitai, number, admin,
cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe,
websiteUrl ) VALUES ('temp', UNIX_TIMESTAMP( joindate ) , CONCAT_WS( '
', firstname, lastname ) , id, username, email, active, keitai, number,
admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe,
website)FROM members
However, I got the following error:
#1064 - 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 'MD5( passwd ) , dateRegistered, realName, ID_MEMBER, memberName,
It seems that MySQL does not like me putting in syntax to modify the
fields within my INSERT command. Specifically, it objected to having
MD5(), UNIX_TIMESTAMP(), and CONCAT_WS(). At least this is what is
indicated to me within the phpMyAdmin interface, which highlights the
erroneous sections of the statement in red.
Am I using the wrong statement? How do I take a value from one field,
such as the date, and make it into a different format for inserting into
another field if the INSERT command won't accept those modifications?
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
Hi Dave,
I think your problem is that you're trying to call one of
your columns MD5(passwd) and my guess would be that you should do:
INSERT INTO forum_members( passwd, dateRegistered, realName,
ID_MEMBER, memberName, emailAddress, active, keitai, number, admin,
cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe,
websiteUrl ) VALUES (MD5('temp'), UNIX_TIMESTAMP( joindate ) ,
CONCAT_WS( ' ', firstname, lastname ) , id, username, email, active,
keitai, number,
admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe,
website)FROM members
/Johan
Dave wrote:
After reading through the MySQL manuals, as suggested here, I attempted
to construct the following statement to copy data from the table
members to the table forum_members:
INSERT INTO forum_members( MD5(passwd) , dateRegistered, realName,
ID_MEMBER, memberName, emailAddress, active, keitai, number, admin,
cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe,
websiteUrl ) VALUES ('temp', UNIX_TIMESTAMP( joindate ) , CONCAT_WS( '
', firstname, lastname ) , id, username, email, active, keitai, number,
admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe,
website)FROM members
However, I got the following error:
#1064 - 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 'MD5( passwd ) , dateRegistered, realName, ID_MEMBER, memberName,
It seems that MySQL does not like me putting in syntax to modify the
fields within my INSERT command. Specifically, it objected to having
MD5(), UNIX_TIMESTAMP(), and CONCAT_WS(). At least this is what is
indicated to me within the phpMyAdmin interface, which highlights the
erroneous sections of the statement in red.
Am I using the wrong statement? How do I take a value from one field,
such as the date, and make it into a different format for inserting into
another field if the INSERT command won't accept those modifications?
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
I think your problem is that you're trying to call one of your columns MD5(passwd) Thank you for pointing that out. I had the function in the wrong part of the statement. Actually, I've realized I'm probably best off to leave the passwords alone for now, and go ahead and copy the rest of the data. However, I've encountered a new error which comes out of nowhwere. This is my query: INSERT INTO forum_members( dateRegistered, realName, ID_MEMBER, memberName, emailAddress, active, keitai, number, admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe, websiteUrl ) SELECT (UNIX_TIMESTAMP( joindate ) , CONCAT_WS( ' ', firstname, lastname ) , id, username, email, active, keitai, number, admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe, website) FROM members WHERE id 0 And this is the error returned: #1267 - Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'concat_ws' So far as I know, I've attempted at every possible place to make all fields, tables, and settings in my database to be utf-8. I have double checked the source fields and table, and the destination fields and table, and in all cases the collation is marked as being utf8_general_ci. Where could this latin1_swedish_ci collation be coming from? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
Hello.
After reading through the MySQL manuals, as suggested here, I attempted
to construct the following statement to copy data from the table members
to the table forum_members:
Please, keep your answers on the list. Use INSERT ... SELECT. Don't put
the functions in the column list. See one more time:
http://dev.mysql.com/doc/mysql/en/insert-select.html
Dave [EMAIL PROTECTED] wrote:
After reading through the MySQL manuals, as suggested here, I attempted to
construct the following statement to copy data from the table members to the
table forum_members:
INSERT INTO forum_members( MD5(passwd) , dateRegistered, realName, ID_MEMBER,
memberName, emailAddress, active, keitai, number, admin, cardpic, cardbio,
hofpic, hofbio, nickname, contactMe, showMe, websiteUrl ) VALUES ('temp',
UNIX_TIMESTAMP( joindate ) , CONCAT_WS( ' ', firstname, lastname ) , id,
username, email, active, keitai, number, admin, cardpic, cardbio, hofpic,
hofbio, nickname, contactMe, showMe, website)FROM members
However, I got the following error:
#1064 - 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
'MD5( passwd ) , dateRegistered, realName, ID_MEMBER, memberName,
It seems that MySQL does not like me putting in syntax to modify the fields
within my INSERT command. Specifically, it objected to having MD5(),
UNIX_TIMESTAMP(), and CONCAT_WS(). At least this is what is indicated to me
within the phpMyAdmin interface, which highlights the erroneous sections of
the statement in red.
Am I using the wrong statement? How do I take a value from one field, such as
the date, and make it into a different format for inserting into another field
if the INSERT command won't accept those modifications?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
Dave, I'm not that good when it comes to collations but latin1_swedish_ci is normally the default collation for a MySQL installation. Have you checked via SHOW VARIABLES that you don't have latin1_swedish_ci lurking somewhere. My hunch here is that your ' ' in the CONCAT_WS defaults to latin1_swedish_ci. Hope this helps, /Johan Dave wrote: I think your problem is that you're trying to call one of your columns MD5(passwd) Thank you for pointing that out. I had the function in the wrong part of the statement. Actually, I've realized I'm probably best off to leave the passwords alone for now, and go ahead and copy the rest of the data. However, I've encountered a new error which comes out of nowhwere. This is my query: INSERT INTO forum_members( dateRegistered, realName, ID_MEMBER, memberName, emailAddress, active, keitai, number, admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe, websiteUrl ) SELECT (UNIX_TIMESTAMP( joindate ) , CONCAT_WS( ' ', firstname, lastname ) , id, username, email, active, keitai, number, admin, cardpic, cardbio, hofpic, hofbio, nickname, contactMe, showMe, website) FROM members WHERE id 0 And this is the error returned: #1267 - Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'concat_ws' So far as I know, I've attempted at every possible place to make all fields, tables, and settings in my database to be utf-8. I have double checked the source fields and table, and the destination fields and table, and in all cases the collation is marked as being utf8_general_ci. Where could this latin1_swedish_ci collation be coming from? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
My hunch here is that your ' ' in the CONCAT_WS defaults to
latin1_swedish_ci.
It seems you're right. There are a few variables that are still in
default mode which I discovered through the SHOW VARIABLES command. I've
tried to switch them, but I'm obviously using the syntax incorrectly. I
looked in the manual for variable settings, and it says to use the SET
command. But none of these versions work:
SET collation_connection = utf8_general_ci;
or
SET collation_connection (utf8_general_ci);
or
SET collation_connection {utf8_general_ci};
What is the correct syntax for setting this kind of variable?
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
Dave,
I've tried
SET collation_connection = utf8_general_ci;
on my server (4.1.14) and it seems to work so
I'm afraid I can't help you anymore there.
/Johan
Dave wrote:
My hunch here is that your ' ' in the CONCAT_WS defaults to
latin1_swedish_ci.
It seems you're right. There are a few variables that are still in
default mode which I discovered through the SHOW VARIABLES command. I've
tried to switch them, but I'm obviously using the syntax incorrectly. I
looked in the manual for variable settings, and it says to use the SET
command. But none of these versions work:
SET collation_connection = utf8_general_ci;
or
SET collation_connection (utf8_general_ci);
or
SET collation_connection {utf8_general_ci};
What is the correct syntax for setting this kind of variable?
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
SET collation_connection = utf8_general_ci; on my server (4.1.14) and it seems to work I made a new Query, copying the text as you wrote it, and it still didn't work, but in a different way. I type: SET collation_connection = utf8_general_ci; ... and I get the following returned to me: Your SQL-query has been executed successfully (Query took 0.0002 sec) But, when I type: SHOW VARIABLES; ... it still shows: collation_connection latin1_swedish_ci Am I perhaps setting the variable in the wrong place? Globally and not locally or something to that effect? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another [PARTIALLY SOLVED]
While the basic issue of correct syntax is still open, this issue has boiled down to a specific error message, so I've moved to a new thread called: Operand should contain 1 column error However, for future reference, the issue of variables has been solved. I discovered that to make the changes to the variables that I wanted to take hold and not be temporary, I had to use SET GLOBAL instead of just SET. Thank you everyone for your assistance. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying data stored in different field formats from one table to another
MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have recently installed a forum ( http://www.simplemachines.org/ ) on my web site. I already have about 150 users on my site, and I want to transplant their information from their current table into the members table used by the forum. With many of the necessary fields, this means I have to take the format of the current data and put it into a the new field with a different format. The Questions: In the following 5 cases, will it be safe to copy data directly, or will there be data loss or scrambling: 1. In the first case, the id number for the original table is an INT, with a length of 11. In the target table, it's a MEDIUMINT with a length of 8. Since I only have 150 members starting at an id of 1, I'm assuming I can just copy the numbers without fear of truncation 2. The username in the source table is a VARCHAR with a length of 80, but the destination has a length of 16. What will happen to the data in the destination table if it's longer than 16? Will it be truncated or rejected? 3. The third case I'm a little confused about. In the source table, I have a field for recording the date that the member joined. This is stored in DATE format. The destination table uses a simple INT with a length of 10. I this is a Unix time stamp format, because according to the forum web site, you can access it from PHP using the time() function. So would the command be something like: COPY DATE(members.joindate) TO UNIX_TIMESTAMP(forum_members.dateRegistered) ...? 4. For storing email addresses, the source table uses a VARCHAR field with a length of 100. In the destination it's a TINYTEXT. If I understand correctly, TINYTEXT should hold 256 characters, so there should be enough room. 5. The last case I need to combine two fields into one. The source table uses seperate fields for first name and last name, and each is a VARCHAR with a length of 100. In the destination table, first and last names are stored in the same field, with a TINYTEXT format. However, despite the fact that I've looked up combine fields and concatenate fields on Google, I can't find instructions on how to do that in reference to merging two fields into one, or copying from one field to another. All the references I found are for querying the database, combining data at the point of user input. How do I combine two fields from one table, and put them into another? Any assistance is much appreciated. Thank you. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying data stored in different field formats from one table to another
Hello. This is not a step by step instructions. But taking into an account that you're a MySQL beginner, in my opinion, you should read some parts of the manual: http://dev.mysql.com/doc/mysql/en/string-functions.html http://dev.mysql.com/doc/mysql/en/insert-select.html http://dev.mysql.com/doc/mysql/en/numeric-types.html http://dev.mysql.com/doc/mysql/en/show-warnings.html http://dev.mysql.com/doc/mysql/en/string-types.html I recommend you to upgrade your MySQL to the latest release (4.1.14 now). See: http://dev.mysql.com/doc/mysql/en/upgrade.html Dave [EMAIL PROTECTED] wrote: MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have recently installed a forum ( http://www.simplemachines.org/ ) on my web site. I already have about 150 users on my site, and I want to transplant their information from their current table into the members table used by the forum. With many of the necessary fields, this means I have to take the format of the current data and put it into a the new field with a different format. The Questions: In the following 5 cases, will it be safe to copy data directly, or will there be data loss or scrambling: 1. In the first case, the id number for the original table is an INT, with a length of 11. In the target table, it's a MEDIUMINT with a length of 8. Since I only have 150 members starting at an id of 1, I'm assuming I can just copy the numbers without fear of truncation 2. The username in the source table is a VARCHAR with a length of 80, but the destination has a length of 16. What will happen to the data in the destination table if it's longer than 16? Will it be truncated or rejected? 3. The third case I'm a little confused about. In the source table, I have a field for recording the date that the member joined. This is stored in DATE format. The destination table uses a simple INT with a length of 10. I this is a Unix time stamp format, because according to the forum web site, you can access it from PHP using the time() function. So would the command be something like: COPY DATE(members.joindate) TO UNIX_TIMESTAMP(forum_members.dateRegistered) ...? 4. For storing email addresses, the source table uses a VARCHAR field with a length of 100. In the destination it's a TINYTEXT. If I understand correctly, TINYTEXT should hold 256 characters, so there should be enough room. 5. The last case I need to combine two fields into one. The source table uses seperate fields for first name and last name, and each is a VARCHAR with a length of 100. In the destination table, first and last names are stored in the same field, with a TINYTEXT format. However, despite the fact that I've looked up combine fields and concatenate fields on Google, I can't find instructions on how to do that in reference to merging two fields into one, or copying from one field to another. All the references I found are for querying the database, combining data at the point of user input. How do I combine two fields from one table, and put them into another? Any assistance is much appreciated. Thank you. Dave -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
