Deleting data from a file && MySQL

[Spock]

Hey guys, first post and I've just returned to PHP after about 4 years of no coding, so be gentle! 

 

In a nutshell I'm creating a photo album and I've pretty much got the majority of it complete, apart from a few tweaks and the obvious ongoing development.

 

I'm at the stage now where I need to moderate images being uploaded, so I've made an admin only script which displays the uploaded images with links that say approve and delete.

 

Uploaded images are stored in uploads/ which are left there until i move them to img/, plus the filename is stored in mysql so I can "<img src='../uploads/".$row['filename']."' width='200'>".

 

Now, I would like to make the Approve button move the image from uploads/ to img/ and I'd like the delete button to remove both the entry from MySQL and the file from the uploads folder and I'm not too sure on how to make it work.

 

Here's what I have so far in the mod.php file (mod for moderation)

 

$image = mysql_query("SELECT * FROM images WHERE id");
while($row = mysql_fetch_assoc($image))
{
echo "
<table>
<td>
<tr>
<img src='../uploads/".$row['filename']."' width='200'><br />
</tr>
</td>
<td>
<tr>
<a href=''>Approve</a> <a href=''>Delete</a>
</tr>
</td>
</table>
";
}

 

You'll have to ignore the table tags, I'm still getting used to positioning items on the screen lol.

 

Any clues would be greatly appreciated

 

Live long and prosper.

[litebearer]

http://webxadmin.free.fr/article/php-move-file-211.php

 

However, really no need to move the images; just add a field  to the db table, a field that is 0 or 1; 0 means the image is awaiting 'approval' so don't display to 'public',  1 means the image has been 'approved'. all the images can be in the same folder

[Spock]

Ahh so I can delete the actual image though so it doesn't waste space?

[Spock]

Link don't work, shameless bump

[litebearer]

Ahh so I can delete the actual image though so it doesn't waste space?

My impression was, only images that you disapprove would be deleted.

 

[Spock]

Okay excellent, i've managed to get both the file and the mysql entry to delete, now for approving I was thinking of using UPDATE, but I'm wondering how I do so in such a way that a single image would have a value changed in mysql rather than them all.

 

I've tried the following but it doesn't work, any ideas?

 

$appfile=$_GET['filename'];
$sql = mysql_query("UPDATE images WHERE filename='$appfile' SET approved='1'");

if ($sql)
{	
echo "Approved!";
echo "<br />";
echo "<a href='mod.php'>Back</a>";
}
else
{
echo "Error.";
echo "<br />";
echo "<a href='mod.php'>Back</a>";
}

[litebearer]

1. echo $appfile to be sure it contains the value you expect.

 

IF it does have the correct value

change this...

$sql = mysql_query("UPDATE images WHERE filename='$appfile' SET approved='1'");

to this...

$sql = mysql_query("UPDATE images SET approved=1 WHERE filename='$appfile'");

[Spock]

Thank you ever so much litebearer, you've been a great help.

 

The final thing I'm struggling with now is displaying the images that have an approved value of 1 instead of 0

 

I thought I was being clever by using an if statement, but that just basically said

 

 

if approved equals 1, then display everything

 

 

So i need to rethink it

 

 

		//Display data
	$get = mysql_query("SELECT * FROM images LIMIT $start, $per_page");
	$approved = mysql_query("SELECT * FROM images WHERE approved");
	$fetchapproved = mysql_fetch_assoc($approved);
	while($row = mysql_fetch_assoc($get))
	{
		// get data
		$filename = $row['filename'];

		echo "<div align='center'><img src='../uploads/".$filename."' / width='600px'></div>";
	}

[TOA]

Close...change

$approved = mysql_query("SELECT * FROM images WHERE approved");

 

to this:

 

$approved = mysql_query("SELECT * FROM images WHERE approved=1");

 

[Spock]

That just sets the variable though, how does it filter them out?

 

Should I reuse the if statement around the whole statement?

[Spock]

Nope, that pretty much does the same thing

[TOA]

That just sets the variable though, how does it filter them out?

 

Should I reuse the if statement around the whole statement?

 

No, it says to get all(*) from images where approved is equal to one. That's what you said you wanted

 

 

[litebearer]

try...

$result = mysql_query("SELECT filename FROM images WHERE approved=1");
while($row = mysql_fetch_array($result)) {
?><div align='center'><img src='../uploads/<?PHP echo $row['filename']; ?>' width='600px'></div><?PHP
}

[Spock]

Ahhh, what I mean is I'm trying to get display 1 approved image per page so you can navigate with previous and next buttons, the other way displayed all the approved images on the one page.

[TOA]

Ahhhhhhh. You need pagination...

 

There's plenty of tutorials here at phpfreaks and also on google.

They'll be easy to find. Basically you set an offset and use LIMIT to return what you want.

 

Look into it

[litebearer]

display one per page - http://www.nstoia.com/sat/disp_pag/one.php

[Spock]

I've already set up pagination, at the moment it will display everything (one per page) that is in uploads/  however I only want it to display the images marked with a 1 in approved

[litebearer]

try changing this...

 

$get = mysql_query("SELECT * FROM images LIMIT $start, $per_page");

to this...

$get = mysql_query("SELECT * FROM images WHERE approved=1 LIMIT $start, $per_page");

and get rid of the other query

 

[Spock]

That sort of works, it only displays the images with approved set to 1, however you can still navigate for the images it doesn't display...I think I might do it the way I originally thought and move the file to a different folder, it'll probably be simpler lol

[litebearer]

perhaps we need to look at the pagination script