Insert data from text file into mysql

Question

I have files on my server example 25295.txt, 24565.txt on my server where file name is the user id and the content of the file is like

[{"name":"+91 88264 73159","mobile":"+918826473159"},
 {"name":"+91 99971 17220","mobile":"+919997117220"}]

My table structure is like this:

I want to run a cron job to insert each name and mobile with user_id (name of the text file is user id) in different rows from multiple files. There are around 800 records for the same in each file and also trim the mobile with:

$mobile=str_replace('+','0',$data['mobile']);
$mobile=str_replace(' ','',$mobile);
$mobile=substr($mobile, -10);

How to do the same and after the upload I want to delete the text file from the server.

this method also works fine but it does not take user_id from file name and how to use it for multiple files ? — Niks
– Niks, Commented Sep 8, 2016 at 15:39

Peter · Accepted Answer · 2016-09-08 13:08:13Z

1

The files you have on your server look like JSON. You can simply decode them and then insert them as you would normally do.

$filePath = '/tmp/somefile.txt';
$contents = file_get_contents($filePath);
$obj = json_decode($contents);

var_dump($cbj);

Update

To make it even easier, here's a little example of the whole process from file to database.

cronjob.php

// Make your database connection
$link = mysqli_connect('<hostname>', '<username>', '<password>', '<database>');

// Define the file location
$fileLocation = '/tmp/example.txt';

// Retrieve contents
$contents = file_get_contents($fileLocation);

// Convert json to ObjectArray
$persons = json_decode($contents);

// Loop through array and insert
foreach ($persons as $person) {
    $number = substr(str_replace(' ', '', str_replace('+', '00', $person->number))), -10);
    $stmt = "INSERT INTO persons (id, name, number) VALUES ('".$person->id."', '".$person->name."', '".$number."')";

    mysqli_query($link, $stmt);
}

// Remove the file
unlink($fileLocation);

example.txt

[{"id":14,"name":"John","number":"555-66-77-8"},{"id":24,"name":"Jane","number":"555-77-88-9"},{"id":34,"name":"Santa","number":"555-6-77-77"}]

edited Sep 8, 2016 at 13:08

answered Sep 8, 2016 at 10:07

Peter

9,1868 gold badges69 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rasa Mohamed Over a year ago

$obj[0]['mobile'], $obj[1]['mobile'], ... like wise you can get all datas

Passionate Coder · Accepted Answer · 2016-09-08 11:27:30Z

My code is for one file say 25295.txt

<?php
$conn = new mysqli('localhost','root','password','dbname');
if($conn){
$filename = '25295.txt';
$filePath = '/path_to_file/'.$filename;// file path    
$contents = file_get_contents($filePath);
$userID = rtrim($filename,".txt");// this will give  25295  
$data = json_decode($contents,true);// this will give associative array 
$stmt = $conn->prepare("INSERT INTO table_name(user_id,name, mobile) VALUES(?,?, ?)") or die($stmt->error);
foreach($data as $insertRow){
    foreach($insertRow as $key => $value){
    $insertData[$key] =  $value;
     }  
    extract($insertData);// this will give variable from array with name same as key of array
    $stmt->bind_param('iss',$userID,$name,$mobile);
    $stmt->execute();   
}   
}

Collectives™ on Stack Overflow

Insert data from text file into mysql

2 Answers 2

Update

cronjob.php

example.txt

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

cronjob.php

example.txt

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related