开发者

Insert text file to MySQL with PHP

I'm trying to load data from a .txt file with PHP, but I fail every time. I tried to convert the text file to a CSV, but even there I fail. My text file holds the content like this:

0001

120PCS LED MOVING HEAD LIGHT

TECHNICAL PARAMETER
Voltage: AC90V-120V or 200V-240V 50-60HZ
Power consumption:400W
Light source:120PCS 1W or 开发者_如何学JAVA3W LED 
(R:30pcs?G:30pcs,B:30psc,W:30pcs)
Control mode:12HS
Operation mode: master-slave, auto movement,
Sound control: DMX 512
Each led source has an expectancy over 50000 to 100000 hours in theory 
Optical len angle:15 degrees
Level scanning:540 degrees Vertical scanning
270 degrees, speed adjustable 
Indefinite RGBW color mixing system
LCD display adopted
Product size:512*402*555mm
N.W:19kg  G.W:21kg

0002

LED 108PCS MOVING HEAD LIGHT

TECHNICAL PARAMETER
Voltage:AC100V-240V ,50/60HZ 
Power consumption:306W
Light source:108pcs of 1/3W LED 
Operation mode master-slave, sound control,
auto movement,DMX512
Control channel:11Hs
Level scanning angle:540 degrees 
Vertical scanning angle:270degrees 
Quick electronic dimmer, strobe from 1 to 20 times/second
Smooth RGB mixing system &
Rainbow effect(can add white)
Beam angle:15 degrees
Package size :420*330*550mm
N.W:10kg  G.W:13kg

I tried to use:

 <?php
 $handle = @fopen("data.txt", "r");
 $conn = mysql_connect("localhost","root",""); 
 mysql_select_db("mytext",$conn);
 while (!feof($handle)) // Loop til end of file.
 {
 $buffer = fgets($handle, 4096);
  // Read a line.
 list($a,$b,$c)=explode("|",$buffer);
 //Separate string by the means of |
 echo $a."-".$b."-".$c."<br>";
 $sql = "INSERT INTO data_table (id, name, age) VALUES('".$a."','".$b."','".$c."')";   
 mysql_query($sql,$conn) or die(mysql_error());
 }
 ?>

But I can't figure out how to insert it nicely.

value a = 0001
value b = 120PCS LED MOVING HEAD LIGHT
value c = Technicalparameter etc etc etc

value a = 0002
value b = LED 108PCS MOVING HEAD LIGHT
value c = Technicalparameter etc etc etc

I have trouble with delimiters.


The following code will work, assuming your input file is as shown above. That is:

  • Contains 3 fields per record
  • Fields in file separated by blank line
  • Number of fields in file evenly divisible by 3.

        <?php
        /* For simplicity in this example, I will assume the db table
         * conforms to the following schema:
         * 
         * CREATE TABLE products(
         *   id VARCHAR(5),
         *   item VARCHAR(100),
         *   item_details TEXT
         * );
         */
    
        /* Make the db connection */
        $link = mysql_connect('localhost', 'mysql_user', 'user_pwd');
        if (!$link) {
            die('Could not connect to db.');
        }
    
        // set the db.
        $db_selected = mysql_select_db('test', $link);
        if (!$db_selected) {
            die ("Can't access the db");
        }
    
        /* Read entire file into string. */
        $fileContents = file_get_contents('f1.txt');
    
        /* Set line ending character based on particular OS. 
         * Unix       = \n
         * Macintosh  = \r
         * MS Windows = \r\n
         */
        $lec = "\r\n"; // My system is MS Windows.
    
        /*Fields are separated by a blank line so the delimiter between
         * fields is double line ending character ($lec).
         */
        $delimiter = "$lec$lec";
    
        /* Build array of field data for all records. */
        $contentArray = explode($delimiter, $fileContents); 
    
        /* Get the size of the array. */
        $numElements = count($contentArray);
    
        /* Because we have 3 fields per record, $numElements should be 
         * evenly divisible by 3.
         */
        $numElelemtsPerRec = 3;
        if($numElements % $numElelemtsPerRec > 0) 
          exit("File parse error. Contents of file does not fill records.");
    
        /* Now build SQL input records. 
         * One record contails 3 ($numElelemtsPerRec) array elements.
         */
        for ($i = 0; $i <= $numElements - 1; $i = $i + $numElelemtsPerRec) {
            $id = mysql_real_escape_string($contentArray[$i]);
            $item = mysql_real_escape_string($contentArray[$i+1]);
            $item_details = mysql_real_escape_string($contentArray[$i+2]);
            $records .= "('$id', '$item', '$item_details'), ";
            echo("id = $id<br>
                  item = $item<br>
                  item_details = $item_details<br>
                  <hr>");
        }
    
        /* Now trim the ending comma and space from string of records. */
        $records = substr($records, 0, -2);
        //exit($records);
    
        /* Build and execute the SQL INSERT query. */
        $query = "INSERT INTO products (id, item, item_details) 
                 VALUES $records";
        $result = mysql_query($query);
    
        /* Check for db error. */
        if(!$result) echo("Query Error! Process aborted.");
    ?>
    


You're trying to explode each line with the | character, but I don't see any | in the text! The PHP code looks OK (untested). It's the text input that needs work.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜