I want to remove a row from mysql database instead of deleting it using a column with a 1 to hide and a 0 to show when searching

Question

    <?php
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(-1);

mysql_connect("localhost", "*******", "*******") or die("Connection Failed"); 
mysql_select_db("Naissance-passwords")or die("Connection Failed"); 
$id = '';

if(isset($_POST['id'])) $id = $_POST['id'];
  $id = mysql_real_escape_string($id);

$query = "DELETE FROM system_passwords WHERE id='".$id."'"; 
if(mysql_query($query)){
}
else{ 
} 
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <link rel="stylesheet" type="text/css" href="style.css"/>
        <style>
label{display:inline-block;width:150px;margin-bottom:10px;} <!-- this is for the style of form -->
</style>
</head> 
<body> 
<!-- Nav bar and links -->
<div id="menu">
            <a href="search.php">Search</a>
            <a href="insert.php">Insert</a>
            <a href="update.php">Update</a>
            <a href="remove.php">Remove</a>
        </div>
<form action="remove.php" method="post"> 
ID
<input name="id" type="text" style="
    padding-top: 1px;
    margin-top: 10px;
    border-left-width: 2px;
    margin-left: 50px;
"> 
<input name="Submit" type="submit" value="delete record" /> 
</form>

</body>
</html>

SO this will delete a row by id, but I do not want it to delete, I want to change it so that it looks for a column I am calling "Hide" which will have one of two numbers in it, either a 0 or a 1. If it is a 0 when I use the search function, I want it to show the result, and if it has a 1, then I want it to hide from the search rather than delete as this code will do.

why do yo have a Delete query then? you can do that with an Update query. just add a new column to the table — , Aug 20 '15 at 17:33
What is the code I should use to change it from delete to changing the value so it is hidden in the search? — deansmith, Aug 20 '15 at 17:33
I suppose I am just looking for the code to use instead of the delete — deansmith, Aug 20 '15 at 17:34

Hatem Jaber · Accepted Answer · 2015-08-20T17:40:14.570

2

So you want to do is a soft delete, meaning that you want to flag the record as deleted but not actually delete the data. So you created a column in your table that is is_deleted tinyint(1) or whatever you want to name it. When a delete happens, you simply flag it as a 1 so that when you search you only search records WHERE is_deleted > 0 or !=1...

UPDATE I just read your comments, here is some sample code, maybe it will work out of the box.

Add column:

ALTER TABLE system_passwords
ADD is_deleted tinyint(1) DEFAULT 0;

Your delete query will look like this: enter code here

UPDATE system_passwords
SET is_deleted = 1
WHERE id = ?;

You will have to modify this to work with PHP and the code that you're using.

edited Aug 20 '15 at 17:40

answered Aug 20 '15 at 17:35

Hatem Jaber

2,341
2
22
38

That is kind of the answer. How do I write this in the above code? Sorry, I am really new to this... – deansmith Aug 20 '15 at 17:40
I was assuming that you have an existing database that you need to add a column to it. Once you have the column added you need to use the "soft delete" query to flag it as deleted. – Hatem Jaber Aug 20 '15 at 17:41
Great yeah, I already have the database and column called "hide" so all I need to do is change the "DELETE FROM system_passwords WHERE id='".$id."'"; to the query UPDATE system_passwords SET is_deleted = 1 WHERE id = ?; – deansmith Aug 20 '15 at 17:45
don't use "is_deleted" if you have a column called "hide", no sense in adding an extra column. Please don't forget to mark this as the right answer if I successfully answered your question. – Hatem Jaber Aug 20 '15 at 17:49
@deansmith Essentially, yes. Although, to clarify, you want to use [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) rather than escaping or (horror of horrors) simple catenation. Additionally, you'll want to make sure that wherever you use the thing you're sure `not hide`. Although you may want to use a [bit, bool, or boolean instead of tinyint](http://stackoverflow.com/questions/289727/which-mysql-datatype-to-use-for-storing-boolean-values), in terms of storage it shouldn't matter. – Parthian Shot Aug 20 '15 at 17:52
One more question please. My select statement looks like this $raw_results = mysql_query("SELECT * FROM system_passwords WHERE (`id` LIKE '%".$query."%') OR (`system_name` LIKE '%".$query."%') OR (`database_name` LIKE '%".$query."%') OR (`username` LIKE '%".$query."%') OR (`password` LIKE '%".$query."%') OR (`notes` LIKE '%".$query."%') OR (`additional_info` LIKE '%".$query."%')") or die(mysql_error()); What do I add to this to ignore the "soft deleted entries? – deansmith Aug 20 '15 at 17:54
If you read my response above you will see that I addressed that for you, but here it is again: WHERE hide != 1 – Hatem Jaber Aug 20 '15 at 17:57
how does that fit in with my select statement that I have just posted? – deansmith Aug 20 '15 at 18:02
What you're doing is not efficient to begin with, you want to have a search and allow them to search across multiple columns, this is not the way to go with it. There are many ways to do this, but I think most important at this time for you to do is to learn more about SQL in general and MySQL. This looks like it will be a nightmare to maintain. – Hatem Jaber Aug 20 '15 at 18:11
Well this is why I have registered on here, to learn more from people that know php, sql and mysql better than I do and hopefully one day help those who ask me a question. – deansmith Aug 20 '15 at 18:15

score 0 · Answer 2 · answered Aug 20 '15 at 18:09

0

Your question is not very clear, but if I understand you can do so:

insert column status in your table ( default value = 0)
when you delete a row do an update (status = 1)
when you do a search in where clause add status = 0

answered Aug 20 '15 at 18:09

Gino De Maria

51
8

soft delete a row which adds a 1 to the entry then when searching the database only return results that have a 0 in the hide column, including the other search criteria. – deansmith Aug 20 '15 at 18:21
to all searches you need add this criteria e.g. `// select ....... where ..... AND status = 0` – Gino De Maria Aug 20 '15 at 18:57
I am having trouble finding exactly where to add the hide != 1 to my select * from statement that I added earlier. Anyone know where to put it ? – deansmith Aug 20 '15 at 19:29
`SELECT * FROM system_passwords WHERE hide != 1 ` if you have other criteria add in where clause so `SELECT * FROM system_passwords WHERE hide != 1 AND column! = value AND ......` – Gino De Maria Aug 21 '15 at 19:11

I want to remove a row from mysql database instead of deleting it using a column with a 1 to hide and a 0 to show when searching

2 Answers2