[SOLVED] trying to read a csv file and insert individual fields into correct colunms

[cluce]

I have the a small table that is username(varchar 50) and password(varchar 50). And a csv file with 2 colunms such as this.....

username|password

  user1 pass1

  user2 pass2

  user3 pass3

  user4 pass4

  user5 pass5

 

I know how to read a csv in a loop and display it on a browser and use an UPDATE query to update one colunm but how can I pick out colunm 1 data(username) and put it in a string and pick out colunm 2 data(password) and put it in a string and use the INSERT INTO users(username, password) VALUES(userX, passX ) to insert the data into the database.

 

here is my code..

<?php
$counter = 1; //initialize counter

include'db.php';

$handle = fopen("pass.csv","r");
while(($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
{
   $username = $data[0];   
   $password = $data[2];   

   $sql = 'INSERT INTO users (username , password) VALUES ("'.$username.'", "'.$password.'")';
   mysqli_query($mysqli, $sql);   
   $counter++;
}
?>

what its doing is inserting only the passwords and putting it in the username colunm? can someone give me some insight on this?

[Yesideez]

The same thing has been discussed here.

http://www.phpfreaks.com/forums/index.php/topic,148711.30.html

[Yesideez]

Add this line before your mysqli_query() call to show what is being used in the query:

echo 'SQL='.$sql.'<br />';

[cluce]

The same thing has been discussed here.

http://www.phpfreaks.com/forums/index.php/topic,148711.30.html

yes I saw that AND I couldnt find a solution there. thats where I got that code from and modified it.

[cluce]

OK here is what I have which I was expecting. I just dont know how to fix it

 

SQL=INSERT INTO users (username , password) VALUES ("pass1", "")

SQL=INSERT INTO users (username , password) VALUES ("pass2", "")

SQL=INSERT INTO users (username , password) VALUES ("pass3", "")

SQL=INSERT INTO users (username , password) VALUES ("pass4", "")

SQL=INSERT INTO users (username , password) VALUES ("pass5", "")

[Yesideez]

   $username = $data[0];   
   $password = $data[2];

You're indexing 0 and then 2 - can you post a line of your CSV file?

[Yesideez]

If you CSV file looks like this:

username,password
user1,pass1
user2,pass2
user3,pass3
user4,pass4
user5,pass5

then you should be using this instead:

   $username = $data[0];
   $password = $data[1];

[cluce]

thanks for pointing the index out I changed that but thats not all. The output is still the same as previous post.  I think the problem is somewhere in these lines. I just not sure what? .....

 

$handle = fopen("pass.csv","r");

while(($data = fgetcsv($handle, 1000, ",")) !== FALSE)

 

and here is my whole csv file..

 

 user1   pass1

 user2   pass2

 user3   pass3

 user4   pass4

 user5   pass5

[Yesideez]

Have you not got any commas in your CSV file? Without those it will fail. Try replacing your file with that in my previous post and see if that works.

[cluce]

great well I got it to work. it was the wrong file name. my mistake. Sorry.  It worked without the commas? im not sure if I need them? topic solved thanks for your help

[Yesideez]

$password = md5($data[1]);

That will create an MD5 hash of the password which will be a 32 character string so the datatype for your password field would have to be VARCHAR(32)

[cluce]

oh. that never occured to me . maybe thats why my UPDATE loop didnt work when I tried to logon with the real passwords. so I need to change my datatype to match the hash functions.  I will try both my UPDATE and INSERT codes and see if I can update the table with the hashed passwords and see if I can login. thx again

[cluce]

that was it!! I would have never figured that out by myself. Just went over my head but was simple. I also got my UPDATE loop doing the same principle to work too. All I had to do was change my varchar to 50 which I thought it was or to whatever can handle md5 or sha1.  Another careless mistake I did.

 

thx