10 Replies Latest reply: Apr 12, 2013 4:59 AM by osgood_ RSS

    Can anyone give me some advice on databases?

    Ness_quick Community Member

      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

        • 1. Re: Can anyone give me some advice on databases?
          bregent MVP

          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.

          • 2. Re: Can anyone give me some advice on databases?
            osgood_ MVP

            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.

            • 3. Re: Can anyone give me some advice on databases?
              bregent MVP

              >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.

              • 4. Re: Can anyone give me some advice on databases?
                osgood_ MVP

                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

                • 5. Re: Can anyone give me some advice on databases?
                  bregent MVP

                  >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.

                  • 6. Re: Can anyone give me some advice on databases?
                    osgood_ MVP

                    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.

                    • 7. Re: Can anyone give me some advice on databases?
                      Ness_quick Community Member

                      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?

                      • 8. Re: Can anyone give me some advice on databases?
                        osgood_ MVP

                        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.

                        • 9. Re: Can anyone give me some advice on databases?
                          Ness_quick Community Member

                          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! 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)?

                           

                          Heres what the code looks like -

                           

                          <?php require_once('../Connections/milesdata.php'); ?>

                          <?php

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

                          // 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 1 DAY");

                          mysqli_close($con);

                          ?>

                          <?php

                          if (!isset($_SESSION)) {

                            session_start();

                          }

                          $MM_authorizedUsers = "";

                          $MM_donotCheckaccess = "true";

                           

                           

                          // *** Restrict Access To Page: Grant or deny access to this page

                          function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {

                            // For security, start by assuming the visitor is NOT authorized.

                            $isValid = False;

                           

                           

                            // When a visitor has logged into this site, the Session variable MM_Username set equal to their username.

                            // Therefore, we know that a user is NOT logged in if that Session variable is blank.

                            if (!empty($UserName)) {

                              // Besides being logged in, you may restrict access to only certain users based on an ID established when they login.

                              // Parse the strings into arrays.

                              $arrUsers = Explode(",", $strUsers);

                              $arrGroups = Explode(",", $strGroups);

                              if (in_array($UserName, $arrUsers)) {

                                $isValid = true;

                              }

                              // Or, you may restrict access to only certain users based on their username.

                              if (in_array($UserGroup, $arrGroups)) {

                                $isValid = true;

                              }

                              if (($strUsers == "") && true) {

                                $isValid = true;

                              }

                            }

                            return $isValid;

                          }

                           

                           

                          $MM_restrictGoTo = "index.php";

                          if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {  

                            $MM_qsChar = "?";

                            $MM_referrer = $_SERVER['PHP_SELF'];

                            if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";

                            if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0)

                            $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];

                            $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);

                            header("Location: ". $MM_restrictGoTo);

                            exit;

                          }

                          ?>

                          <?php

                          if (!function_exists("GetSQLValueString")) {

                          function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                          {

                            if (PHP_VERSION < 6) {

                              $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                            }

                           

                           

                            $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                           

                           

                            switch ($theType) {

                              case "text":

                                $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                break;   

                              case "long":

                              case "int":

                                $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                                break;

                              case "double":

                                $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                                break;

                              case "date":

                                $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                break;

                              case "defined":

                                $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                                break;

                            }

                            return $theValue;

                          }

                          }

                           

                           

                          $editFormAction = $_SERVER['PHP_SELF'];

                          if (isset($_SERVER['QUERY_STRING'])) {

                            $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

                          }

                           

                           

                          if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

                            $insertSQL = sprintf("INSERT INTO donation (name, url) VALUES (%s, %s)",

                                                 GetSQLValueString($_POST['name'], "text"),

                                                 GetSQLValueString($_POST['url'], "text"));

                           

                           

                            mysql_select_db($database_milesdata, $milesdata);

                            $Result1 = mysql_query($insertSQL, $milesdata) or die(mysql_error());

                           

                           

                            $insertGoTo = "insert_success.php";

                            if (isset($_SERVER['QUERY_STRING'])) {

                              $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

                              $insertGoTo .= $_SERVER['QUERY_STRING'];

                            }

                            header(sprintf("Location: %s", $insertGoTo));

                          }

                          ?>

                          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

                          <html xmlns="http://www.w3.org/1999/xhtml">

                          <head>

                          <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

                          <title>Insert data</title>

                          <style type="text/css">

                          @import url("../Style sheet.css");

                          a:link {

                                    color: #D91327;

                          }

                          a:visited {

                                    color: #D91327;

                          }

                          a:hover {

                                    color: #CEB239;

                          }

                          a:active {

                                    color: #D91327;

                          }

                          </style>

                          </head>

                           

                           

                          <body>

                          <div id="container"><img src="../banner.jpg" width="1082" height="187" alt="banner" />

                          <p> </p>

                          <p class="admin_heading">Insert Record to Database</p>

                          <p> </p>

                          <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

                            <table align="center">

                              <tr valign="baseline">

                                <td height="25" align="right" nowrap="nowrap" bgcolor="#E6CC7F">Deceased Name:</td>

                                <td height="25" bgcolor="#E6CC7F"><input type="text" name="name" value="" size="70" /></td>

                              </tr>

                              <tr valign="baseline">

                                <td height="25" align="right" nowrap="nowrap" bgcolor="#F5E7B8">Deceased Unique Url:</td>

                                <td height="25" bgcolor="#F5E7B8"><input type="text" name="url" value="" size="70" /></td>

                              </tr>

                              <tr valign="baseline">

                                <td nowrap="nowrap" align="right"> </td>

                                <td><input type="submit" class="submit" value="Insert record" /></td>

                              </tr>

                            </table>

                            <input type="hidden" name="MM_insert" value="form1" />

                          </form>

                          <p> </p>

                          <p class="maintext">If you wish to just update data in database please click <a href="update_donation.php">here</a></p>

                           

                           

                          </div>

                          </body>

                          </html>

                          • 10. Re: Can anyone give me some advice on databases?
                            osgood_ MVP

                            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.