CSV file into mysql database using PHP
Hi, I know very basic scripting but am getting confused with the mysql portion. I need a PHP script to import CSV file into mysql database.
I have a CSV file exported from Excel that contains 5 columns:
userid, firstname, lastname, password in text format which needs to be md5, and email address.
These 5 columns need to be imported into an existing mysql database that already has existing tables and data. I believe it needs something like this:
INSERT INTO table_members SET group_id=5, username= .... and then username needs to come from the csv file.
The row inserted into table_members also needs:
- a uniqueNumber - a random 32 character string, used for session cookies.
- ip_address (just put in 127.0.0.1 is fine)
- date - Unix time stamp correlated to GMT time. (just whatever time is fine, just proper format?)
Then, there are 2 more tables that needs a row for each new member. Using the member_id of the member as reference:
- table_memberd (this table also needs to add from the CSV file the firstname and lastname)
- table_memberh (this table just needs to add row with member_id and no data for the columns in this table)
I'm thinking that when inserting in table_members, that the script gets the last member_id that was used the next new member uses the next member_id.
Hope that makes sense! Help is so much appreciated.
Thanks!
I have a CSV file exported from Excel that contains 5 columns:
userid, firstname, lastname, password in text format which needs to be md5, and email address.
These 5 columns need to be imported into an existing mysql database that already has existing tables and data. I believe it needs something like this:
INSERT INTO table_members SET group_id=5, username= .... and then username needs to come from the csv file.
The row inserted into table_members also needs:
- a uniqueNumber - a random 32 character string, used for session cookies.
- ip_address (just put in 127.0.0.1 is fine)
- date - Unix time stamp correlated to GMT time. (just whatever time is fine, just proper format?)
Then, there are 2 more tables that needs a row for each new member. Using the member_id of the member as reference:
- table_memberd (this table also needs to add from the CSV file the firstname and lastname)
- table_memberh (this table just needs to add row with member_id and no data for the columns in this table)
I'm thinking that when inserting in table_members, that the script gets the last member_id that was used the next new member uses the next member_id.
Hope that makes sense! Help is so much appreciated.
Thanks!
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hmm, there are no enclosures, it is just item,item,item,item in the CSV file.
ASKER
Where would I put the md5 to md5 the password? hmm....
thanks a bunch!
thanks a bunch!
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Oh! ok... let me try it ... thx again
ASKER
Ok, looking through the script again, it doesn't have UniqueNumber
The row inserted into table_members also needs:
- a uniqueNumber - a random 32 character string, used for session cookies.
The row inserted into table_members also needs:
- a uniqueNumber - a random 32 character string, used for session cookies.
ASKER
Three things:
1. After import, the first username imported has these strange characters in front of it. The 2nd imported seems ok. See attached image username-and-date-wrong.jp
2. The date function now() doesn't seem to work, as it has set the date to be 1969-12-31. Also see attached image username-and-date-wrong.jp
3. I need - a uniqueNumber - a random 32 character string, used for session cookies. I believe it also has to be hashed. I tried adding this:
[code]
function genUniqID()
{
$uniqueID = hash(uniqid(mt_rand()));
return $uniqueID;
}
[/code]
But hash doesn't work on my test server -- I must be using PHP4. (The live server is using PHP5.) So instead of hash, i use md5 there. But look at
However, the results are
username-and-date-wrong.jpg
1. After import, the first username imported has these strange characters in front of it. The 2nd imported seems ok. See attached image username-and-date-wrong.jp
2. The date function now() doesn't seem to work, as it has set the date to be 1969-12-31. Also see attached image username-and-date-wrong.jp
3. I need - a uniqueNumber - a random 32 character string, used for session cookies. I believe it also has to be hashed. I tried adding this:
[code]
function genUniqID()
{
$uniqueID = hash(uniqid(mt_rand()));
return $uniqueID;
}
[/code]
But hash doesn't work on my test server -- I must be using PHP4. (The live server is using PHP5.) So instead of hash, i use md5 there. But look at
However, the results are
username-and-date-wrong.jpg
ASKER
Oops, didn't finish the above entry... actually 4 things:
3. I need - a uniqueNumber - a random 32 character string, used for session cookies. I believe it also has to be hashed. I tried using this function to generate a unique ID.
But hash doesn't work on my test server -- I must be using PHP4. (The live server is using PHP5.) So instead of hash, i use md5 there. Please see attached image problem-md5.jpg.
Testuser2 and Testuser3 are NOT imported using the script. They are created in the system and their passwords and unique_id hashes are 40 characters long.
When using import script, the unique ID's created for Frodo and Wizard are only 32 characters long.
4. The fourth issue: notice the passwords. They are also only 32 characters long when imported using the script. The system generates 40 character long ones for testuser2 and testuser3.
Your help is very much appreciated. Thank you!
3. I need - a uniqueNumber - a random 32 character string, used for session cookies. I believe it also has to be hashed. I tried using this function to generate a unique ID.
But hash doesn't work on my test server -- I must be using PHP4. (The live server is using PHP5.) So instead of hash, i use md5 there. Please see attached image problem-md5.jpg.
Testuser2 and Testuser3 are NOT imported using the script. They are created in the system and their passwords and unique_id hashes are 40 characters long.
When using import script, the unique ID's created for Frodo and Wizard are only 32 characters long.
4. The fourth issue: notice the passwords. They are also only 32 characters long when imported using the script. The system generates 40 character long ones for testuser2 and testuser3.
Your help is very much appreciated. Thank you!
function genUniqID()
{
$uniqueID = hash(uniqid(mt_rand()));
return $uniqueID;
}
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Roonaan!
#1. - Yes, the CSV file was UTF, changed it and now it imports without the funny characters at the beginning.
#2 and #3 - uniqueID seems to be working. Both uniqueID and password needed to be sha1, that was my mistake. But now they are OK.
*
#4 - now() is NOT working on the live server either. I looked at the table in phpmyAdmin and it says the date should be int(10) unsigned. When importing with now() function, it only shows the year 2008 when I look at it in phpmyadmin.
So, other than the date, that's it --- we're really close now. Almost...
Thanks again for your help.
#1. - Yes, the CSV file was UTF, changed it and now it imports without the funny characters at the beginning.
#2 and #3 - uniqueID seems to be working. Both uniqueID and password needed to be sha1, that was my mistake. But now they are OK.
*
#4 - now() is NOT working on the live server either. I looked at the table in phpmyAdmin and it says the date should be int(10) unsigned. When importing with now() function, it only shows the year 2008 when I look at it in phpmyadmin.
So, other than the date, that's it --- we're really close now. Almost...
Thanks again for your help.
ASKER
ok, found it, it is using unix time stamp, I think 1216199294.
ASKER
Thank you!
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER