PHP problem - bit complicated. Can anyone have a look? Thanks

Hi people. I'm new. Im working on a php content management system as part of a project and im having some trouble with my admin script.

What the following script does is bring together all unauthorised user submitted files. When files are submitted via a simple web form (different script) the script makes use of the $_FILES multidimensional array, inserting the properties of the file to the 'uploads' table, and another function stores the file into its physical location on the server.

Along with these descriptive attributes (upload_id, size, type ect) is an 'approved' field which is automatically set to 'N' (i.e. non approved).

Here is my 'uploads' db scheme:

CREATE TABLE uploads(
upload_id int( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
file_name VARCHAR( 30 ) NOT NULL ,
file_size INT( 6 ) UNSIGNED NOT NULL ,
file_type VARCHAR( 30 ) NOT NULL ,
description VARCHAR( 100 ) DEFAULT NULL ,
upload_date DATETIME NOT NULL ,
approved CHAR(1) DEFAULT 'N',
PRIMARY KEY ( upload_id ) ,
KEY file_name( file_name )
)

My view_files script checks checks if the file exists on the server, and then its approved status in the upload table. It will only display files with a 'approved' set to 'Y'. If they are approved, the files are displayed with a link for download - I hope i havent lost anyone here.

Ok so my admin script collects together all files that have their approved field set to 'N' - non approved. It lists the file name, type and a link for previewing the file.

On this script I have checkboxes next to the files, along with an approved and delete button. Selecting which files via the checkboxes and hitting the 'approve' button runs an SQL update query to change the selected files' approved status to 'Y' - thereby making them viewable on the users' side and removing them from the list of 'currently unauthorised' files.

The checkbox is part of an array that is constructed using a foreach() function..i.e. '"if the approve button is clicked, for each checkbox selected, do the following"

NOW then..I also have the delete button, the purpose of which is to remove any dodgy files that may have been submitted. This makes use of the same checkbox array. What I want this to do is for each checkbox selected, not only delete the upload_id from the database, but also the actual file on the server.

Here is the code:

<?php #Admin submission approval check

$page_title = "View Files";
include_once ('../includes/cms_admin_header.html');

require_once ('../../../mysql_connect2.php');//opens the database connection

if (isset($_POST['submit'])) {

foreach ($_POST['select'] as $key => $value) { // For each item selected do the following.

// $key is the file's unique id
$query = "UPDATE uploads SET approved = 'Y' WHERE upload_id = '$key'";
$result = mysql_query($query);
}
}
if ($result) { // If it ran OK, display the message
echo '<h3>Files successfully added to the database</h3>';
include_once ('../includes/cms_admin_footer.html');
exit();
}

if (isset($_POST['delete'])) {//if delete is pressed

foreach ($_POST['select'] as $key => $value) {//for each item selected do the following

// $key is the file's unique id
$query = "DELETE from uploads WHERE upload_id = '$key'";
$result = mysql_query($query);
}
}

if ($result) { // If it ran OK, display the message
echo '<h3>Files successfully deleted</h3>';
include_once ('../includes/cms_admin_footer.html');
exit();
}

echo "Greetings Administrator";

$query = "SELECT upload_id, file_name, file_type, description, DATE_FORMAT(upload_date, '%M %e, %Y') AS d FROM uploads WHERE approved = 'N' ORDER BY upload_date DESC";
$result = mysql_query($query);

if ($result) {

echo "<p><b>All the user submitted files</b></p>";

//Begin Parsing of data into table
echo "<form action=\"{$_SERVER['PHP_SELF']}\" method=\"post\" />
<table border=\"1\" cellpadding=\"4\" width=\"100%\">
<tr>
<td>
<b>Name</b>
</td>
<td>
<b>Type</b>
</td>
<td>
<b>Description</b>
</td>
<td>
<b>Link</b>
</td>
<td>
<b>Date Submitted</b>
</td>
<td>
<b>Select</b>
</td>
</tr>";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

echo " <tr>
<td>
{$row['file_name']}
</td>
<td>
{$row['file_type']}
</td>
<td>
{$row['description']}
</td>
<td>
<a href=\"../download_file.php?uid={$row['upload_id']}\">Preview</a>
</td>
<td>
{$row['d']}
</td>
<td>
<input type=\"checkbox\" name=\"select[{$row['upload_id']}]\" value=\"1\">
</td>";
}

echo " </tr>
</table>";

echo "
<INPUT TYPE=\"submit\" name=\"submit\" value=\"Approve\">
<INPUT TYPE=\"submit\" name=\"delete\" value=\"Delete\">";

} else {

echo '<p>There has been an error</p><p>' . mysql_error() . '</p>';

}

include_once ('../includes/cms_admin_footer.html');
?>


Right now, when i hit the delete button, nothin happens. All i want it to do is delete the selected upload_ids from the table, thereby removing its trace in my database. I would also like to delete the actual file from the server but im not sure how to do that.

When i hit the approve button, my update query works fine and the approved status is changed and the files no longer appear on my admin script.

Basically my DELETE query just refuses to work. If I change that query with any other type of query it works when i hit the delete button..but the DELETE FROM query itself just falls flat. To test this i assigned the exact same code as the approved button to the delete button..so now selecting files and hitting either button (approve/delete) does exactly the same thing.

I a completely stumped. I have no idea what to do

Just wondered if someone could have a look for me..i am new to php and maybe im not seeing an obvious error. I just want to be able to delete selected file records (via checkboxes) from my uploads table.

Thanks for your time.

Kind regards

-Pallaris

The first thing I'd do is add an 'or die(mysql_error())' to the end of your mysql_query() call to see what the database'll tell you. The code looks ok, though, so my first guess is that the user you're connecting as doesn't have DELETE privileges.

Madpawn, thank you so much. Now I feel so stupid..the problem was, as you suspected, the lack of DELETE privileges for the user i was connecting as!!

Thanks for pointing out the obvious. Honestly..I should have thought of that one.

So that now successfully removes the file description from the uploads table. What I would like to do is also have the ability to remove the actual file from the folder on the server (where all the uploads are stored). I'm not so sure how to do this..something about the unlink() function. Could this be part of the delete query in my code...i.e..for all items selected, remove the record from the database..AND the file from location 'x'.

Any suggestions on how to do this would be most weclome. Thanks a lot for your help.

Regards

-P

You just need to have the filename and unlink() it. It would probably be easiest to just SELECT the filename before you remove the record, but there are other options if you don't want to run a separate SELECT each time.

The best option would probably be to actually set the filename to it's id as it's uploaded, then you can use the id passed through your form to do both.

Actually, something like this may work, since you've already got all your upload_id's in an array:



I know it seems redundant to reselect your upload_id, but that way, you can remove the file from the db and the filesystem at the same time without the headache of aligning your select array and your form array.

Hey Mudpwan, thanks for the tip. I've tried using that code (changed a few parameters). here it is:



But i am getting the following error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in c:\web\project\cms\admin\check_submissions.php on line 26 - which happens to be the first query line.

Im not sure what the problem is - the query looks fine to me

So after some playing around i tried this:



Which is pretty much what i had before, except with the inclusion of the unlink() as partof the clause. This sucessfully deletes selected upload records from the uploads table but the unlink doesnt seem to work and i'm greeted with this error:

Warning: unlink(../../../uploads): Permission denied in c:\web\project\cms\admin\check_submissions.php on line 52


I tried this online on my server however and i get a different message:

PHP Warning: unlink(../../../uploads/): Is a directory in ............../project/cms/admin/check_submissions.php on line 52


Is there anything obviously wrong here? Any advice would be much appreciated. Thanks a lot for pointing me in the right direction here....i'm very grateful.

Regards

-P

Yeah, the first thing's my fault -- for it to work, you need to convert $_POST['select'] to a comma-separated string first.

Either way, though, you'll still have the filepath errors. Without having your setup in front of me, I won't be able to help much with that. I'm pretty terrible with filepath issues myself, and I usually just end up having to pound at it until it works. Your local system's problem sounds like it's just a permissions issue, though -- check your uploads folder to see if the user you're logged in as has remove permissions.

Hey Mudpawn..

.

Im not sure i fully understand that. Do you mean define what POST['select'] is first?

Also just to be sure i've set global priveleges to the mysql user im using to connect and im still getting he permission denied error.

Online though the error is different again - warning me that ../../../uploads/ is in fact a directory.

edit - ok i've managed to get your script to work with this:



except now i get this error after clicking the delete button:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM uploads WHERE upload_id IN ('Array')' at line 1

:\..

$_POST['select'] is an array, and you can't just dump arrays into strings the way I did -- I was typing faster than I was thinking, unfortunately. You could use implode() to change that into a string:



Then you can dump $select_list into the query. Sorry 'bout that.

In the code you posted, your unlink is looking for upload_id, not file_name. If the filename's different, you will have to pull it out of the db before you can try to unlink() it, of course. I think the error you got from your server, though, came from the fact that you don't have a slash between your uploads directory name and the filename, so your path is being read as ../../../uploadsfilename instead of ../../../uploads/filename.

As for your local error, your MySQL permissions aren't the ones unlink() checks. You need to check the uploads folder itself -- it will also have a set of permissions that determine who can delete objects inside that folder.

Im using windows XP, when i right click the uploads folder within my webserver I only have the following options under the sharing tab:

- share folder on network - checked
-allow network users to change files - checked

and under the general tab I can hit advance i can uncheck the 'read only' box..and apply.

I can't see anywhere else where i can change permissions.. any ideas?

Ok, I now have the following code which produces no error:



Trouble is the files aren't removed from the server, but the upload record is successfully deleted from the uploads table.

So basically i think i've narrowed it down to the unlink line..

The problem is that you're trying to loop through the results of your DELETE query (with another mysql_query() call, too). DELETE queries only return the number of rows deleted. Try it like this instead:



Of course, you're still not going to have a $row['file_name'] unless you run a SELECT somewhere.

OK, thanks, so this is what I now have: