Directory List and MySql

[cutielou22]

Here is The Code:

if ($handle = @opendir("../../images/year/$year/")) {
    $blacklist = array('.', '..', 'index.php', 'index.html');
    while (false !== ($file = readdir($handle))) {
        if (!in_array($file, $blacklist)) {
	$stmt = $mysqli->prepare("SELECT id, image, title, caption, secureid FROM photogallery WHERE image = ? AND year = ? AND timeofday = ? LIMIT 5");
	$stmt->bind_param('sis', $file, $year, $timeofday);
	$stmt->execute();
	$stmt->store_result();
	$stmt->bind_result($id, $image, $title, $caption, $photoid);
	//$stmt->fetch();
	
	while ($stmt->fetch())
	{
		if (($id) && ($photoid)){
		$idcheck = "<font color=\"green\">Found in Database</font>";
		$admin = "<a href=\"../edit/photos.php?photoid=$photoid\">Edit</a>";
		$details = "<b>$title</b> $caption";
		} else {
		$idcheck = "<font color=\"red\">Not Found in Database</font>";
		$admin = "<a href=\"../add/photos.php?image=$file&year=$year&timeofday=$timeofday\">Add</a>";
		$details = "";
		}
		
		echo "$image $details $idcheck $admin<br>";
	}
	$stmt->close();
	
            echo "$file $details $idcheck $admin<br>";
		}
    }
    closedir($handle);
} else {echo "This image directory was not found.";}

 

What It is Doing:

Right now it is only showing the ones found in the database.

 

What I Want It To Do:

I want it to show all the results found in the database and the ones that are not found in the database. So I can see everything.

 

If you need any more information. Please ask.

[cutielou22]

Ran some extra tests and changed coding slightly (getting different results):

if ($handle = @opendir("../../images/year/$year/")) {
    $blacklist = array('.', '..', 'index.php', 'index.html');
    while (false !== ($file = readdir($handle))) {
        if (!in_array($file, $blacklist)) {
		
	if($stmt = $mysqli->prepare("SELECT id, image, title, caption, secureid FROM photogallery WHERE image = ? AND year = ? AND timeofday = ?"))
	{
	$stmt->bind_param('sis', $file, $year, $timeofday);
	$stmt->execute();
	$stmt->store_result();
	$stmt->bind_result($id, $image, $title, $caption, $photoid);
	$stmt->fetch();
	$stmt->close();
	}
	
		if ((isset($id)) && (isset($photoid)) && (isset($image))){
		$idcheck = "<font color=\"green\">Found in Database</font>";
		$admin = "<a href=\"../edit/photos.php?photoid=$photoid\">Edit</a>";
		$details = "<b>$title</b> $caption";
		} else {
		$idcheck = "<font color=\"red\">Not Found in Database</font>";
		$admin = "<a href=\"../add/photos.php?image=$file&year=$year&timeofday=$timeofday\">Add</a>";
		$details = "";
		}
		
		echo "$file $image $details $idcheck $admin<br>";
	
		}
    }
    closedir($handle);
} else {echo "This image directory was not found.";}

What It is Doing:
Right now it is only reading the first file through the database and adding those results to the others file in the directory.

 

EXAMPLE: This is what it is doing now.

BoatDock2004.jpg BoatDock2004.jpg Take Off Found in Database Edit
Weigh-in2004.jpg BoatDock2004.jpg Take Off Found in Database Edit
FirstPlace2004.jpg BoatDock2004.jpg Take Off Found in Database Edit

What I Want It To Do:
I want it to show all the results regardless if found in the database or not and with the correct information (right now it is not showing the correct information afterwards as shown in the example above).

 

EXAMPLE: Notice how image files are now matching up. (They do not necessarily need to match up maybe return blank if not found in the database.)

BoatDock2004.jpg BoatDock2004.jpg Take Off Found in Database Edit
Weigh-in2004.jpg Weigh-in2004.jpg NOT Found in Database Edit
FirstPlace2004.jpg FirstPlace2004.jpg NOT Found in Database Edit

Edited March 3, 2013 by cutielou22

[teynon]

I'll move on to answering your question in a moment, but I'd like to hit another point first.

 

I would recommend against running a query for every single file. If you end up with 500 files, you'll be running 500 queries. I might suggest a different approach.

 

You could either get the entire list of files first and put it into an array and query using something like "where image IN ()".

 

Or depending on you database, you could get the entire list of files in the database and compare that as you run through the files. I would prefer the first option, but from what I remember with MySQLi, it's kind of a PITA to get it to work with arrays. I typically prefer PDO.

 

The reason you are getting the "BoatDock2004.jpg" again and again is because those values are being set once. When the statement returns no results, there are no replacements.

 

So the quick fix for you here is to set the values at the beginning of each loop.

if ($handle = @opendir("../../images/year/$year/")) {
    $blacklist = array('.', '..', 'index.php', 'index.html');
    while (false !== ($file = readdir($handle))) {
        if (!in_array($file, $blacklist)) {
            $id = "";
            $photoid = "";
            $image = "";
            $title = "";
            $caption = "";
            $idcheck = "Not found in database.";
            $admin = "<a href=\"../add/photos.php?image=$file&year=$year&timeofday=$timeofday\">Add</a>";
            $details = "";
            if ($stmt = $mysqli->prepare("SELECT id, image, title, caption, secureid FROM photogallery WHERE image = ? AND year = ? AND timeofday = ?")) {
                $stmt->bind_param('sis', $file, $year, $timeofday);
                $stmt->execute();
                $stmt->store_result();
                $stmt->bind_result($id, $image, $title, $caption, $photoid);
                $stmt->fetch();
                $stmt->close();
            }

            if ((!empty($id)) && (!empty($photoid)) && (!empty($image))) {
                $idcheck = "<font color=\"green\">Found in Database</font>";
                $admin = "<a href=\"../edit/photos.php?photoid=$photoid\">Edit</a>";
                $details = "<b>$title</b> $caption";
            }

            echo "$file $image $details $idcheck $admin<br>";
        }
    }
    closedir($handle);
} else {
    echo "This image directory was not found.";
}

 

Edits: - Fighting the code block formatting...

Edited March 3, 2013 by teynon

[cutielou22]

I agree with not running a query for every single file. I didn't think of that.

 

But the code you gave doe's work thank you for the help and suggestions.