0

Hi iam trying to delete certain columns data from database.Here is my code for delete query.

//getting column id by comparing the id need to delete the data from that row.
$id=$_GET['id'];
$res = "DELETE rental_annual_rent,
rental_block,
rental_street,
rental_area,
rental_town,
rental_state,
rental_pincode
 FROM house_details 
 WHERE house_details_id='$id'";
$result=mysql_query($res);
if(mysql_affected_rows()){
    echo "successfully deleted";
    session_start();
    header("Location:property.php");
    }else{
    echo "Failure";
    }

First iam inserting house_details data into database but i need to delete only particular data from that columns

user5891511
  • 71
  • 3
  • 9
  • 1
    [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Feb 09 '16 at 18:09
  • 1
    Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Feb 09 '16 at 18:09
  • `$row = mysql_fetch_array($res);` just delete that. You're probably looking for `mysql_affected_rows()` once you use DELETE correctly. – Funk Forty Niner Feb 09 '16 at 18:09
  • 1
    *Hold on there cowboy*, that isn't how DELETE is used http://dev.mysql.com/doc/refman/5.7/en/delete.html unless you want to delete multiple tables. `DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]` and `mysql_fetch_array()` is for SELECT, ***not*** DELETE. – Funk Forty Niner Feb 09 '16 at 18:11
  • Create table y as select a, b, c from x; drop table x; – Strawberry Feb 09 '16 at 18:14
  • @Fred-ii- i have replaced with mysql_affected_rows but it is not working – user5891511 Feb 09 '16 at 18:16
  • reload my comment above. I made a few edits to it. and unsure what it is you want to do here exactly. – Funk Forty Niner Feb 09 '16 at 18:17
  • 1
    You cannot "delete" specific columns within a row. You can update the row and set the column values to `NULL` or an empty string `""`, but you do that with an `UPDATE` statement, not a `DELETE` statement. – Patrick Q Feb 09 '16 at 18:20
  • @Fred-ii- first iam inserting details of property and rental details into house_details table but i need to delete rental_details columns data from house_Details. – user5891511 Feb 09 '16 at 18:20
  • @PatrickQ as i tried with update query also it is not working can anyone update it – user5891511 Feb 09 '16 at 18:21
  • `DELETE FROM house_details WHERE house_details_id='$id'` - That's how and will delete every row associated with `$id`. – Funk Forty Niner Feb 09 '16 at 18:22
  • @Fred-ii- if i do that it will delete entire row but i dont want to delete entire row only some columns i need to delete – user5891511 Feb 09 '16 at 18:22
  • see the answer given below. You can't use DELETE like that, as I already mentioned here in comments. – Funk Forty Niner Feb 09 '16 at 18:28
  • may be the answer can give you other clarification too what you want to change. thanks – Alive to die - Anant Feb 09 '16 at 18:39

1 Answers1

2

What you want is an UPDATE, not a DELETE since you want to keep the row, but just clear/blank/unset certain columns within the row.

$res = "UPDATE house_details SET
    rental_annual_rent = NULL,
    rental_block = NULL,
    rental_street = NULL,
    rental_area = NULL,
    rental_town = NULL,
    rental_state = NULL,
    rental_pincode = NULL
 WHERE house_details_id='$id'";

Note that you should really be sanitizing your id input before using it in the query, you should parameterize it, and you should also migrate from the mysql library to mysqli or PDO

Patrick Q
  • 6,373
  • 2
  • 25
  • 34