PHP and Mysql - Automatic deleting of dates and times when they have passed.

Mortenjan · February 19, 2009

Hi.

Is there any way i can get a php script or something to delete dates and times in mysql when they have passed in real time?

Regards Morten

Mchl · February 19, 2009

Yes, but you would need a means to run a script periodically (probably using cron)

Mortenjan · February 19, 2009

well, i have no idea how to make that.

Is it much work?

And do you got an example?

Regards Morten

allworknoplay · February 19, 2009

Hi.

Is there any way i can get a php script or something to delete dates and times in mysql when they have passed in real time?

Regards Morten

This is a cron job issue, I do this all the time. I have my script running once a minute since that's the least you can do for cron's...

You could also write a PHP program that runs once, gets kicked off by cron, and the program is a continuous WHILE loop that can clear out your info faster than 1 minute....but I am always weary of writing a continuous WHILE loop...

Mortenjan · February 19, 2009

yes i agree. I have bad experiences with while loops.

But, as i said. I have no clue on how to build a code like this:S

Mchl · February 19, 2009

No, not much work. But first check with your hosting, if you have access to cron at all.

Mortenjan · February 19, 2009

I fixed the problem in one query with "WHERE date.date >= CURDATE()".

But i got a template file here, that i did not make my self.

How can i add the same function to this code?

<?php
/***********************************************************
* Class:        baaSelect                                  *
* Version:      1.1                                        *
* Date:         May 2004                                   *
* Author:       Barry Andrew                               *
* Copyright:    © BA Andrew 2004                           *
* Licence :     Free for non-commercial use                *
*                                                          *
************************************************************
*   ver 1.1     added support for postgresql, mssql, odbc, *
*               sybase                                     *
***********************************************************/


/*---------------------------- Arguments for addSelect------------------------------
* selectName - name to be given the the <SELECT></SELECT> element
* tableName -  source db table for SELECT option list
* id field  -  the field name to be allocated to option values
* tx field  -  text field whose contents appear in the option list
*----------------------------- optional --------------------------------------------
* fk field  -  foreign key field linking table its parent option list table
* order by  -  BY_ID or BY_TEXT defines order of options in list. (default=BY_TEXT)
* default text-the text to appear when there is no option selected
*----------------------------------------------------------------------------------*/

/***************** SAMPLE SCRIPT ***************************
<?php
$cnx = mysql_connect('localhost');
mysql_select_db('test', $cnx);

include 'baaSelect.php';
$sel = new baaSelect(DB_MYSQL);

$sel->addSelect('Category', 'category', 'catID', 'category','',1,'--select--');
$sel->addSelect('Subcat', 'subcategory', 'subcatID', 'subcategory','catID',1,'--select--');
?>

<HTML>
<HEAD>
<meta Name="generator" content="PHPEd Version 3.1.2 (Build 3165)">
<title>Sample</title>

<?php $sel->makeScript() ?>        # this creates the js

</HEAD>
<BODY>
<form method=get>
<?php
$sel->makeSelect('Category');         # this creates the HTML
$sel->makeSelect('Subcat');
?>
</form>
</BODY>
</HTML>

********************** END SAMPLE*************************************/


define("BY_ID",0);
define("BY_TEXT",1);

define('DB_MYSQL',0);
define('DB_POSTGRESQL',1);
define('DB_ODBC',2);
define('DB_MSSQL',3);
define('DB_SYBASE',4);


class baaSelectList {
      // private class used by class baaSelect
      var $name;
      var $table;
      var $id_field;
      var $tx_field;
      var $fk_field;
      var $default_text;
      var $orderby;
      var $master;
      var $child;
      var $curr_value;
      var $rc;
      var $cursor;
      var $db;
      var $conn;

      function baaSelectList($selectName, $tableName, $id_fieldName, $tx_fieldName, $fk_fieldName="",
                             $orderby=1, $defaultText="", $adb, $aconn) {
               $this->name = $selectName;
               $this->table = $tableName;
               $this->id_field = $id_fieldName;
               $this->tx_field = $tx_fieldName;
               $this->fk_field = $fk_fieldName;
               $this->default_text = $defaultText;
               $this->orderby = $orderby;
               $this->curr_value = isset($_REQUEST[$selectName]) ? $_REQUEST[$selectName] : '0';
               $this->db = $adb;
               $this->conn = $aconn;
      }

      function _setParent($aselect) {
               $this->master = $aselect;
      }

      function _setChild($aselect) {
               $this->child = $aselect;
      }

      function _makeSelect($owner) {
             $changeEvent="";
             $sql = "SELECT $this->id_field, $this->tx_field FROM $this->table";
             if (!empty($this->master) && !empty($this->fk_field)) {
                  $asel = $owner->_namedSelect($this->master);
                  $sql .= " WHERE $this->fk_field = '$asel->curr_value' ";
             }
             if (!empty($this->child)) {
                  $asel = $owner->_namedSelect($this->child);

                  if (!empty($asel->fk_field) && $owner->scripted)
                  $changeEvent = "onchange=\"baaSelectUpdate{$asel->name}(this.form)\" ";
             }

             $sql .= " ORDER BY ";
             $sql .= $this->orderby==0 ? $this->id_field : $this->tx_field;

             $this->rc = 0;
             $this->cursor =  $this->_query($sql);
             $default = $this->cursor ? $this->default_text : "Error in selection query";
             $strRes = "<SELECT name=\"$this->name\" $changeEvent>\n";
             $strRes .= "<OPTION value=\"0\" > $this->default_text</option>\n";
             if ($this->cursor) {
                 while (list($id,$desc) = $this->_fetch_row()) {
                        $id = trim($id);
                        $desc = trim($desc);
                        $select = $id == $this->curr_value ? " SELECTED " : "" ;
                        $strRes .= "<OPTION value=\"$id\" $select> $desc</option>\n";
                 }
             }
             $strRes .= "</SELECT>\n";
             $this->_free_result();
             echo $strRes;
     }

     function _dataArray($owner) {
              if (empty($this->master) || empty($this->fk_field)) return;

              $at = "array".$this->name."TXT";
              $av = "array".$this->name."VAL";

              echo "\nvar $at = new Array();\n";
              echo "var $av = new Array();\n";
              echo "$at"."[0] = \"\";\n";
              echo "$av"."[0] = 0;\n";

              $sql = "SELECT $this->fk_field as fk, $this->id_field as id, $this->tx_field as tx
                      FROM $this->table ORDER BY ";
              $sql .= $this->orderby==0 ? "$this->id_field" : "$this->tx_field";

              $tarray = $varray = array();

              $this->cursor = $this->_query($sql);
              while (list($fk, $id, $tx) = $this->_fetch_row()) {
                     $tarray[$fk][] = trim($tx);
                     $varray[$fk][] = trim($id);
              }
              $this->_free_result();
              ksort($tarray);
              ksort($varray);
              foreach($tarray as $k=>$data) {
                      echo "$at"."[$k] = \"" . join('|' , $data) . "\";\n";
              }
              echo "\n";
              foreach($varray as $k=>$data) {
                      echo "$av"."[$k] = \"" . join('|' , $data) . "\";\n";
              }
              echo "\n";
     }

     function _updateFunction() {
              if (empty($this->master) || empty($this->fk_field)) return;

              $at = "array".$this->name."TXT";
              $av = "array".$this->name."VAL";
              $mr = $this->master;
              $me = $this->name;
              $call = empty($this->child) ? "" : "baaSelectUpdate$this->child (form)";

              echo "\n\nfunction baaSelectUpdate$this->name (form) {\n";
              echo "
               current$mr = form.$mr.options[form.$mr.selectedIndex].value;

               while (form.$me.options.length) {
                    form.$me.options[0] = null;
               }
               var tmp = new String (".$at."[current$mr]);
               var arrayText = tmp.split(\"|\");
               tmp = new String (".$av."[current$mr]);
               var arrayVals = tmp.split(\"|\");

               var optionlist = form.$me.options;
               optionlist[0] = new Option();
               optionlist[0].value = 0;
               optionlist[0].text = \"$this->default_text\";
               if (".$at."[current$mr]) {
                   for (var i=0; i<arrayText.length; i++) {
                        optionlist[i+1]=new Option();
                        optionlist[i+1].value = arrayVals[i];
                        optionlist[i+1].text = arrayText[i];
                        if (current$me == arrayVals[i])
                            optionlist[i+1].selected = true;
                   }
               }
               $call\n";
              echo "}\n";
     }
     function _handleError($msg) {
         echo "<br /><div style='background: #FF0000; color: #FFFFFF'> $msg </div><br />";
     }
      /******************************************
      *  DATABASE DEPENDENT FUNCTIONS           *
      *  the next 3 function are db dependent.  *
      *  You can add a case for your db and add *
      *  appropriate code                       *
      ******************************************/

      function _query($sql) {
               switch ($this->db) {
               case DB_MYSQL :
                    $res = @mysql_query($sql);
                    if (!$res) {
                         $this->_handleError(mysql_error());
                         return false;
                    }
                    break;
               case DB_MSSQL :
                    $res = @mssql_query($sql);
                    if (!$res) {
                         $this->_handleError(mssql_get_last_message());
                         return false;
                    }
                    break;
               case DB_SYBASE :
                    $res = @sybase_query($sql, $this->conn);
                    if (!$res) {
                         $this->_handleError(sybase_get_last_message());
                         return false;
                    }
                    break;
               case DB_POSTGRESQL:
                    $res = @pg_query($sql);
                    if (!$res) {
                         $this->_handleError(pg_last_error());
                         return false;
                    }
                    break;
               case DB_ODBC:
                    $res = @odbc_exec($this->conn,$sql);
                    if (!$res) {
                         $this->_handleError(odbc_errormsg());
                         return false;
                    }
                    break;
               }
               return $res;
      }
      function _fetch_row() {
               switch ($this->db) {
               case DB_MYSQL:
                    return mysql_fetch_row($this->cursor); break;
               case DB_SYBASE:
                    return sybase_fetch_row($this->cursor); break;
               case DB_MSSQL:
                    return mssql_fetch_row($this->cursor); break;
               case DB_POSTGRESQL:
                    return pg_fetch_row($this->cursor, $this->rc++ ); break;
               case DB_ODBC:
                    return (odbc_fetch_into($this->cursor,$row)) ? $row : false;
               }
      }
      function _free_result() {
               switch ($this->db) {
               case DB_MYSQL:
                     mysql_free_result($this->cursor); break;
               case DB_SYBASE:
                     sybase_free_result($this->cursor); break;
               case DB_MSSQL:
                     mssql_free_result($this->cursor); break;
               case DB_POSTGRESQL:
                     pg_free_result($this->cursor); break;
               case DB_ODBC:
                     odbc_free_result($this->cursor); break;
               }

      }
}

class baaSelect {

      var $itsSelects;
      var $scripted;
      var $db;
      var $conn;

      function baaSelect($adb=DB_MYSQL, $aconn=0) {
               $this->itsSelects = array();
               $this->scripted = false;
               $this->db = $adb;
               $this->conn = $aconn;
      }

      function addSelect ($selectName, $tableName, $id_fieldName, $tx_fieldName, $fk_fieldName="", $orderby=1,
                          $defaultText="") {
               /*---------------------------- Arguments for addSelect------------------------------
               * selectName - name to be given the the <SELECT></SELECT> element
               * tableName -  source db table for SELECT option list
               * id field  -  the field name to be allocated to option values
               * tx field  -  text field whose contents appear in the option list
               *----------------------------- optional --------------------------------------------
               * fk field  -  foreign key field linking table its parent option list table
               * order by  -  BY_ID or BY_TEXT defines order of options in list. (default=BY_TEXT)
               * default text-the text to appear when there is no option selected
               *----------------------------------------------------------------------------------*/
               $aSelect = new baaSelectList($selectName, $tableName, $id_fieldName,$tx_fieldName,
                            $fk_fieldName, $orderby, $defaultText, $this->db, $this->conn);
               $x = count($this->itsSelects);
               if ($x > 0) {
                   $aSelect->_setParent($this->itsSelects[$x-1]->name);
                   $this->itsSelects[$x-1]->_setChild($aSelect->name);
               }
               $this->itsSelects[] = $aSelect;
       }

       function makeScript() {
                echo "\n<SCRIPT language=\"JavaScript\" type=\"text/javascript\">\n";
                echo "<!-- \n";
                $this->_currentValues();
                $this->_dataArrays();
                $this->_updateFunctions();
                echo "\n// generated by baaSelect.php  -->\n</SCRIPT>\n\n";
                $this->scripted = true;

       }

       function makeSelect($aname) {
               if ($asel = $this->_namedSelect($aname))
                   $asel->_makeSelect($this);
       }

       // -- PRIVATE METHODS ------------------------------------------------------------------------
       function _namedSelect($aname) {
                $sel = null;
                foreach($this->itsSelects as $asel) {
                        if ($asel->name == $aname) {
                            $sel = $asel;
                            break;
                        }
                }
                return $sel;
       }

       function _currentValues() {
                foreach($this->itsSelects as $asel) {
                         echo "\nvar current$asel->name = $asel->curr_value ;";
                }
                echo "\n";
       }

       function _dataArrays() {
                foreach($this->itsSelects as $asel) {
                         $asel->_dataArray($this);
                }
       }

       function _updateFunctions() {
                foreach($this->itsSelects as $asel) {
                         $asel->_updateFunction();
                }
       }

       function _debug() {
                echo '<pre>';
                print_r ($this);
                echo '</pre>';
       }
}
?>

Thanks in advance.

Regards Morten

Mortenjan · February 20, 2009

any suggestions?

Sign In

PHP and Mysql - Automatic deleting of dates and times when they have passed.

Recommended Posts

Mortenjan

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

Mortenjan

Link to comment

Share on other sites

allworknoplay

Link to comment

Share on other sites

Mortenjan

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

Mortenjan

Link to comment

Share on other sites

Mortenjan

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information