How to load a txt file using php and loop through contents to query a database

Question

I have a piece of code I just can't get to work properly. I am trying to loop through a txt file with about 1k lines with a filename on each. Then loop each filename into a mysql query to delete a row from a table if that filename matches.

<?php 
$handle = fopen("corrupt.txt", "r");
$link = mysqli_connect("localhost", "user", "pass", "listings"); 

if ($handle) {
    while (($line = fgets($handle)) !== false) {
        if($link === false){ 
            die("ERROR: Could not connect. " . mysqli_connect_error()); 
        } 
        $sql = "DELETE FROM images WHERE images_file_name like $line"; 
        if(mysqli_query($link, $sql)){         
        }  
        else{ 
            echo "ERROR: Could not able to execute $sql. "  
            . mysqli_error($link); 
        } 
        mysqli_close($link); 
    }
} else {

} 
fclose($handle);
?>

You need to tell us what your issue is. What happens when you run this code? Also, wouldn't it be better to check the connection _before_ your loop instead of doing it on each iteration? Also, if `images_file_name` is a string, you need to quote the value. However, I would recommend using prepared statements instead. — M. Eriksson, Jan 30 '19 at 06:59
At a guess - I would say missing quotes in `like $line";`, although you should be using prepared statements. — Nigel Ren, Jan 30 '19 at 07:01
You don't need to check `if($link === false)` inside the loop, once after connecting would do. — Nigel Ren, Jan 30 '19 at 07:01
It would help to know what error or behavior you are getting, but: you are closing the database connection while still looping over the file, try moving `mysqli_close($link); ` to the bottom, after the `while { ... }` loop maybe — Sven Mich, Jan 30 '19 at 07:02

Bernhard · Answer 1 · 2019-01-30T07:18:07.800

First: Always avoid mysql-queries inside of loops.

// get data as an array
$file = file('data.txt');    

// check if datasource has at least one line
if(count($file) > 0){

 // create delete-array
 $delete = array();

 // loop trough each array element
 foreach($file as $line){
  // trim current line
  $line = trim($line);
  // check if line is not empty
  if(!empty($line)){
   // add line to delete-array
   $delete[] = $line;
  }
 }

 // check if delete-array contains at least one item
 if(count($delete > 0)){
  // delete the items in the array from the database
  mysqli_query($link, "DELETE FROM records WHERE filename IN('".implode("','", $delete)."'") or die(mysqli_error($link));
 }

}

If the datasource is not of yourself, you should also use mysqli_real_escape_string(); to escape the data before you make the query.

score 0 · Answer 2 · answered Jan 30 '19 at 07:10

0

You need to put quotes in '$line' so that the variable is not treated as a column name

DELETE FROM images WHERE images_file_name like '$line'

You have to read on prepared statements though as you are open to sql injection

answered Jan 30 '19 at 07:10

Twista

241
3
11

score 0 · Answer 3 · answered Jan 30 '19 at 07:30

Using the (very) useful advice in the comments and tidying up the code a bit, this uses prepared statements etc, only closes the link at the end (some useful info at When should I close a database connection in PHP? as well)...

$handle = fopen("corrupt.txt", "r");

if ($handle) {
    $link = mysqli_connect("localhost", "user", "pass", "listings"); 
    if($link === false){
        die("ERROR: Could not connect. " . mysqli_connect_error());
    }
    $sql = "DELETE FROM images WHERE images_file_name = ?";
    if( !$stmt = mysqli_prepare($link, $sql) ){
        die("ERROR: Could not prepare. " . mysqli_error($link));
    }
    mysqli_stmt_bind_param($stmt, "s", $line);
    while (!feof($handle)) {
        $line = trim(fgets($handle));
        if(!mysqli_stmt_execute($stmt)){
            echo "ERROR: Could not able to execute $sql. "
                . mysqli_error($link);
        }
    }
    mysqli_close($link);
    fclose($handle);
} else {

}

Also note that I've changed the SQL from like ... to = ... assuming that the name is an exact match to the content.

How to load a txt file using php and loop through contents to query a database

3 Answers3