13 Replies - 662 Views - Last Post: 08 April 2013 - 05:07 PM Rate Topic: -----

#1 mandaralo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 30-March 13

Delete form posted in X number of days

Posted 30 March 2013 - 02:40 PM

I've been creating a website fro a school project and i ran into a problem. The website is a book posting website so people can post books that they have. When the form is posted, a date and time entry is auto submitted with the form. Since the users don't have their own way of deleting their post, I want to create a PHP file that auto deletes the post 6 days prior to submition.

I've looked everywhere and most forums tell me to write a php file then use a cron job. The problem is how to write this php according to my files already.


include('connect-mysql.php');
	
    $date = $_POST['date'];
	$date = date('Y-m-d');
        $fname = $_POST['fname'];
	$lname = $_POST['lname'];
	$phonenumber = $_POST['phonenumber'];
	$email = $_POST['email'];
	$bookname = $_POST['bookname'];
	$isbnnumber = $_POST['isbnnumber'];
	$bookcondition = $_POST['bookcondition'];
	$price = $_POST['price'];
	$info = $_POST['info'];
	
	$sqlinsert = "INSERT INTO people (date, firstname, lastname, phonenumber, email, bookname, isbnnumber, bookcondition, price, info) VALUES ('$date', '$fname', '$lname', '$phonenumber', '$email', '$bookname', '$isbnnumber', '$bookcondition', '$price', '$info')";



This is a part of the code im using to post into the database.

Please help me figure this out.

<input type="hidden" name="submitted" value="true" />
<input type="hidden" name="date" value="<?= $date ?>">



This is also part of my files.

I don't know how to write it accordingly with the date.

Is This A Good Question/Topic? 0
  • +

Replies To: Delete form posted in X number of days

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3034
  • Posts: 10,584
  • Joined: 08-August 08

Re: Delete form posted in X number of days

Posted 30 March 2013 - 02:55 PM

  • Using outdated, insecure mysql* functions should cost you points on any school project. Read up on PDO prepared statements.
  • You shouldn't delete any posts. Instead, you should only show posts whose date is on or after 6 days ago.

Was This Post Helpful? 0
  • +
  • -

#3 joshgriggs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 31-March 13

Re: Delete form posted in X number of days

Posted 31 March 2013 - 05:26 PM

As mentioned by CTphpnwb, it is bad practice to use unprepared SQL statements / insecure mysql functions.

However, you could go about storing the date in UNIX timestamp format (number of seconds since 1/1/70). You can easily add 6 days to the current timestamp using something like:
$sixdays = time() + (6*24*60*60)


Then instead of running a cron job that would delete posts older than six days just pull the posts that are younger than six days using a simple query such as
"SELECT * FROM table WHERE date_expire > ".time();

This would just pull posts where the expiry date in seconds is more than the current timestamp.

Hope this helps you grasp it a little
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3554
  • View blog
  • Posts: 10,333
  • Joined: 08-June 10

Re: Delete form posted in X number of days

Posted 01 April 2013 - 07:41 AM

storing a UNIX timestamp is usually a bad idea since DBs can do better math with DATE (or DATETIME) fields. and each DB engine usually has lots of date related functions (that do include DST and leap year corrections).

about the deletion I am of the same opinion as CT, there is no need to delete entries.
Was This Post Helpful? 0
  • +
  • -

#5 joshgriggs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 31-March 13

Re: Delete form posted in X number of days

Posted 01 April 2013 - 11:47 AM

I do agree Dormilich, just thought it would be a nice simple solution for this small project :)
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Delete form posted in X number of days

Posted 01 April 2013 - 06:19 PM

No offence, joshgriggs, but if you teach people inferior techniques to quickly solve their small projects, they will bring those with them when they start doing bigger projects. Best to just leave those techniques behind altogether.

Not that using Unix timestamps like that is actually any simpler than if you were to use DATETIME or TIMESTAMP types instead.
SELECT `stuff` FROM `table` 
WHERE `date` + INTERVAL 6 DAY > NOW()


Was This Post Helpful? 0
  • +
  • -

#7 joshgriggs  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 31-March 13

Re: Delete form posted in X number of days

Posted 02 April 2013 - 11:40 AM

No offence taken, can see where you're coming from, my mistake :)
Was This Post Helpful? 0
  • +
  • -

#8 mandaralo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 30-March 13

Re: Delete form posted in X number of days

Posted 02 April 2013 - 06:09 PM

I've tried one of the methods and its given me an error. This is how i had it set up before additions. I could press the search key and all the data will be shown.

 $category = $_POST['category'];
 $criteria = $_POST['criteria'];
 $query = "SELECT * FROM people WHERE $category LIKE '%".$criteria."%'";
 $result = mysqli_query($dbcon, $query,) or die('error getting data');
 $num_rows - mysqli_num_rows($result);



After revision
 $category = $_POST['category'];
 $criteria = $_POST['criteria'];
[b] $date = $_POST['date'];[/b]
 $query = "SELECT * FROM people WHERE $category LIKE '%".$criteria."%'";
 [b]$show = "SELECT * FROM people WHERE $date + INTERVAL 6 DAY > NOW()";[/b]
 $result = mysqli_query($dbcon, $query, [b]$show[/b]) or die('error getting data');
 $num_rows - mysqli_num_rows($result);



This is how i have my date setup in the database.

$date = $_POST['date'];
	$date = date('Y-m-d');


What am I doing wrong?
Was This Post Helpful? 0
  • +
  • -

#9 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Delete form posted in X number of days

Posted 02 April 2013 - 06:32 PM

What you are doing wrong there is that you are trying to execute two separate SQL queries using a single mysqli_query call. That's not possible. - Look at the manual entry for the function. There is only one $query parameter there.

In the larger sense, your error is that you seem to be misunderstanding how to use the information we just gave you with your existing code. The query I posted earlier just demonstrated how you can filter a result set to exclude entries who's date value are more than 6 days old. What you should be doing with this info is combining it with your existing query, not executing it in a new query all by itself.

In a SELECT query, the WHERE clause can take multiple conditions, all of which will be applied to the result set and the set reduced accordingly. So if I do:
SELECT name FROM people
WHERE
    name LIKE 'A%' AND
    gender = 'female' AND
    date_of_birth >= NOW() - INTERVAL 18 YEAR


This will return all the rows in the table for women who's names start with "A" and who are 18 or older.

Do you see what I mean?
Was This Post Helpful? 0
  • +
  • -

#10 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3034
  • Posts: 10,584
  • Joined: 08-August 08

Re: Delete form posted in X number of days

Posted 03 April 2013 - 07:43 AM

View Postmandaralo, on 02 April 2013 - 09:09 PM, said:

What am I doing wrong?

You're not following my first instruction.

View PostCTphpnwb, on 30 March 2013 - 05:55 PM, said:

  • Using outdated, insecure mysql* functions should cost you points on any school project. Read up on PDO prepared statements.

Was This Post Helpful? 0
  • +
  • -

#11 mandaralo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 30-March 13

Re: Delete form posted in X number of days

Posted 08 April 2013 - 01:34 PM

so i kind of got it
$category = $_POST['category'];
 $criteria = $_POST['criteria'];
 $date = $_POST['date'];
 $query = "SELECT * FROM people WHERE $category LIKE '%".$criteria."%' AND 'date' >= NOW() -6" ;
 $result = mysqli_query($dbcon, $query) or die('error getting data');
 $num_rows - mysqli_num_rows($result);



after NOW(), I cant seem to get the date 6 days before NOW. What am I missing?

Thanks
Was This Post Helpful? 0
  • +
  • -

#12 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Delete form posted in X number of days

Posted 08 April 2013 - 01:39 PM

If you do NOW() - 6, that'll convert the date string into a number and then reduce it by 6 seconds. Not at all what you want to happen there.

To get the date 6 days prior to today, either use the DATE_SUB() function, or use the shortcut syntax:
NOW() - INTERVAL 6 DAY


Was This Post Helpful? 0
  • +
  • -

#13 mandaralo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 30-March 13

Re: Delete form posted in X number of days

Posted 08 April 2013 - 04:18 PM

Thanks

This is what im using and is working perfectly

$category = $_POST['category'];
 $criteria = $_POST['criteria'];
 $date = $_POST['date'];
 $query = "SELECT * FROM people WHERE $category LIKE '%".$criteria."%' ANDSELECT * FROM `people` WHERE `date` BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND CURDATE()" ;
 $result = mysqli_query($dbcon, $query) or die('error getting data');
 $num_rows - mysqli_num_rows($result);



View Postmandaralo, on 08 April 2013 - 04:17 PM, said:

Thanks

This is what im using and is working perfectly

$category = $_POST['category'];
 $criteria = $_POST['criteria'];
 $date = $_POST['date'];
 $query = "SELECT * FROM people WHERE $category LIKE '%".$criteria."%' AND `date` BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND CURDATE()" ;
 $result = mysqli_query($dbcon, $query) or die('error getting data');
 $num_rows - mysqli_num_rows($result);


Was This Post Helpful? 0
  • +
  • -

#14 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Delete form posted in X number of days

Posted 08 April 2013 - 05:07 PM

You're welcome.

You can shorten that a bit by using the shortcut syntax I mentioned:
AND `date` + INTERVAL 6 DAY >= CURDATE()


The end result is the same. It's just a little shorter.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1