CODEWITHSUNDEEP
phpmysql
Taylor Stevens
Taylor Stevens

Reputation: 81

Update Database via CSV Duplicating Records

I am trying to update my database from a webpage. I have the connection to the database working and am able to change records in the database from the webpage. My issue currently is, when I upload a CSV the webpage will change the records but it changes all the records in the database to the last line of the CSV. It creates a line for each item in the CSV but each line gets duplicated to the same record. Can anyone point out where there may be an error?

$connect = mysqli_connect("$dbHost", "$dbUsername", "$dbPassword", 
"$dbName");
$message = '';

if(isset($_POST["upload"]))
{
 if($_FILES['product_file']['name'])
 {    
  $filename = explode(".", $_FILES['product_file']['name']);
  if(end($filename) == "csv")
  {
   $handle = fopen($_FILES['product_file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $device_state = mysqli_real_escape_string($connect, $data[0]);
    $last_seen = mysqli_real_escape_string($connect, $data[1]);  
    $device_type = mysqli_real_escape_string($connect, $data[2]);
    $site_number = mysqli_real_escape_string($connect, $data[3]);
    $meritage_site_number = mysqli_real_escape_string($connect, 
$data[4]);
    $store_address = mysqli_real_escape_string($connect, $data[5]);
    $city = mysqli_real_escape_string($connect, $data[6]);
    $state = mysqli_real_escape_string($connect, $data[7]);
    $ip_address = mysqli_real_escape_string($connect, $data[8]);
    $hostname = mysqli_real_escape_string($connect, $data[9]);
    $node_number = mysqli_real_escape_string($connect, $data[10]);
    $real_status = mysqli_real_escape_string($connect, $data[11]);
    $short_model = mysqli_real_escape_string($connect, $data[12]);
    $software_version = mysqli_real_escape_string($connect, $data[13]);
    $query = "
     UPDATE offline_devices 
     SET 
        device_state = '$device_state', 
        last_seen = '$last_seen', 
        device_type = '$device_type',
        site_number = '$site_number',
        meritage_site_number = '$meritage_site_number',
        store_address = '$store_address',
        city = '$city',
        state = '$state',
        ip_address = '$ip_address',
        hostname = '$hostname',
        node_number = '$node_number',
        real_status = '$real_status',
        short_model = '$short_model' 
      WHERE software_version = '$software_version'
    ";

    mysqli_query($connect, $query);
   }
   fclose($handle);
   header("location: ORBupload.php?updation=1");
  }
  else
  {
   $message = '<label class="text-danger">Please Select CSV File 
 only</label>';
  }
 }
  else
 {
  $message = '<label class="text-danger">Please Select File</label>';
 }
 }

 if(isset($_GET["updation"]))
{
 $message = '<label class="text-success">Database Updated</label>';
}

$query = "SELECT * FROM offline_devices";
$result = mysqli_query($connect, $query);

When I upload the file, the database copies the same record for each individual line. Each line in the database repeats the last line of the CVS.

Upvotes: 0

Views: 37

Answers (1)

S. Imp
S. Imp

Reputation: 2895

Without seeing your database structure & data or the CSV file's contents, I can't say for sure what the problem is, but I think you should examine your query very closely. Looks to me like that could be the problem. If every record in your DB has the same software_version, then you are updating every single record in your DB every time you run this query with that software_version value:

    $query = "
     UPDATE offline_devices 
     SET 
        device_state = '$device_state', 
        last_seen = '$last_seen', 
        device_type = '$device_type',
        site_number = '$site_number',
        meritage_site_number = '$meritage_site_number',
        store_address = '$store_address',
        city = '$city',
        state = '$state',
        ip_address = '$ip_address',
        hostname = '$hostname',
        node_number = '$node_number',
        real_status = '$real_status',
        short_model = '$short_model' 
      WHERE software_version = '$software_version'
    ";

Shouldn't that query be checking for a product_id or something? Maybe node_number?

Upvotes: 0

Related Questions