delete contents of a column in a table using button

Question

I have a table named "users" in my database. My table has 2 columns, "user_id" and "profile". user_id keeps the id of the user that is currently logged in. The profile keeps the url of image that the user has uploaded in his profile page. All I want is to create a button that will delete the contents of profile column for the user that is logged in. Any idea how to move on?

<?php                       
function deleteimage($user_id){
$sql="DELETE `profile` FROM `users`  WHERE 'user_id' = $user_id;";
$result=mysql_query($sql) or die("Error when trying to delete...");
}
?>

<input type="button" value="Delete">

score 1 · Answer 1 · answered May 08 '13 at 10:50

The DELETE keyword is for rows not columns

If you want to empty a column you would UPDATE the record

"UPDATE `users` SET `profile` = NULL  WHERE `user_id` = $user_id;"

Or, if you profile column does not accept NULL values

"UPDATE `users` SET `profile` = ''  WHERE `user_id` = $user_id;"

However, you should be using mysqli_ or PDO as the mysql_ functions are deprecated and being removed from PHP

Also, please ensure you are escaping the $user_id variable you are passing into this method otherwise you could be leaving yourself open to SQL injection vulnerabilities

or '' instead of NULL if the column is set as NOT NULL – RMcLeod May 08 '13 at 10:51 — RMcLeod, May 08 '13 at 10:51

score 1 · Answer 2 · answered May 08 '13 at 10:52

1

if you want to make column emply you can also set blank value or Null value

UPDATE `users` SET `profile` = '' WHERE 'user_id' = $user_id;

or

UPDATE `users` SET `profile` = NULL  WHERE 'user_id' = $user_id;

answered May 08 '13 at 10:52

liyakat

11,825
2
40
46

You need to put backticks around the `user_id` column in the where clause. At the moment its a string which would cause a SQL error. Just a heads up. – fullybaked May 08 '13 at 11:18
i just paste from php my admin with static and change variable sorry for that – liyakat May 08 '13 at 11:56

score 0 · Answer 3 · answered May 08 '13 at 10:51

0

You have to set 1 global variable which will contain user_id of logged in user.

And on button click you can call a PHP with GET or POST with this user_id(global variable) value as a parameter .

It should work.

answered May 08 '13 at 10:51

Yogeshkumar

46
3

rcbevans · Accepted Answer · 2013-05-08T11:51:59.393

You need to set up the connection to your database first.

I have three files, the main PHP script like yours, a db_config file:

<?php
/**
 * Database config variables
 */
define("DB_SERVER", "%address%");
define("DB_USER", "%username%");
define("DB_PASSWORD", "%password%");
define("DB_DATABASE", "%dbname%");
?>

Then a connect utility script:

<?php

/**
 * A class file to connect to database
 */
class DB_CONNECT {

    // constructor
    function __construct() {
        // connecting to database
        $this->connect();
    }

    // destructor
    function __destruct() {
        // closing db connection
        $this->close();
    }

    /**
     * Function to connect with database
     */
    function connect() {
        // import database connection variables
        require_once __DIR__ . '/db_config.php';

        // Connecting to mysql database
        $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());

        // Selecing database
        $db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());

        // returing connection cursor
        return $con;
    }

    /**
     * Function to close db connection
     */
    function close() {
        // closing db connection
        mysql_close();
    }

}

?>

Then you can use this in your main script to open the connection:

// include db connect class
require_once __DIR__ . '/db_connect.php';

at the beginning to open the connection to your database. You can then do a query as you did in your question above. This format makes it nice and easy to have queries going from multiple PHP scripts as the connector a separate class.

You can then do the logic for executing that SQL query when the button is clicked

For removing the image URL you want something like

UPDATE `users` SET `profile` = NULL WHERE `user_id` = $user_id

You can't directly access the button click as it were. The PHP is just telling the client page to display a button. If you want to receieve when it was clicked you need to use something like a POST self-referral like below to make a button which when clicked sends a signal back to the server page:

<form action="<?=$_SERVER['PHP_SELF'];?>" method="post">
<input type="submit" name="submit" value="Click Me">
</form>

Then, at the top of your PHP page you can do

if(isset($_POST['submit'])) {
    deleteimage($user_id);
}

To run the function to do the URL delete function only once the button is clicked.

To migrate your script to MYSQLI for future compatibility, take a look at the oracle wiki guide here.

It has a tool which you point at your PHP scripts and it will make the changes for you. Took me about 2 mins to convert all my scripts

Hope this is helpful

I have try it, looks great until now, any idea how to make the button to call UPDATE `users` SET `profile` = NULL WHERE `user_id` = $user_id — 33528, May 08 '13 at 11:00
I put them in the following order: 1.function deleteimage 2.if(isset($_POST['submit'])) and 3. the form. The problem now is that I get the message Undefined variable: user_id. Is there a problem in the order I used? — 33528, May 08 '13 at 11:19
You should not be using the `mysql_` functions in PHP anymore. This isn't the best advice I'm afraid... — fullybaked, May 08 '13 at 11:20
I agree, however, depending on the stage of script development, it may be easier (and I am) getting mysql functional as it is what I know, then migrate to mysqli as suggested here http://stackoverflow.com/questions/4598854/how-do-i-migrate-my-site-from-mysql-to-mysqli — rcbevans, May 08 '13 at 11:33
@Stefanos $user_id is a a placeholder which you have to assign a value to before you can use. Use PHP sessions or a post or get variable to get the user_id which you want to have the profile URL removed from — rcbevans, May 08 '13 at 11:34

delete contents of a column in a table using button

4 Answers4