Skip navigation
Currently Being Moderated

Can anyone give me some advice on databases?

Apr 11, 2013 9:12 AM

Hi All,

Ive recently created my first database in phpmyadmin so i am new to the whole process. Id like to some how apply a time limit of 60 days to the data that is uploaded to the table, after this time id like the data to be automatically deleted. Is this possible in phpmyadmin and can this be done without changing or making alterations to the code in Dreamweaver? Please see image below to see the structure of my database table.

 

Thank you for any advice!

 

screenshot.jpg

 
Replies
  • Currently Being Moderated
    Apr 11, 2013 10:30 AM   in reply to Ness_quick

    The best way would be to create a CRON job that would execute a SQL statement to delete the data based on the date. Your table would need to include a date column.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2013 10:40 AM   in reply to Ness_quick

    Ness_quick wrote:

     

    Hi All,

    Ive recently created my first database in phpmyadmin so i am new to the whole process. Id like to some how apply a time limit of 60 days to the data that is uploaded to the table, after this time id like the data to be automatically deleted. Is this possible in phpmyadmin and can this be done without changing or making alterations to the code in Dreamweaver? Please see image below to see the structure of my database table.

     

    Thank you for any advice!

     

     

     

    It can be done but not without altering what you have already set-up. So do NOT proceed without making a back-up of your site folder and database.

     

    Consider this:

    You need to create another field in the database called 'date' and choose date as the 'type' for the field. In the date field obviously input the date the record was created. It will only allow you to input the date in this format: 2013-4-11 etc and you pick it from the date-picker in phpMyAdmin, which will pop up when you attempt to insert a date, so it gets inserted accurately.

     

    Then on your Dreamweaver pages you need to add a connection to the database and a mysqli query at the top, like below: (obviously change the "localhost","root","root","databaseName" to your own details and the table name where you want to DELETE FROM, which from memeory I think was 'donations'.

     

    <?php

    $con=mysqli_connect("localhost","root","root","databaseName");

    // Check connection

    if (mysqli_connect_errno())

       {

       echo "Failed to connect to MySQL: " . mysqli_connect_error();

       }

    mysqli_query($con,"DELETE FROM donations WHERE date < DATE_SUB(NOW(), INTERVAL 60 DAY");

    mysqli_close($con);

    ?>

     

    You might also have to add S to 60 DAY. I've just set one up to delete after a date has expired so it only needed to be 1 DAY.

     

     

    However that's not the end of it because now you need to provide the client with the ability to insert the date the record was created? You would need to add another form field to your 'insert' and 'update' pages and set a javascript date-picker to pop up a calendar much like the one in phpMyAdmin.

     

    Food for thought.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2013 10:42 AM   in reply to osgood_

    >You need to create another field in the database called 'date'

     

    Osgood, it's best to always avoid reserved words like 'date' and it also does not describe what the column means enough. Is it the date entered, or updated, or when it should be deleted?  Use something more specific, like trans_date or date_entered.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2013 10:44 AM   in reply to bregent

    bregent wrote:

     

    >You need to create another field in the database called 'date'

     

    Osgood, it's best to always avoid reserved words like 'date' and it also does not describe what the column means enough. Is it the date entered, or updated, or when it should be deleted?  Use something more specific, like trans_date or date_entered.

     

    Yup agreed should be 'createdDate' or 'dateCreated' similar

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2013 10:46 AM   in reply to osgood_

    >However that's not the end of it because now you need to

    >provide the client with the ability to insert the date the record was created?

     

    You don't really need that. Just setup the database column to automatically be set to the current date.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2013 10:53 AM   in reply to bregent

    bregent wrote:

     

    >However that's not the end of it because now you need to

    >provide the client with the ability to insert the date the record was created?

     

    You don't really need that. Just setup the database column to automatically be set to the current date.

     

    Good point, cuts out a lot of work. I was getting carried away because the project I've just finished required dates to be set in the future.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2013 3:09 AM   in reply to Ness_quick

    Ness_quick wrote:

     

    Ok so ive created another field in my database table called 'dateCreated' and selected date from the field 'type' but what shall i set everything else to or should they be left blank, how do i make the column automatically be set to current date as bregent suggested?

     

    I have not done this BUT I think instead of choosing 'date' as the 'type' of field you use 'timestamp' and under 'default' choose 'current_timestamp'

     

    Now how that affects the below I don't know:

     

    mysqli_query($con,"DELETE FROM donations WHERE dayCreated < DATE_SUB(NOW(), INTERVAL 60 DAY");

     

     

    What you will have to do on your local testing server is create an entry which is 1 day older than the current date and test it out by setting 60 DAY to 1 DAY. If it gets deleted from the 'donations' table when you visit the webpage then its working. If it doesnt then that mysqli query will need  adjusting.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2013 4:59 AM   in reply to Ness_quick

    Ness_quick wrote:

     

    Ok i have done that and inserted a new data via my 'insert_donations.php' page which has uploaded the new data and a date now appears in 'dateCreated' column in my table, all working as it should! I tried changing the date to yesterdays date via phpmyadmin but it didnt seem to like it so ive left it and will wait to see if it deletes it tomorrow. Fingers crossed!

     

     

     

     

    You need to change 'date' in the query below to the column name -  dateCreated (if that is what you named it) - and YOUR table is called 'donation' NOT 'donations'

     

    mysqli_query($con,"DELETE FROM donations WHERE date < DATE_SUB(NOW(), INTERVAL 1 DAY");

     

     

    Ness_quick wrote:

     

    BUT the problem i am having now is when i open the 'insert_donations.php' the security login page (index.php) should load first but now it doesn't for some reason. It was working great until i added that earlier code (above)?

     

     

    I'm guessing that's because you haven't closed the browser so the 'session' is still working.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points