dsjoes Posted January 3, 2011 Share Posted January 3, 2011 the code below creates a table that lets me choose a row to delete from mysql database <?php $host="host"; // Host name $username="user"; // Mysql username $password="pass"; // Mysql password $db_name="testdocs"; // Database name $tbl_name="Docs"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY id"; else { $sql = "DELETE FROM $tbl_name WHERE"; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " id='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])) header('Location: index.php'); // redirect ?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="1" cellpadding="3" cellspacing="1"> <tr> <td colspan="5" align="center"><strong>Testimonials</strong> </td> </tr> <tr> <td align="center"><strong>Select</strong></td> <td align="center"><strong>ID</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>Message</strong></td> <td align="center"><strong>Download</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"> </td> <td><? echo $rows['id']; ?></td> <td><? echo $rows['Name']; ?></td> <td><? echo $rows['Message']; ?></td> <td><? echo $rows['Download']; ?></td> </tr> <tr> <td colspan="5" align="center"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php } mysql_close(); ?> </table> </form> </td> </tr> </table> but what i am trying to do is add unlink() to the script so when i delete a row it deletes the linked file. this bit is where the filename is shown like test.doc and the files are stored in the directory /test_docs <td><? echo $rows['Download']; ?></td> is there anyway to do it so it deletes the file Quote Link to comment Share on other sites More sharing options...
jake2891 Posted January 3, 2011 Share Posted January 3, 2011 so whats the problem? is unlink not deleting the file or what? Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 3, 2011 Author Share Posted January 3, 2011 i am wanting to impliment unlink in the above code so when i delete data from my sql database it deletes the file that is linked in the download column. this is where the name of the file is displayed like test.doc <td><? echo $rows['Download']; ?></td> so what i want is for unlink to get the file name from Download and then delete it Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 3, 2011 Share Posted January 3, 2011 assuming the name of the file is in the same table as the records you are deleting, you've already got the record ids. just query the name of each file using the record id, then unlink() the files. Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 3, 2011 Author Share Posted January 3, 2011 yes it is in the same table but i don't know how query and add the unlink() Quote Link to comment Share on other sites More sharing options...
litebearer Posted January 3, 2011 Share Posted January 3, 2011 Psuedo code. at the point in your script where you are going to delete the record, use this... $query = "SELECT * FROM tablename WHERE id='$id_of_record_to_be_deleted'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $file_name = $row['name_of_field_containing_name_of_file_to_be_deleted']; unlink($file_name}; $query1 = "DELETE FROM tablename WHERE id='$id_of_record_to_be_deleted'"; $result2 = mysql_query($query1); Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 4, 2011 Author Share Posted January 4, 2011 Psuedo code. at the point in your script where you are going to delete the record, use this... $query = "SELECT * FROM tablename WHERE id='$id_of_record_to_be_deleted'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $file_name = $row['name_of_field_containing_name_of_file_to_be_deleted']; unlink($file_name}; $query1 = "DELETE FROM tablename WHERE id='$id_of_record_to_be_deleted'"; $result2 = mysql_query($query1); i can't get it to work it just gives me a blank table Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 4, 2011 Share Posted January 4, 2011 post your code? Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 4, 2011 Author Share Posted January 4, 2011 i have got it displaying the code again but when i delete it gives me the error Warning: unlink(/admin/docs/) [function.unlink]: No such file or directory in /hermes/bosweb/web230/b2302/ipg.myaccount/test_server/admin/test.php on line 27 and this is the code <?php $host="fulltimeincomepartti.ipagemysql.com"; // Host name $username="xxxxxxx"; // Mysql username $password="xxxxxxxx"; // Mysql password $db_name="testdocs"; // Database name $tbl_name="Docs"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY id"; else { $sql = "DELETE FROM $tbl_name WHERE"; $file_name = $row['Download']; unlink('/admin/docs/'.$file_name); // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " id='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])){ header('Location: index.php'); // redirect exit;}?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="1" cellpadding="3" cellspacing="1"> <tr> <td colspan="6" align="center"><strong>Testimonials</strong> </td> </tr> <tr> <td align="center"><strong>Select</strong></td> <td align="center"><strong>ID</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>Description</strong></td> <td align="center"><strong>Download</strong></td> <td align="center"><strong>Last Modified</strong></td> </tr><?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $rows['id']; ?>"> </td> <td align="center"><?php echo $rows['id']; ?></td> <td align="center"><?php echo $rows['Name']; ?></td> <td align="center"><?php echo $rows['Message']; ?></td> <td align="center"><a href="/admin/docs/<?php echo $rows['Download']; ?>">Download</a></td> <td align="center"><?php echo $rows['Modified']; ?></td> </tr> <tr> <td colspan="6" align="center"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php } mysql_close(); ?> </table> </form> </td> </tr> </table> Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 4, 2011 Share Posted January 4, 2011 where is $row coming from here? $file_name = $row['Download']; Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 4, 2011 Author Share Posted January 4, 2011 it is down near the bottom i forgot to move it and i am not sure where to put it Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 4, 2011 Share Posted January 4, 2011 it has to be before you use it, that's for sure. Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 4, 2011 Author Share Posted January 4, 2011 here is my code <?php $host="xxxx"; // Host name $username="xxxx"; // Mysql username $password="xxxxx"; // Mysql password $db_name="testdocs"; // Database name $tbl_name="docs"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Build SQL query if(!isset($_POST['delete'])) $sql="SELECT * FROM $tbl_name ORDER BY id"; else { $sql = "DELETE FROM $tbl_name WHERE"; // add row id to where section for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) $sql.= "AND "; $sql .= " id='" . $_POST['checkbox'][$i] . "'"; } } $result = mysql_query($sql); if(isset($_POST['delete'])){ header('Location: index.php'); // redirect exit;}?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="1" cellpadding="3" cellspacing="1"> <tr> <td colspan="6" align="center"><strong>Testimonials</strong> </td> </tr> <tr> <td align="center"><strong>Select</strong></td> <td align="center"><strong>ID</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>Description</strong></td> <td align="center"><strong>Download</strong></td> <td align="center"><strong>Last Modified</strong></td> </tr><?php while($rows=mysql_fetch_array($result)){ $file_name = $rows['Download']; unlink('/hermes/bosweb/web230/b2302/ipg.myaccount/test_server/admin/docs/'.$file_name); ?> <tr> <td align="center"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $rows['id']; ?>"> </td> <td align="center"><?php echo $rows['id']; ?></td> <td align="center"><?php echo $rows['Name']; ?></td> <td align="center"><?php echo $rows['Message']; ?></td> <td align="center"><a href="/admin/docs/<?php echo $rows['Download']; ?>">Download</a></td> <td align="center"><?php echo $rows['Modified']; ?></td> </tr> <tr> <td colspan="6" align="center"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> <?php } mysql_close(); ?> </table> </form> </td> </tr> </table> it now works but it deletes all the files that are in the table instead of you the file that is selected. it deletes them when i refresh the page. Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 5, 2011 Author Share Posted January 5, 2011 anyone know how to fix this Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 5, 2011 Share Posted January 5, 2011 when you loop over the files to display them, you unlink them. don't do that. only unlink the files when you mean to delete them, not when you mean to display them. Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 5, 2011 Author Share Posted January 5, 2011 it doesn't work if i put it anywhere else down near the bottom Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 6, 2011 Author Share Posted January 6, 2011 bump Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 6, 2011 Share Posted January 6, 2011 you want to delete files before you list the ones that aren't deleted, right? therefore, you need to delete the files BEFORE the list of files is displayed, not AFTER. If you delete AFTER the list is displayed, you will see images in the list that are actually deleted. Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 7, 2011 Author Share Posted January 7, 2011 where do i need to put it then because it doesn't work above because it is not declared Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 7, 2011 Author Share Posted January 7, 2011 bump Quote Link to comment Share on other sites More sharing options...
Anti-Moronic Posted January 7, 2011 Share Posted January 7, 2011 you want to delete files before you list the ones that aren't deleted, right? therefore, you need to delete the files BEFORE the list of files is displayed, not AFTER. If you delete AFTER the list is displayed, you will see images in the list that are actually deleted. Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 7, 2011 Author Share Posted January 7, 2011 where do i need to put it then because it doesn't work above because it is not declared where do i need to put it then because $rows is declared near the bottom so i can't put unlink an where else Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 7, 2011 Share Posted January 7, 2011 sounds like you need to perform 2 different queries: one to delete the selected files and another one to list the files. Quote Link to comment Share on other sites More sharing options...
jaikob Posted January 7, 2011 Share Posted January 7, 2011 The code you provided was real ugly, try this (replace your header PHP with the below): <?php // Set Global Vars $HOST = "YOUR_HOST_HERE"; $USERNAME = "MYSQL_USERNAME"; $PASSWORD = "MYSQL_PASSWORD"; $DATABASE = "testdocs"; $TABLE = "docs"; // Establish a connection mysql_connect($HOST, $USERNAME, $PASSWORD) or die(mysql_error()); mysql_select_db($DATABASE) or die(mysql_error()); // Query for results if(!isset($_POST['delete'])) { $sql = "select * from `$TABLE` order by `id`;"; } else { $DELETE_ID = mysql_real_escape_string($_POST['delete']); $result1 = mysql_query("select * from `$TABLE` where `id` = '$DELETE_ID';"); $row = mysql_fetch_assoc($result1); mysql_close($result1); // Delete the file on the disk unlink('../admin/docs/'.$row['Download']); // Build your query, kind of weird but alright. $sql = "delete from $TABLE where "); for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) { $sql.= "AND "; } $sql .= " id='" . $_POST['checkbox'][$i] . "'"; } $result = mysql_query($sql); if(isset($_POST['delete'])) { header('Location: index.php'); exit; } } ?> Quote Link to comment Share on other sites More sharing options...
dsjoes Posted January 8, 2011 Author Share Posted January 8, 2011 it shows the table but it is empty is there anything i have missed off <?php // Set Global Vars $HOST = "XXXX"; $USERNAME = "XXXX"; $PASSWORD = "XXXX"; $DATABASE = "testdocs"; $TABLE = "docs"; // Establish a connection mysql_connect($HOST, $USERNAME, $PASSWORD) or die(mysql_error()); mysql_select_db($DATABASE) or die(mysql_error()); // Query for results if(!isset($_POST['delete'])) { $sql = "select * from `$TABLE` order by `id`;"; } else { $DELETE_ID = mysql_real_escape_string($_POST['delete']); $result1 = mysql_query("select * from `$TABLE` where `id` = '$DELETE_ID';"); $row = mysql_fetch_assoc($result1); mysql_close($result1); // Delete the file on the disk unlink('../admin/docs/'.$row['Download']); // Build your query, kind of weird but alright. $sql = ("delete from $TABLE where "); for($i=0;$i<count($_POST['checkbox']);$i++){ if($i != 0) { $sql.= "AND "; } $sql .= " id='" . $_POST['checkbox'][$i] . "'"; } $result = mysql_query($sql); if(isset($_POST['delete'])) { header('Location: index.php'); exit; } } ?> <table align="center" width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="delete" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <table width="400" border="1" cellpadding="3" cellspacing="1"> <tr> <td colspan="6" align="center"><strong>Testimonials</strong> </td> </tr> <tr> <td align="center"><strong>Select</strong></td> <td align="center"><strong>ID</strong></td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>Description</strong></td> <td align="center"><strong>Download</strong></td> <td align="center"><strong>Last Modified</strong></td> </tr> <tr> <td align="center"> <input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $row['id']; ?>"> </td> <td align="center"><?php echo $row['id']; ?></td> <td align="center"><?php echo $row['Name']; ?></td> <td align="center"><?php echo $row['Message']; ?></td> <td align="center"><a href="/admin/docs/<?php echo $row['Download']; ?>">Download</a></td> <td align="center"><?php echo $row['Modified']; ?></td> </tr> <tr> <td colspan="6" align="center"><input name="delete" type="submit" id="delete" value="Delete"></td> </tr> </table> </form> </td> </tr> </table> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.