PHP read from file line by line, declare as variable, then use in MySQL query as WHERE column_name equals variable

Good day, My aim with the script (overview) is to let PHP read from text file line by line then declare the line as variable that will be used in a MySQL statement as a where clause into an array, then ultimately writing to another text file.

CODE:

$lines = file('/root/prcode');
foreach($lines as $line) {
        $query = "select * from $db_table where code=$line";
        $result = mysqli_query($conn,$query);
        while($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
        $out = "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],
$row[8],$row[9],$row[10],$row[11],$row[12],n";

        //file_put_contents($outfile, $out);
        //$fp = fopen($outfile, 'w');
        //fwrite($fp, $out);
        //fclose($fp);
echo $out;
//echo $line;
//echo $query;
//echo $result;
//echo $row;
};
}

File prcode’s first 10 lines:

60025909170
*
.05005140160
0000000000000000
0000000000000001
0000000000000004
0000000000000005
0000000000000007
0000000000000010
0000000000000011

OUTPUT when script is run from Linux shell:

0010,0000,60025909170,01,,,,,,,,,,
0010,0000,60025909170,02,,,,,,,,,,
0010,0000,60025909170,03,,,,,,,,,,
0010,0000,60025909170,04,,,,,,,,,,
0010,0000,60025909170,05,,,,,,,,,,
0010,0000,60025909170,06,,,,,,,,,,
0010,0000,60025909170,07,4.000,36.960,-4.000,-36.960,,,,,,
0010,0000,60025909170,08,4.000,36.960,,,,,,,,
0010,0000,60025909170,09,4.000,36.960,,,,,,,,
0010,0000,60025909170,10,4.000,36.960,,,,,,,,
0010,0000,60025909170,11,4.000,36.960,,,,,,,,
0010,0000,0000060025909170,01,,,,,,,,,,
0010,0000,0000060025909170,02,,,,,,,,,,
0010,0000,0000060025909170,03,,,,,,,,,,
0010,0000,0000060025909170,04,,,,,,,,,,
0010,0000,0000060025909170,05,,,,,,,,,,
0010,0000,0000060025909170,06,,,,,,,,,,
0010,0000,0000060025909170,07,,,,,,,,,,
0010,0000,0000060025909170,08,,,,,,,,,,
0010,0000,0000060025909170,09,,,,,,,,,,
0010,0000,0000060025909170,10,-4.000,-.040,4.000,.040,,,,,,
0010,0000,0000060025909170,11,-4.000,-.040,,,,,,,,
0010,0000,60025909170,12,4.000,36.960,,,,,,,,
0010,0000,0000060025909170,12,-4.000,-.040,,,,,,,,

From the output, it can be established that the order of the text file is not being followed as 60025909170 and 0000060025909170 are 2 different products and 0000060025909170 is at about line 32000 <- my first problem. To try to rectify this and cater for following the order, the special characters and spaces I tried:

$query = "select * from $db_table where code='$line'";  \causes empty output
$query = "select * from $db_table where code="$line""; \causes empty output
$query = "select * from $db_table where code='$line'"; \empty output

The echos below were just to test to see which variables are set or not to try to further troubleshoot:

echo $out;
//echo $line;
//echo $query;
//echo $result;
//echo $row;

The second problem is, none of the attempts to write to file worked, it would either write just a single line and nothing more or would not write at all.

Any advise or guidance on how to possibly fix my 2 issues?

Answer

1st issue: Likely, your file is being read in order, but your query is finding all of those rows. You can see if this is happening by adding the line number to your output. Try changing your foreach to foreach($lines as $lineNum => $line) { and your output to $out = $lineNum.implode(',', $row)."n"; If you have the same lineNum for 60025909170 and 0000060025909170, then you know that the query is matching both.

2nd issue: You just need to add a flag of FILE_APPEND to the file_put_contents. Like this: file_put_contents($outfile, $out, FILE_APPEND);

Advertisement

Answer