Link to home
Start Free TrialLog in
Avatar of sbsupport
sbsupportFlag for United Kingdom of Great Britain and Northern Ireland

asked on

PHP, MySQL, Delete attached files from SQL and Directory

Hello,

I'm trying to figure out how to "Delete attached files from SQL and Directory" when a checkbox is checked...
I have attached the code I'm working with at present but i'm now stuck.

Any help would be very appreciated!

Thanks
=========
index.php
=========
<form enctype='multipart/form-data' action='process_form.php' method='post'>
<input type='checkbox' name='del' value='yes'>
<input type='submit' value='Delete'>
</form>
 
================
process_form.php
================
if($del =='yes'){
  // delete file stored in the 'attachment' field
  @$res = "select attachment from helpdesk_data where id='$id'";
    if($row=@mysql_fetch_row($res)){
      if($row[0]!=''){
      @unlink("./files/".$row[0]);
  }
}
  @$res = "delete from helpdesk_data where id='$id'";
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Roonaan
Roonaan
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sbsupport

ASKER

Thanks for the reply

The ID should be there.. The full code attached.
=========
index.php
=========
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="table.css" type="text/css">
</head>
<body background="/Themes/default/images/main_bg.gif" style="margin: 0 0 0 0;">
<?
 
$idsel = $_GET['id'];
$search = $_GET['search'];
$sort = $_GET['sort'];
$order = $_GET['order'];
 
 if($order =='ASC'){
 $vorder = 'DESC';
 }
 else{
 $vorder = 'ASC';
 }
 
@$vstart = $_GET["vstart"];
if($vstart =='')
    $vstart =0;
 
$host = "localhost";
$username = "root";
$password = "password";
$db = "helpdesk";
$table ="helpdesk_data";
 
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
 
//total number of records in the table
$res = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table`");
@$rows = mysql_num_rows ($res);
 
if($order ==''){
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE id LIKE \"%$search%\" OR (start LIKE \"%$search%\" or  name LIKE \"%$search%\" or  subject LIKE \"%$search%\" or enquiry LIKE \"%$search%\" or  follow_up LIKE \"%$search%\") ORDER BY complete ASC, id DESC LIMIT $vstart,25");}
else{
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE id LIKE \"%$search%\" OR (start LIKE \"%$search%\" or  name LIKE \"%$search%\" or  subject LIKE \"%$search%\" or enquiry LIKE \"%$search%\" or  follow_up LIKE \"%$search%\") ORDER BY $sort $order LIMIT $vstart,25");}
 
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
echo "<table bgcolor='#E9E9E9' align='center' width='740'><td><form>
<input type='hidden' name='id' value='$idsel'>
<input type='hidden' name='sort' value='complete asc,'>
<input type='hidden' name='order' value='id desc'>
<input name='search' type='text' class='TextBox' value='$search'>&nbsp;
<input type='submit' value='search'></td></table>";
$cols = mysql_num_fields($result);
$records = mysql_num_rows ($result);
 echo "<table align='center' bgcolor='FFFFFF' width='740' cellspacing='1' cellpadding='0' border='0' >";
 echo "<tr bgcolor='505050' class='menu'>";
 
 for ($i = 0; $i < $cols;$i++)
 {
    echo "<td align='center'><a href='?id=$idsel&search=$search&sort=".mysql_field_name($result,$i)."&order=$vorder&vstart=$vstart'>".mysql_field_name($result,$i)."</a></td>";
 }
 echo "</tr>";
 while ($row = mysql_fetch_array($result, MYSQL_NUM))
 {
    echo "<tr bgcolor='F6F6F6' class='normal'>";
 
$idget = $row[0];
 
   foreach ($row as $value)
   { echo "<td class='table'><a href='?id=$idget&search=$search&sort=$sort&order=$order&vstart=$vstart'>".$value ."</a></td>";
   } 
   echo "</tr>";
 }
 $end = $vstart + $records;
 echo "<tr>
<td width='30'></td>
<td width='80'></td>
</tr>
 <tr  align = 'center' bgcolor = '505050' class='menu'><td colspan=$cols>";
 
     if($vstart != 0)
    {
    $prev = $vstart - 25;
    echo "<font size='2'>|</font> <a href='?id=$idsel&search=$search&sort=$sort&order=$order&vstart=$prev'>Previous</a> <font size='2'>|</font>";
    }
 
 echo " Records $vstart  to  $end  of $rows ";
 
      if($vstart<$rows-25)
     {
       $next = $vstart + 25;
       echo "<font size='2'>|</font> <a href='?id=$idsel&search=$search&sort=$sort&order=$order&vstart=$next'>Next</a> <font size='2'>|</font>";
    }
 
echo "</td></tr></form><form enctype='multipart/form-data' action='process_form.php' name='myform' method='post'></table>";
 
  if ($id) {
    $sql = "SELECT * FROM helpdesk_data WHERE id=$id";
    $result = mysql_query($sql);
    $myrow = mysql_fetch_array($result);
    $start = $myrow["start"];
    $complete = $myrow["complete"];
    $name = $myrow["name"];
    $email = $myrow["email"];
    $subject = $myrow["subject"];
    $enquiry = $myrow["enquiry"];
    $ext = $myrow["ext"];
    $follow_up = $myrow["follow_up"];
    $finish = $myrow["finish"];
    $assigned_to = $myrow["assigned_to"];
    $attachment = $myrow["attachment"];
 
echo "<input type='hidden' name='id' value='$id'>";
  }
 
$date = date("d-m-y H:i");
 
 if($start ==''){
 $startvar = $date;
 }
 else{
 $startvar = $start;
 }
 
  if($complete =='no'){
 $c_sel1 = ' selected';
 }
  if($complete =='yes'){
 $c_sel2 = ' selected';
 }
 
  if($assigned_to ==''){
 $a_sel1 = ' selected';
 }
  if($assigned_to =='JD'){
 $a_sel2 = ' selected';
 }
   if($assigned_to =='ICD'){
 $a_sel3 = ' selected';
 }
   if($assigned_to =='PC'){
 $a_sel4 = ' selected';
 }
   if($assigned_to =='APS'){
 $a_sel5 = ' selected';
 }
 
echo "
<script language='javascript' type='text/javascript' src='actb.js'></script>
<script language='javascript' type='text/javascript' src='search_suggestions.php'></script>
<script language='javascript' type='text/javascript' src='email.js'></script>
<table width='740' border='0' align='center' cellpadding='0' cellspacing='0'>
  <tr> 
    <td width='740'><img src='images/top.gif' width='740' height='22'></td>
  </tr>
  <tr> 
    <td background='images/middle2.gif' width='740'>
	<table width='740' border='0' align='center' cellpadding='0' cellspacing='0'>
        <tr>
          <td width='740'>
              <table width='100%' border='0' align='center' cellpadding='3' cellspacing='0'>
                <tr>
				</td>&nbsp;ID $id
                  <td width='10%'> <div align='right'><font color='#FF0000'>
					*</font> Name :</div></td>
                  <td width='328'> 
					<input name='name' type='text' class='TextBox' id='name' size='40' maxlength='40' value='$name' onfocus='actb(this,event,namearray);this.value=this.value;modify(this);' autocomplete='off' />
                  </td>
                  <td width='11%' align='right'><font color='#FF0000'>
					*</font> Start :</td>
                  <td width='39%'> 
					<input name='start' type='text' class='TextBox' id='start' size='40' maxlength='40' value='$startvar'></td>
                </tr>
                <tr> 
                  <td><div align='right'><font color='#FF0000'>
					*</font> E-Mail :</div></td>
                  <td>
					<input name='email' type='text' class='TextBox' id='email' size='40' maxlength='40' value='$email' onfocus='actb(this,event,emailarray);' autocomplete='off' /></td>
                  <td align='right'>Complete :</td>
                  <td>
<select name='complete' class='TextBox'>
<option value='no'$c_sel1>no</option>
<option value='yes'$c_sel2>yes</option>
</select>
</td>
                </tr>
                <tr> 
                  <td> <div align='right'><font color='#FF0000'>
					*</font> Subject :</div></td>
                  <td valign='middle'><input name='subject' type='text' class='TextBox' id='subject' size='40' maxlength='40' value='$subject'></td>
                  <td valign='top' align='right'>Assigned To :</td>
                  <td valign='top'>
<select name='assigned_to' class='TextBox'>
<option value=''$a_sel1></option>
<option value='JD'$a_sel2>JD</option>
<option value='ICD'$a_sel3>ICD</option>
<option value='PC'$a_sel4>PC</option>
<option value='APS'$a_sel5>APS</option>
</select>
</td>
                </tr>
                <tr> 
                  <td valign='top'> <div align='right'><font color='#FF0000'>
					*</font> Enquiry 
                      :</div></td>
                  <td width=328>
					<textarea class=TextBox name=enquiry cols=60 rows=10>$enquiry</textarea>
                  </td>
                  <td valign='top' align='right'>Follow Up :</td>
                  <td valign='top'>
					<textarea class=TextBox name=follow_up cols=60 rows=10>$follow_up</textarea></td>
                </tr>
                <tr> 
                  <td><div align='right'><font color='#FF0000'>
					*</font> Ext :</div></td>
                  <td> 
					<input name='ext' type='text' class='TextBox' id='ext' size='3' maxlength='3' value='$ext'> 
                  </td>
                  <td align='right'>Finish :</td>
                  <td> 
					<input name='finish' type='text' class='TextBox' id='finish' size='40' maxlength='40' value='$date'></td>
                </tr>
                <tr> 
                  <td></td>
<td>
Upload new file  <input type='file' name='attachment' class='TextBox'>
<input type='checkbox' name='del' value='yes'><br>
Attached: <a target='_blank' href='/support/admin/files/$attachment'><font color='red'>$attachment</font></a><br>
</td>
<td align='right'></td>
<td><br>
<input name='new' type='submit' value='New Record'>&nbsp;&nbsp;
<input name='update' type='submit' value='Update Record'>
<input name='reset' type='submit' value='Reset'>
</form>
					</td>
                </tr>
                </table>
          </td>
          <td width='0'>
              &nbsp;</td>
          </tr> 
        </table></td>
  </tr>
    </tr>
  <tr>
    <td width='740'><img src='images/bottom.gif' width='740' height='1'></td>
  </tr>
</table>";
?>
</body>
</html>
 
================
process_form.php
================
<?php
 
// Receiving variables
@$new = $_POST['new'];
@$update = $_POST['update'];
@$del = $_POST['del'];
@$id = addslashes($_POST['id']);
@$start = addslashes($_POST['start']);
@$complete = addslashes($_POST['complete']);
@$name = addslashes($_POST['name']);
@$email = addslashes($_POST['email']);
@$subject = addslashes($_POST['subject']);
@$enquiry = addslashes($_POST['enquiry']);
@$ext = addslashes($_POST['ext']);
@$follow_up = addslashes($_POST['follow_up']);
@$finish = addslashes($_POST['finish']);
@$assigned_to = addslashes($_POST['assigned_to']);
@$attachment_Name = $_FILES['attachment']['name'];
@$attachment_Size = $_FILES['attachment']['size'];
@$attachment_Temp = $_FILES['attachment']['tmp_name'];
@$attachment_Mime_Type = $_FILES['attachment']['type'];
 
if($del =='yes'){
	// delete file stored in the 'attachment' field
	@$res = "select attachment from helpdesk_data where id='$id'";
	if($row=@mysql_fetch_row($res)){
		if($row[0]!=''){
			@unlink("./files/".$row[0]);
		}
	}
 
	@$res = "delete from helpdesk_data where id='$id'";
}
 
function RecursiveMkdir($path)
 {
   if (!file_exists($path)) 
   { 
      RecursiveMkdir(dirname($path));
      mkdir($path, 0777);
    }
  }
 
if( $attachment_Size == 0){}
else
{
$uploadDir = "../support/admin/files/";
$uploadFile = "../support/admin/files/".$attachment_Name ;
$fileExt = substr(strrchr($attachment_Name, "."), 1);
$randName = md5(rand() * time());
  {
  @chmod(dirname($uploadFile), 0777);
  }
$filePath = $uploadDir . $randName . '.' . $fileExt;
@move_uploaded_file( $attachment_Temp , $filePath);
chmod($filePath, 0644);
$attachment_URL = "http://forum/support/admin/files/".$randName . '.' . $fileExt;
$md5Name = $randName . '.' . $fileExt;
}
 
 if($new =='New Record'){
 //saving record to MySQL database
@$pfw_strQuery = "INSERT INTO helpdesk_data (start, complete, name, email, subject, enquiry, ext, assigned_to, follow_up, finish, attachment) values (" . (($start != "") ? "'$start'" : "NULL") . ", " . (($complete != "") ? "'$complete'" : "NULL") . ", " . (($name != "") ? "'$name'" : "NULL") . ", " . (($email != "") ? "'$email'" : "NULL") . ", " . (($subject != "") ? "'$subject'" : "NULL") . ", " . (($enquiry != "") ? "'$enquiry'" : "NULL") . ", " . (($ext != "") ? "'$ext'" : "NULL") . ", " . (($assigned_to != "") ? "'$assigned_to'" : "NULL") . ", " . (($follow_up != "") ? "'$follow_up'" : "NULL") . ", " . (($finish != "") ? "'$finish'" : "NULL") . ", " . (($attachment != "") ? "'$md5Name'" : "NULL") . ")";
@$pfw_host = "localhost";
@$pfw_user = "root";
@$pfw_pw = "Ae3B7DdG2";
@$pfw_db = "helpdesk";
$pfw_link = mysql_connect($pfw_host, $pfw_user, $pfw_pw);
if (!$pfw_link) {
 die('Could not connect: ' . mysql_error());
}
$pfw_db_selected = mysql_select_db($pfw_db, $pfw_link);
if (!$pfw_db_selected) {
die ('Can not use $pfw_db : ' . mysql_error());
}
 
//insert new record
$pfw_result = mysql_query($pfw_strQuery);
if (!$pfw_result) {
 die('Invalid query: ' . mysql_error());
}
mysql_close($pfw_link);
}
 
 if($update =='Update Record'){
  //updating record in MySQL database
@$pfw_strQuery = "UPDATE helpdesk_data SET " . "start=" . (($start != "") ? "'$start'" : "NULL") . ", " . "complete=" . (($complete != "") ? "'$complete'" : "NULL") . ", " . "name=" . (($name != "") ? "'$name'" : "NULL") . ", " . "email=" . (($email != "") ? "'$email'" : "NULL") . ", " . "subject=" . (($subject != "") ? "'$subject'" : "NULL") . ", " . "enquiry=" . (($enquiry != "") ? "'$enquiry'" : "NULL") . ", " . "ext=" . (($ext != "") ? "'$ext'" : "NULL") . ", " . "assigned_to=" . (($assigned_to != "") ? "'$assigned_to'" : "NULL") . ", " . "follow_up=" . (($follow_up != "") ? "'$follow_up'" : "NULL") . ", " . "finish=" . (($finish != "") ? "'$finish'" : "NULL") . ", " . ($md5Name!="" ? "attachment='$md5Name'" : ($HTTP_POST_VARS['attachment_remove'] != 1 ? "attachment=attachment" : "attachment=NULL")) . " where id='$id'";
@$pfw_host = "localhost";
@$pfw_user = "root";
@$pfw_pw = "password";
@$pfw_db = "helpdesk";
$pfw_link = mysql_connect($pfw_host, $pfw_user, $pfw_pw);
if (!$pfw_link) {
 die('Could not connect: ' . mysql_error());
}
$pfw_db_selected = mysql_select_db($pfw_db, $pfw_link);
if (!$pfw_db_selected) {
die ('Can not use $pfw_db : ' . mysql_error());
}
 
//updating record
$pfw_result = mysql_query($pfw_strQuery);
if (!$pfw_result) {
 die('Invalid query: ' . mysql_error());
}
mysql_close($pfw_link);
  
  }
 
header('Location: index.php');
die();
?>

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /web/htdocs/help/process_form.php on line 26

You are correct I didn't want to delete the whole row. I just changed it for testing and forgot to change it back.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It doesn't even get to the point of deleting because this error occurs...

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /web/htdocs/help/process_form.php on line 26

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /web/htdocs/help/process_form.php on line 26

Have Changed to the Attached Code but still the above message :(

Thanks
if($del =='yes'){
	// delete file stored in the 'attachment' field
  $res = mysql_query("select attachment from helpdesk_data where id='$id'");
	if($row=mysql_fetch_row($res)){
		if($row[0]!=''){
			unlink("./files/".$row[0]);
		}
	}
$res = mysql_query("UPDATE helpdesk_data SET attachment = '' WHERE id='$id'");
}

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many Thanks, Just me being stupid.

All sorted !!! :)
$host = "localhost";
$username = "root";
$password = "password";
$db = "helpdesk";
$table ="helpdesk_data";
 
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
 
if($del =='yes'){
	// delete file stored in the 'attachment' field
  $res = mysql_query("select attachment from helpdesk_data where id='$id'");
	if($row=mysql_fetch_row($res)){
		if($row[0]!=''){
			unlink("../support/admin/files/".$row[0]);
		}
	}
$res = mysql_query("UPDATE helpdesk_data SET attachment = '' WHERE id='$id'");
}

Open in new window

In post 21069935 snippet, mysql connection is made on line 35.

Now that the mysql_query has been added and the @$res replaced with $res.

Line 291 in the extended snipet above and line 4 in the most recent snipet:
if($row=mysql_fetch_row($res)){ #is the problem  you are assigning an array to a int which means $row will point to the begining of the array of the result which can be access by derreferencing $row into an array format by adding '@' @$row[0]

you should use :
if(@row=mysql_fetch_row($res)){