Query doesn't work

[Sir Osis of Liver]

Can't get this to work -

Code Select Expand



// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected to database<br />";

    $directory = 'forum411/custom_avatar';

    if (!is_dir($directory)) {
        exit('Invalid directory path');
    }

    $files = array();
    foreach (scandir($directory) as $file) {
        if ($file !== '.' && $file !== '..') {
            $files[] = $file;

echo '$file = ', $file ,'<br />';

$sql = "SELECT id_attach, file_hash FROM smf_attachments WHERE filename=$file";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo '<br> id: '. $row["id_attach"] .' hash: '. $row["file_hash"] .'<br>';
    }
} else {
    echo "0 results";
}

        }
    }

$conn->close();



Output -

Code Select Expand



Connected to database
$file = avatar_1006_1391155472.png
0 results$file = avatar_1015_1392742937.png
0 results$file = avatar_1018_1392292492.png
0 results$file = avatar_1019_1391705788.png
0 results$file = avatar_1033_1415736908.png
0 results$file = avatar_1042_1403992470.png
0 results$file = avatar_1067_1393361915.png



If I remove WHERE clause it outputs id_attach and file_hash, but seems to get stuck in infinite loop.

[shawnb61]

At first glance, I think you need braces around the file, {$file}, so it will get evaluated.

Quotes, too, or it thinks it's another column name...

One more: if you do a print_r on $conn & $result, that will help ID issues.

[Sir Osis of Liver]

Will try it tonight, thanks.

[Sir Osis of Liver]

There's something basically wrong with the logic here, and I'm not getting it.  This -

Code Select Expand



$files = array();
foreach (scandir($directory) as $file) {
if ($file !== '.' && $file !== '..') {
$files[] = $file;

echo '$file = ', $file ,'<br />';

$sql = "SELECT id_attach, file_hash FROM smf_attachments WHERE filename= 'avatar_166_1430756565.png' ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo '<br> id: '. $row["id_attach"] .' hash: '. $row["file_hash"] .'<br>';
    }
} else {
    echo "0 results";
}
        }
    }



Returns this -

Code Select Expand



id: 5252 hash: e1d7d32a089c965764abf2db9e133ec30511cf6c
$file = avatar_1610_1483783698.png

id: 5252 hash: e1d7d32a089c965764abf2db9e133ec30511cf6c
$file = avatar_1629_1449301999.png

id: 5252 hash: e1d7d32a089c965764abf2db9e133ec30511cf6c
$file = avatar_166_1430756565.png

id: 5252 hash: e1d7d32a089c965764abf2db9e133ec30511cf6c
$file = avatar_1688_1422762387.png

id: 5252 hash: e1d7d32a089c965764abf2db9e133ec30511cf6c
$file = avatar_1690_1433014155.png

id: 5252 hash: e1d7d32a089c965764abf2db9e133ec30511cf6c
$file = avatar_1696_1421741412.png



id_attach and file_hash are correct for avatar_166_1430756565.png, but same values are repeated for all 500+ files.

[Sir Osis of Liver]

Got it -

Code Select Expand



$sql = "SELECT id_attach, file_hash FROM smf_attachments WHERE filename= '$file' ";



Now what to do with it.

[Sir Osis of Liver]

Shawn, if you're interested here are the scripts that fix files 2.1 upgrade changes.

avatarfix.php

Code Select Expand



<?php

// Restores hashed avatar file names that have been changed by 2.1 upgrade

$servername = "localhost";
$username = "user";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected to database<br /><br />";

$directory = 'custom_avatar';

if (!is_dir($directory)) {
exit('Invalid directory path');
}

$files = array();
foreach (scandir($directory) as $file) {
if ($file !== '.' && $file !== '..') {
$files[] = $file;

echo '$file = ', $file;

$sql = "SELECT id_attach, file_hash FROM smf_attachments WHERE filename= '$file' ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo '<br /> id: '. $row["id_attach"] .' hash: '. $row["file_hash"] .'<br /><br />';

   $newname = $row["id_attach"] .'_'. $row["file_hash"];
   echo '$newname = ' . $newname .'<br />';

   rename ($directory.'/'.$file, $directory.'/'.$newname);

    }
} else {
    echo "0 results";
}
        }
    }

$conn->close();

?>



attachfix.php

Code Select Expand



<?php

// Strips .dat extension added to attachment hash by 2.1 upgrade

echo 'Running attachfix.php';

$directory = 'attachments+dat/';
foreach (glob($directory."*.dat") as $filename) {
    $file = realpath($filename);
    rename($file, str_replace(".dat","",$file));
}

?>