read text file then insert into mysql database

[AMADH]

Hello,

I was wondering if somone might be able to help me.

I have a txt file, it looks like this

ID1|NAME1|URL1|EMAIL1|LOGO1|ADTEXT1|ADTEXT1|CATEGORY1|PUBDATE1
ID2|NAME2|URL2|EMAIL2|LOGO2|ADTEXT2|ADTEXT2|CATEGORY2|PUBDATE2
ID3|NAME3|URL3|EMAIL3|LOGO3|ADTEXT3|ADTEXT3|CATEGORY3|PUBDATE3

This is my php code

// initial database stuff
$host = "localhost";
$user = "root";
$pass = "root";
$db = "database";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!"); 
$file = "localhost:8888/milestones.txt";
$file_handle = fopen($file, "r");


fclose($file_handle);



    while (($milestone = fgetcsv($file_handle, 1000, "|")) !== FALSE) {  

           $milestone_query="INSERT into milestones(ID,NAME,URL,EMAIL,LOGO,ADTEXT,CATEGORY,PUBDATE) values('$milestone  
    [0]','$milestone[1]','$milestone[2]','$milestone[3]','$milestone[4]','$milestone[5]','$milestone[6]','$milestone[7]')";  
        mysql_query("TRUNCATE TABLE milestones") or die("MySQL Error: " . mysql_error()); //Delete the existing rows


          mysql_query($milestone_query) or die(mysql_error());  

         }  




echo "Done!";

It works, but what I am haing trouble with is that it only inserts one the last row from the txt file and it does not insert the ID number.

Any IDeas on where I might be doing wrong?

Thanks,

[cereal]

Change your code from line 9 to 26 with this:

<?php

$file = file('file.txt'); # read file into array
$count = count($file);

if($count > 0) # file is not empty
{
    $milestone_query = "INSERT into milestones(ID, NAME, URL, EMAIL, LOGO, ADTEXT, CATEGORY, PUBDATE) values";
    $i = 1;
    foreach($file as $row)
    {
        $milestone = explode('|',$row);
        $milestone_query .= "('$milestone[0]',  '$milestone[1]', '$milestone[2]', '$milestone[3]', '$milestone[4]', '$milestone[5]', '$milestone[6]', '$milestone[7]')";
        $milestone_query .= $i < $count ? ',':'';
        $i++;
    }
    mysql_query($milestone_query) or die(mysql_error());
}
?>

If you echo the output of $milestone_query you will see something like this:

INSERT into milestones(ID, NAME, URL, EMAIL, LOGO, ADTEXT, CATEGORY, PUBDATE) values('ID1', 'NAME1', 'URL1', 'EMAIL1', 'LOGO1', 'ADTEXT1', 'ADTEXT1', 'CATEGORY1'),('ID2', 'NAME2', 'URL2', 'EMAIL2', 'LOGO2', 'ADTEXT2', 'ADTEXT2', 'CATEGORY2'),('ID3', 'NAME3', 'URL3', 'EMAIL3', 'LOGO3', 'ADTEXT3', 'ADTEXT3', 'CATEGORY3')

Besides, I count 9 keys in the array, not 8:

Array
(
    [0] => ID1
    [1] => NAME1
    [2] => URL1
    [3] => EMAIL1
    [4] => LOGO1
    [5] => ADTEXT1
    [6] => ADTEXT1
    [7] => CATEGORY1
    [8] => PUBDATE1

)

so pay attention to array keys 5 and 6 which in this example are the same and check if ID in your table is INT, in this case it will not accept a string like ID1, ID2 but only numbers.

[AMADH]

Thanks for your help Cereal. I got it working now. Thank you so much

[cereal]

Perfect, just remember to use truncate outside the loop. I forgot to mention that in my example, but that was the main problem, bye!

(if we have finished mark the thread solved!)