$session problem with remove data function

[Guest]

Hello,

I am puzzled as to how to get this code actually working, I am using some example code, I want the code to remove the data of the logged in users, at the moment it will remove all users registered in the mysql db.

<?php
# Access session.
session_start() ;

if ( !isset( $_SESSION[ 'user_id' ] ) ) { require ( 'login_tools.php' ) ; load() ; }

# Connect to the database.
require ('connect_db.php');

// Attempt delete query execution
$sql = "DELETE FROM users WHERE user_id=user_id";
if(mysqli_query($dbc, $sql, $q)){
    echo "Records were deleted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbc);
}
# Close database connection.
mysqli_close( $dbc ) ;
?>

$_session id knows that the user is logged into the control page, but for the code to remove the particular user logged in from the db, what exactly does this need?

Thank you.

[Barand]

I suggest you read your query carefully, bearing in mind that the condition "user_id = user_id" is true for all records.

[Guest]

Sure, but for example id 1, or 2, 3, 4, if that user is logged in, which that code above

 

# Access session. session_start() ;

if ( !isset( $_SESSION[ 'user_id' ] ) ) { require ( 'login_tools.php' ) ; load() ; }

The $_session needs to know which user, 1, or what ever to remove the correct one from the users table. So how does this work out? I will need to add a timer,  so remove in three days after the function has been initiated.

Are you getting at, that user_id is not correct to use?

Edited November 16, 2019 by Guest
Question

[Barand]

At the moment, your query says

"DELETE all records from the user table where the value in column "user_id" is equal to the value in column "user_id".

Is it obvious to you yet why every record gets deleted?

You need to fix your query so it compares the value in column user_id against your session value.

[Guest]

Right yeah, I'm thinking back on this.

I don't know how to get the specific user removed after three days, I don't know where to begin. I've been stuck on this for a while.

[Barand]

One way would be to add an "expiry_date" (default NULL) column to your user table.

Instead of deleting the record, update the record setting the expiry date to CURRENT_DATE+3 days.

Run a job every day that does a "DELETE FROM user WHERE expiry_date < CURRENT_DATE

[Guest]

I got a copy of in Easy steps book, and it is php 7. But it isn't much of 7.

I then downloaded their example files from an old book, php & Mysql.

So I am trying to add and edit to my user, at the moment, it is a basic registration system, so a user can login. And I want to add, change password, remove data, perhaps upload an image, and display submitted comments onto an index page of each registered user.

Currently, it is a public forum when wanting to post a comment.

But for the moment, I want to get this remove data working, as it is important with running any type of user site, especially since GDPR. I am not sure what I can do with it. But I am trying this out. I've been messing with the example code for months but gave up on it.

[Guest]

// Attempt delete query execution
$sql = "DELETE FROM users WHERE user_id=user_id";
if(mysqli_query($dbc, $sql, $q)){
    echo "Records were deleted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbc);
}

// Attempt delete query execution
  $sql = "UPDATE FROM users WHERE user_id=user_id";
if(mysqli_query($dbc, $sql, $q)){
    echo "Records were deleted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($dbc);
}

So where echo is CURRENT_DATE+3 days

 

[Barand]

1 minute ago, tryingphp said:

$sql = "UPDATE FROM users WHERE user_id=user_id";

I can see I have been talking to myself. I have better things to than waste more time on you.

[maxxd]

I recommend reading this page - https://www.php.net/manual/en/language.variables.basics.php

[Guest]

I even posted on a youtube about this sessions problem last month, and no response. Along with the example, with which the above is also from another site's example.

 

[maxxd]

OK - this is not a session problem, it's a problem of not understanding variables or SQL.

Your SQL statement is currently looping through every record in the table and saying 'update the current record if the ID value of the current record is equal to the ID of the current record'. Which, as Barand pointed out, will always be true. So if you're wanting to only update one specific record at a time, you'll have to get the user_id of the specific record you want to update and then assign that value to a php variable (which begins with a dollar sign), which you will then use as the value of `user_id` in the WHERE clause.

[Guest]

I understand what a variable is, a keyword for a particular item.

$q is the key value for anything to do with what I have been using.

So $user_id as it is, isn't going to work for a particular record, there is the id column, but that would only do the same task, remove all from id column.

$sql = "UPDATE FROM users WHERE user_id=id";

That won't work.

id =1 or 2 in the db. How will the user I am logged in which is 1, how is the function going to read that? Sessions was a part of this, I was mentioned months back on another forum.

[Barand]

Try this

// Attempt delete query execution
$stmt = $dbc->prepare("DELETE FROM users WHERE user_id = ? ");   // prepare query with placeholder (?) for id value
$stmt->bind_param('i', $_SESSION['user_id']);                    // bind the id value to the placeholder

if ($stmt->execute()) {                                          // execute the query
    echo "Records were deleted successfully.";
} else {
    echo "ERROR: Not able to execute query " ;
}

 

Edited November 17, 2019 by Barand

[Guest]

Barand, that actually worked! 🙂

As for adding a time delay, so three days, that requires an edit in the code? Of CURRENT_DATE+3

Thank you.

Edited November 17, 2019 by Guest

[Barand]

On 11/16/2019 at 7:37 PM, Barand said:

One way would be to add an "expiry_date" (default NULL) column to your user table.

Instead of deleting the record, update the record setting the expiry date to CURRENT_DATE+3 days.

Run a job every day that does a "DELETE FROM user WHERE expiry_date < CURRENT_DATE

This time, read what I said.

[Guest]

I have added the expiry_date column as Null,

("DELETE FROM users WHERE user_id = CURRENT_DATE+3 ");

 

UPDATE FROM users WHERE user_id = ?

 

$stmt = $dbc->prepare("DELETE FROM users WHERE user_id = ? "); // prepare query with placeholder (?) for id value

$stmt->bind_param('i', CURRENT_DATE+3, $_SESSION['user_id']);

These won't work.

 

Edited November 17, 2019 by Guest

[ginerjm]

Do you ever read your code when you write it?

"delete records where user_id = a date value?"

I thought user ids were integers like 1,2 or 3 as you used earlier.  Now they are date values?

[Barand]

30 minutes ago, tryingphp said:

("DELETE FROM users WHERE user_id = CURRENT_DATE+3 ");

The thing about programming is that it requires some thought. Why would as user_id be equal to a date value?

Why don't you do some reading about how to use SQL instead of taking the "infinite monkeys with typewriters" approach in the hope you eventually come up with a right answer?

On 11/16/2019 at 7:37 PM, Barand said:

Instead of deleting the record, update the record setting the expiry date to CURRENT_DATE+3 days

[Guest]

I actually got a copy of SQL from in easy steps just recently, but it isn't like this type of code, it is more about messing with the sql in a db control panel. I thought it could of helped with viewing comments from the database, as well as removing the comments from a particular id.

I have send a couple of recommendations to the company to make a book on some the tasks I have been trying. I hope they do.

I'm bad at web languages. I tried to learn python language several years ago to make a basic game, but it didn't work, as well Visual studio did that at a college over twelve years back.

I was first trying w3schools, their examples. I then found the delete code on another site more than a month ago, had to tinker with it, and now I arrived here, and thanks to you, Barand, I have something going here.

In the comparing values chapter, this is example one.

# use the "my_database" database
USE my_database;

# create a table called "clock radios"
CREATE TABLE IF NOT EXISTS clock_radios
(
  code		CHAR(8)		PRIMARY KEY,
  make	 	VARCHAR(25)	NOT NULL,
  model		VARCHAR(25)	NOT NULL,	
  price		DECIMAL(4,2)	NOT NULL
);

# insert 5 records into the "clock_radios" table
INSERT INTO clock_radios (code, make, model, price) 
  VALUES ("512/4792", "Alba", "C2108", 6.75);
INSERT INTO clock_radios (code, make, model, price) 
  VALUES ("512/4125", "Hitachi", "KC30", 8.99);
INSERT INTO clock_radios (code, make, model, price) 
  VALUES ("512/1458", "Philips", "AJ3010", 19.99);
INSERT INTO clock_radios (code, make, model, price) 
  VALUES ("512/3669", "Morphy Richards", "28025", 19.99);
INSERT INTO clock_radios (code, make, model, price) 
  VALUES ("512/1444", "Sony", "C253", 29.49);

# show records in "clock_radios" if price is below 19.99
SELECT * FROM clock_radios WHERE price < 19.99;

# show records in "clock_radios" if price is above 19.99
SELECT * FROM clock_radios WHERE price > 19.99;

# show records in "clock_radios" if price is 19.99 or less
SELECT * FROM clock_radios WHERE price <= 19.99;

# delete this sample table
DROP TABLE IF EXISTS clock_radios;

 

Edited November 17, 2019 by Guest
SQL book code example

[Psycho]

@tryingphp you need to slow down. If you don't understand something, figure it out first or ask a question. It appears you are just copy/pasting code without understanding what it does and then throwing random edit at it. I don't see that you even acknowledged the original problem which @Barand and @ginerjm both stated. Your original query was simply deleting every record where the value in the user_id field was equal to itself. You wanted to delete those records where the user_id was equal to $_SESSION['user_id']! Your original query would look like this

$sql = "DELETE FROM users WHERE user_id = $_SESSION['user_id']";

But, don't use that - it is unsafe. You should use prepared queries such as Barand provided (do some research, way too much info to explain in a forum post). I only provided it in that fashion because it is easier to visualize what was wrong.

Then, Barand provides a different, more efficient solution and you try to integrate it into your current solution. Let me break it down for you:

1. Create a new field in the DB - I would suggest a timestamp or datetime. I assume this is a for a logout function, so the field could be logout_datetime. If not, name it something appropriate for the function being performed.

2. When the process is initiated, perform an update query (using the session value = user_id) and set the value in that field to either A) the current datetiem using NOW() or B) to the datetime three days in the future. I would use option A (explained below)

3. Have a scheduled task that runs each day/hour/whatever which will delete all the records based on the datetime value in the field above.

If you go with option A, then this delete function would need to delete all records where the value is < today-3 days. Something like this:

DELETE FROM users WHERE logout_datetime < (DATE(NOW()) - INTERVAL 3 DAY)

If you go with option B, the logout function would run an update query similar to the above, but the interval would be +3 and the delete query would look something like this:

DELETE FROM users WHERE logout_datetime < NOW()

In either case, Do not use a query based on the values being equal. If the process was to ever not run one day you would have records that would exist indefinitely. So, you want to delete all records that are 3 or more days old. The reason I would go with option A is that if you ever needed to change the logic on the time period on which to delete, you could change it in the DELETE query and it will apply to all existing records. Otherwise, if you put the 3 day period as part of the update query, any changes would only apply to new records.

[Guest]

I've messed with php for two years, I first tried youtube, I then last year bought a couple of books, and it didn't work out.

I then went from one login system to another, and finally this basic working one from the downloads at in Easy steps. Which is php 5 I would guess.

I then got the original function delete code from a site, and now Barand has supplied me with something that works. Finally.

And sure, I don't understand php very well at all. I'm terrible at it.

As for this option A or B, this is for one user at a time.

So create a new column and name it datetime and then select, the following are available, datetime, Timestamp

 

Edited November 18, 2019 by Guest
Added images

[Psycho]

41 minutes ago, tryingphp said:

As for this option A or B, this is for one user at a time.

So create a new column and name it datetime and then select, the following are available, datetime, Timestamp

As I understand your requirements, there appears to be some process that is initiated by the user that initiates the code you are currently working on. I assume it is a logout process, but you have not explicitly stated. Right now, the code is deleting the record, but you later stated

I will need to add a timer,  so remove in three days after the function has been initiated.

. So, there needs to be TWO separate events: One to set the timer and one to delete the records.

1) Modify your existing code for the event so it  sets the datetime/timestamp value for the user initiated action - this will occur for each user individually.

2) Create a process that runs daily (or whatever interval you decide) which will delete the records associated with all users where the applicable time period has elapsed. This is a single query that executes for all applicable records at once.

Do NOT name your field datetime. That is a field type in MySQL and is a "reserved word". You would have to take special steps to prevent errors in your query if you have a field named using a reserved word. Give it a name that is representative of what it is: expirationDate, logoutDate, etc.

[Guest]

Correct, like if you had a link in the control panel on this forum, you then click remove, and then record or account will be removed in three days time.

// Attempt delete query execution
$stmt = $dbc->prepare("DELETE FROM users WHERE logout_datetime < (DATE(NOW()) - INTERVAL 3 DAY) = ? ");   // prepare query with placeholder (?) for id value
$stmt->bind_param('i', $_SESSION['user_id']);                    // bind the id value to the placeholder

if ($stmt->execute()) {                                          // execute the query
    echo "Records were deleted successfully.";
} else {
    echo "ERROR: Not able to execute query " ;
}

Removedate?

Edited November 18, 2019 by Guest

[Psycho]

1 hour ago, tryingphp said:

Correct, like if you had a link in the control panel on this forum, you then click remove, and then record or account will be removed in three days time.

// Attempt delete query execution
$stmt = $dbc->prepare("DELETE FROM users WHERE logout_datetime < (DATE(NOW()) - INTERVAL 3 DAY) = ? ");   // prepare query with placeholder (?) for id value
$stmt->bind_param('i', $_SESSION['user_id']);                    // bind the id value to the placeholder

if ($stmt->execute()) {                                          // execute the query
    echo "Records were deleted successfully.";
} else {
    echo "ERROR: Not able to execute query " ;
}

Removedate?

I think you are misunderstanding something - why is there a placeholder for id in the delete query? Go back and read my last post and pay attention to the very explicit details I posted for steps #1 and #2. Then go back to the post I made before that where I provided example queries for #2 and neither included a user_id.

I think the problem is that you have an incorrect assumption in your head and every new piece of data you are trying to align it with that assumption. You need to read the responses and not assume. It really isn't that difficult. I will try one more time to explain this.

1. When a user selects an option to logout you will run a process to set a logout time for that specific user. In this case you will get their user id from the session and run an UPDATE query similar to what Barand provided using the user_id to determine which records to apply the update to:

$stmt = $dbc->prepare("UPDATE users SET logout_datetime = NOW() WHERE user_id = ? ");
// prepare query with placeholder (?) for id value
$stmt->bind_param('i', $_SESSION['user_id']);
$stmt->execute()

2. Run a scheduled task to delete the records of ALL USERS where the logout_datetime is more than 3 days in the past. It will only use the logout_datetime field to determine which records to delete (i.e. no user_id). Also, there is no need to use a prepared statement since this does not use any input variables.

$query = "DELETE FROM users WHERE logout_datetime < (DATE(NOW()) - INTERVAL 3 DAY)"; //No user_id
$stmt = $pdo->query($query)

Edited November 18, 2019 by Psycho