hello friends plz help me,

iam uploading excel sheets into the database. it is succefully entering . the problem is when again uploading excel sheet i want to check the database and if excel sheet which i send is there already i have to restrict by inserting into the table(resource_table) and i should enter these dublicate excel sheet to another table(resource_table_backup).

please dude help me in this soon.

How about copy the excel cheets over to anouther server automatically with php so you already have a back up. Then when people upload the excel sheets check weather they are there or not, if they are there then tell them that the sheet has already been uploaded, are you sure you want to upload the file say for file changes etc...

Hello dude,

excel_files is the folder in which excel files r placed every time iam unlinking(deleting excel files) after uploading to database and iam retrieving excel files from mail so if any body send same excel sheet to mail it will download to folder excel_files then it should be uploaded when i call excel2.php. so now i need to check whether the database has this excel sheet already . if it have the records i want to insert into another database .

so will it happens like this plz give idea. iam pasting my code here

<?php
require_once 'db_conn.php'; 
require_once 'Spreadsheet/Excel/reader.php';

  date_default_timezone_set('Asia/Calcutta');
  //setlocale(LC_TIME,'in_IN');
  //echo LC_TIME;
  if ($handle = opendir('./excel_files/')) {

    /* This is the correct way to loop over the directory. */
    $file_array = array();

    while (false !== ($file = readdir($handle))) {
        $file_array[]  = $file;
    }
      //print_r($file_array);exit;
    closedir($handle);
  }
    $regexp = '/\d{4}-\d{2}-\d{2}/si';
  if(count($file_array) >2)
  {

    foreach($file_array as $file_key=>$file_value)
    {
        if( !is_dir($file_value))
        {
            $reader = new Spreadsheet_Excel_Reader();
            $reader->setOutputEncoding("UTF-8");                
            $reader->read("./excel_files/".$file_value);
            $regex = '/\d{4}-\d{2}-\d{2}/si';
            $data = array();
            $arr = array();
            $k = 0;
            $dbconn = db_conn();
            $previous_dates = array();
            $arrayOfObjects=array();
            //print_r($file_value);exit;

            for ($i = 0; $i <= $reader->sheets[0]["numRows"]; $i++)
            {

                for ($j = 0; $j <= $reader->sheets[0]["numCols"]; $j++)
                {
                    if(strcasecmp(trim($reader->sheets[0]["cells"][$i][$j]),'Resource')==0) 
                    {
                        $resource = $reader->sheets[0]["cells"][$i][$j+1];
                        $p=$i;
                    }

                    if(strcasecmp(trim($reader->sheets[0]["cells"][($i+$p)-1][$j]),'Date')==0)
                    {
                        $y=($i+$p)-1;
                        $x=0;
                    }

                    if($i>$y)
                    {

                        switch($j)
                        {
                             case 2:

                                if($reader->sheets[0]["cellsInfo"][$i][$j]['type']=='unknown')
                                {
                                    //Make a date for unknown format if coloumn 2 is has digits
                                    //Check for  date coloumn is not empty and hours are also filled
                                    if(!empty($reader->sheets[0]["cells"][$i][$j]) && !empty($reader->sheets[0]["cells"][$i][$j+1]))
                                    {

                                        //Check for digits in date coloumn if not make it the coloumn null
                                       if(preg_match('/\d+/',$reader->sheets[0]["cells"][$i][$j]))
                                       {        
                                         $reader->curformat = 'Y-m-d';  
                                         $date_with_utc_value = $reader->createDate($reader->sheets[0]["cells"][$i][$j]);
                                         $date = isset($date_with_utc_value[0])? $date_with_utc_value[0]: null;

                                       }
                                       else
                                       {
                                         $reader->sheets[0]["cells"][$i][$j] ='';
                                         $reader->sheets[0]["cells"][$i][$j+1] =''; 
                                         $date = null;
                                       }
                                    }
                                    else
                                    {

                                        //Check any of the excel sheet is not sending the date and hours in a row, 
                                        //then fill the other coloumns with previous date

                                        if( !empty($reader->sheets[0]["cells"][$i][$j]) && empty($reader->sheets[0]["cells"][$i][$j+1]) && empty($reader->sheets[0]["cells"][$i+1][$j]) && !empty($reader->sheets[0]["cells"][$i+1][$j+1])  )
                                        {
                                            $k=1;
                                            do
                                            {
                                               if(empty($reader->sheets[0]["cells"][$i+$k][$j]))
                                               {    
                                                 $reader->sheets[0]["cells"][$i+$k][$j] =$reader->sheets[0]["cells"][$i][$j] ;
                                                 $k++;
                                                }
                                                else
                                                {
                                                    $k=0;
                                                }
                                            }while($k>0);


                                             if(preg_match('/\d+/',$reader->sheets[0]["cells"][$i][$j]))
                                             {      
                                                $reader->curformat = 'Y-m-d';   
                                                $date_with_utc_value =  $reader->createDate($reader->sheets[0]["cells"][$i][$j]);
                                                $date = isset($date_with_utc_value[0])? $date_with_utc_value[0]: null;
                                             }

                                        }

                                    }
                                }
                                else if($reader->sheets[0]["cellsInfo"][$i][$j]['type']=='date')
                                {
                                    //If the type is specifically a date then convert them into required database format date
                                    list($day,$month,$year) = explode("/", $reader->sheets[0]["cells"][$i][$j]);
                                    $date = $year."-".$month."-".$day;
                                }
                                else
                                {

                                    if(empty($reader->sheets[0]["cells"][$i][$j]) && !empty($reader->sheets[0]["cells"][$i][$j+1]) && !empty($reader->sheets[0]["cells"][$i+1][$j]))
                                    {
                                        $reader->sheets[0]["cells"][$i][$j] = $reader->sheets[0]["cells"][$i+1][$j];

                                        if(preg_match('/\d+/',$reader->sheets[0]["cells"][$i][$j]))
                                         {      
                                            $reader->curformat = 'Y-m-d';   
                                            $date_with_utc_value =  $reader->createDate($reader->sheets[0]["cells"][$i][$j]);
                                            $date = isset($date_with_utc_value[0])? $date_with_utc_value[0]: null;
                                         }
                                    }
                                    else
                                    {

                                        $date = null;
                                    }
                                }

                                    $reader->sheets[0]["cells"][$i][$j] = $date;
                                    if(!empty($reader->sheets[0]["cells"][$i][$j]) )
                                    {
                                        if($reader->sheets[0]["cells"][$i][$j] != $reader->sheets[0]["cells"][$i+1][$j])
                                        {
                                                array_push($previous_dates,$reader->sheets[0]["cells"][$i][$j]);
                                        }
                                    }

                                    //echo $reader->sheets[0]["cells"][$i][$j]."&nbsp;".$reader->sheets[0]["cells"][$i][$j+1]."<br />";

                                    if(!empty($reader->sheets[0]["cells"][$i][$j]) && preg_match($regexp,$reader->sheets[0]["cells"][$i][$j]) && !empty($reader->sheets[0]["cells"][$i][$j+1]))
                                    {
                                         $object = new stdClass();
                                         $object->resource = $resource;     
                                         $object->date = $reader->sheets[0]["cells"][$i][$j];
                                         $object->hours = $reader->sheets[0]["cells"][$i][$j+1];
                                         $object->desc = addslashes($reader->sheets[0]["cells"][$i][$j+2]);
                                    }
                                    else
                                    {
                                        if(!empty($reader->sheets[0]["cells"][$i][$j+1]) && !preg_match('/\w/',$reader->sheets[0]["cells"][$i][$j])  )
                                        {
                                            $object = new stdClass();
                                            $object->resource = $resource;
                                            $object->date = array_pop($previous_dates);
                                            array_push($previous_dates,$object->date);
                                            $object->hours = $reader->sheets[0]["cells"][$i][$j+1];
                                            $object->desc = addslashes($reader->sheets[0]["cells"][$i][$j+2]);
                                        }
                                        else
                                        {
                                            if(!empty($reader->sheets[0]["cells"][$i][$j+1]) && empty($reader->sheets[0]["cells"][$i][$j]))
                                            {
                                                    $object = new stdClass();
                                                    $object->resource = $resource;
                                                    $object->date = array_pop($previous_dates);
                                                    array_push($previous_dates,$object->date);
                                                    $object->hours = $reader->sheets[0]["cells"][$i][$j+1];
                                                    $object->desc = addslashes($reader->sheets[0]["cells"][$i][$j+2]);
                                            }
                                        }
                                    }

                                break;

                             case 5:
                                                                        if(preg_match($regexp,$reader->sheets[0]["cells"][$i][$j-3]) || !empty($reader->sheets[0]["cells"][$i][$j-2]) )

                                    {
                                        $project = $reader->sheets[0]["cells"][$i][$j];
                                        //print_R($project);exit;
                                        if(!empty($project))
                                        {
                                            if($reader->sheets[0]["cells"][$i-1][$j] != $reader->sheets[0]["cells"][$i][$j])
                                            {
                                                $query = getIdQuery('project' , $project );
                                                $result =   $dbconn->query($query);
                                                $result_arr  = $result->fetch_row();
                                                $project_id = $result_arr[0];
                                            }
                                            if(!empty($project_id))
                                            {
                                                $object->project =  $project_id;
                                            }else
                                            {
                                                $object->project =  22;
                                            }
                                        }
                                        else
                                        {
                                            if(!isset($reader->sheets[0]["cells"][$i][$j]) && (isset($reader->sheets[0]["cells"][$i][$j-2])) )
                                            {       
                                                if(isset($reader->sheets[0]["cells"][$i][$j-3]) && !preg_match('/\w/si',$reader->sheets[0]["cells"][$i][$j-3]))
                                                {
                                                    $object->project = 22;
                                                }
                                                if( !empty($object->date) && !empty($object->hours) && !empty($object->desc))
                                                {
                                                    $object->project = 22;
                                                }
                                            }
                                        }
                                    }   
                                break;

                             case 6:
                                 if((preg_match($regex,$reader->sheets[0]["cells"][$i][$j-4]))  || !empty($reader->sheets[0]["cells"][$i][$j-3])  )
                                 {
                                    $task = $reader->sheets[0]["cells"][$i][$j];
                                    if(!empty($task))
                                    {
                                        if($reader->sheets[0]["cells"][$i-1][$j] != $reader->sheets[0]["cells"][$i][$j])
                                        {
                                            $query = getIdQuery('task' , $task );
                                            $result =   $dbconn->query($query);
                                            $result_arr  = $result->fetch_row();
                                            $task_id = $result_arr[0];
                                        }
                                        if(!empty($task))
                                        {
                                            $object->task = $task_id;
                                        }else
                                        {
                                            $object->task = 18;
                                        }
                                    }
                                    else
                                    {
                                        if( !isset($reader->sheets[0]["cells"][$i][$j]) && (isset($reader->sheets[0]["cells"][$i][$j-3])) )
                                        {
                                            if(!empty($reader->sheets[0]["cells"][$i][$j-3]))
                                            {
                                                if(isset($reader->sheets[0]["cells"][$i][$j-3]) && !isset($reader->sheets[0]["cells"][$i][$j-3])&& !preg_match('/\w/',$reader->sheets[0]["cells"][$i][$j-4]))
                                                {

                                                    $object->task = 18;
                                                }

                                                if( !empty($object->date) && !empty($object->hours) && !empty($object->desc))
                                                {
                                                    $object->task = 18;
                                                }


                                            }
                                        }

                                      }
                                    }   

                                break;
                        }
                    }

                }
                if($i>$y)
                {
                    $x++;
                }

                array_push($arrayOfObjects,$object);
                unset($object);

            }//End of loop I

            echo "<pre>";
            //print_r($arrayOfObjects);exit;

             $arr = array();
             $err_arr = array();
            if(count($arrayOfObjects) !=0)
            {

             //checking if the records already present in the database
             //$chk="select * from resource_tasks where  name='$name'";

            $chk="SELECT 
                DATE_FORMAT( rt.day, '%Y/%m/%d') as day , rt.resource_id, p.project_name, t.task_name, rt.description, rt.hours
              FROM 
                resource_tasks AS rt, project AS p, task AS t
              WHERE 
                t.task_id = rt.resource_task
              AND 
                p.project_id = rt.resource_project
              AND 
                rt.day 
              BETWEEN 
                STR_TO_DATE('$start_date','%Y-%c-%e')  AND STR_TO_DATE('$end_date','%Y-%c-%e') $groupby order by rt.resource_id,day";

                //print_r($chk);exit;

            $result=mysqli_query($chk);
            $rec=mysqli_num_rows($result);
            //echo $rec;exit;
            if($rec>0)
            {
            $insert = "INSERT INTO resource_tasks_backup(resource_id,day,hours,description,resource_project,resource_task) VALUES ";
                $string = "";
                //$res=9 ; 
                //$msg="username is already existing Please please try with another user!";
                //echo '<META HTTP-EQUIV="REFRESH" CONTENT="0;URL=administrator_adddomain.php?res='.$res.'">';
                //exit;

                /*foreach($arrayOfObjects as $key=>$value)
                {
                    if(is_object($value))
                    {
                        $fullobject = get_object_vars($value);
                        if(count($fullobject) == 6 )
                        {
                            $i=0;
                            //echo "<pre>";
                            //print_r($fullobject);
                            foreach($fullobject as $fullkey=>$fullvalue)
                            {
                                switch($fullkey)
                                {
                                case 'task':
                                            if(strtotime($value->date)>0)
                                            {
                                                $task_val = "'$value->task')"; 
                                                $arr[$i] = $arr[$i].$task_val;
                                                $task_val = "";
                                            }
                                            else
                                            {
                                                $task_val = "'$value->task')"; 
                                                $err_arr[$i] = $err_arr[$i].$task_val;
                                                $task_val = "";

                                            }
                                            break;
                                case 'project':
                                            if(strtotime($value->date)>0)
                                            {   
                                                $project_val = "'$value->project',"; 
                                                $arr[$i] = $arr[$i].$project_val;
                                                $project_val = "";
                                            }
                                            else
                                            {
                                                $project_val = "'$value->project',"; 
                                                $err_arr[$i] = $err_arr[$i].$project_val;
                                                $project_val = "";

                                            }
                                            break;
                                case 'date':
                                            if(strtotime($value->date)>0)
                                            {
                                              $dtvalue = date('Y-m-d',strtotime($value->date));
                                              $date = "("."'$value->resource',";
                                              $date .= "'$dtvalue',";
                                              $arr[$i]  = $date;
                                              $date = "";
                                            }
                                            else
                                            {
                                              $dtvalue = date('Y-m-d',strtotime($value->date));
                                              $date = "("."'$value->resource',";
                                              $date .= "'$dtvalue',";
                                              $err_arr[$i]  = $date;
                                              $date = "";

                                            }
                                            break;
                                case 'hours':
                                            if(strtotime($value->date)>0)
                                            {
                                                $hours = "'$value->hours',";
                                                $arr[$i] = $arr[$i].$hours;
                                                $hours = "";
                                            }
                                            else
                                            {
                                                $hours = "'$value->hours',";
                                                $err_arr[$i] = $err_arr[$i].$hours;
                                                $hours = "";

                                            }
                                            break;
                                case 'desc':
                                            if(strtotime($value->date)>0)
                                            {
                                                $desc = "'$value->desc',"; 
                                                $arr[$i] = $arr[$i].$desc;
                                                $desc = "";
                                            }
                                            else
                                            {
                                                $desc = "'$value->desc',"; 
                                                $err_arr[$i] = $err_arr[$i].$desc;
                                                $desc = "";

                                            }
                                            break;                                  
                                }//End of switch

                            }//End of foreach for object
                            $i++;
                            //echo "<pre>";
                            //print_r($arr);
                            //echo "<pre>";
                            //print_r($err_arr);
                            if(count($arr) !=0)
                            {
                                $string = implode(',',$arr);
                                $insert .= $string.",";
                            }

                            if(count($err_arr) !=0)
                            {
                                $error_string = implode(',',$err_arr);
                                $error_insert .= $error_string.",";
                            }
                            }

                        }//End of If condition for object vars

                    }//End of check for object
                //End of arrayOfObjects

            //End of arrayOfObjects count check
            if(count($arr) !=0)
            {
                $insert = substr_replace($insert,';', -1);
                //echo "insert ".$insert;
                $dbconn->multi_query($insert);

            }*/



            }

            /*
            $result=mysql_query($chk);
            $rec=mysql_num_rows($result);

            if($rec>0)
            {
                $res=9 ; 
                //$msg="username is already existing Please please try with another user!";
                echo '<META HTTP-EQUIV="REFRESH" CONTENT="0;URL=administrator_adddomain.php?res='.$res.'">';
                exit;
            }*/
             else
             {
                $insert = "INSERT INTO resource_tasks(resource_id,day,hours,description,resource_project,resource_task) VALUES ";
                $string = "";
                $error_insert = "INSERT INTO error_resource_tasks(resource_id,day,hours,description,resource_project,resource_task) VALUES ";
                $error_string = "";
                foreach($arrayOfObjects as $key=>$value)
                {
                    if(is_object($value))
                    {
                        $fullobject = get_object_vars($value);
                        if(count($fullobject) == 6 )
                        {
                            $i=0;
                            //echo "<pre>";
                            //print_r($fullobject);
                            foreach($fullobject as $fullkey=>$fullvalue)
                            {
                                switch($fullkey)
                                {
                                case 'task':
                                            if(strtotime($value->date)>0)
                                            {
                                                $task_val = "'$value->task')"; 
                                                $arr[$i] = $arr[$i].$task_val;
                                                $task_val = "";
                                            }
                                            else
                                            {
                                                $task_val = "'$value->task')"; 
                                                $err_arr[$i] = $err_arr[$i].$task_val;
                                                $task_val = "";

                                            }
                                            break;
                                case 'project':
                                            if(strtotime($value->date)>0)
                                            {   
                                                $project_val = "'$value->project',"; 
                                                $arr[$i] = $arr[$i].$project_val;
                                                $project_val = "";
                                            }
                                            else
                                            {
                                                $project_val = "'$value->project',"; 
                                                $err_arr[$i] = $err_arr[$i].$project_val;
                                                $project_val = "";

                                            }
                                            break;
                                case 'date':
                                            if(strtotime($value->date)>0)
                                            {
                                              $dtvalue = date('Y-m-d',strtotime($value->date));
                                              $date = "("."'$value->resource',";
                                              $date .= "'$dtvalue',";
                                              $arr[$i]  = $date;
                                              $date = "";
                                            }
                                            else
                                            {
                                              $dtvalue = date('Y-m-d',strtotime($value->date));
                                              $date = "("."'$value->resource',";
                                              $date .= "'$dtvalue',";
                                              $err_arr[$i]  = $date;
                                              $date = "";

                                            }
                                            break;
                                case 'hours':
                                            if(strtotime($value->date)>0)
                                            {
                                                $hours = "'$value->hours',";
                                                $arr[$i] = $arr[$i].$hours;
                                                $hours = "";
                                            }
                                            else
                                            {
                                                $hours = "'$value->hours',";
                                                $err_arr[$i] = $err_arr[$i].$hours;
                                                $hours = "";

                                            }
                                            break;
                                case 'desc':
                                            if(strtotime($value->date)>0)
                                            {
                                                $desc = "'$value->desc',"; 
                                                $arr[$i] = $arr[$i].$desc;
                                                $desc = "";
                                            }
                                            else
                                            {
                                                $desc = "'$value->desc',"; 
                                                $err_arr[$i] = $err_arr[$i].$desc;
                                                $desc = "";

                                            }
                                            break;                                  
                                }//End of switch

                            }//End of foreach for object
                            $i++;
                            //echo "<pre>";
                            //print_r($arr);
                            //echo "<pre>";
                            //print_r($err_arr);
                            if(count($arr) !=0)
                            {
                                $string = implode(',',$arr);
                                $insert .= $string.",";
                            }

                            if(count($err_arr) !=0)
                            {
                                $error_string = implode(',',$err_arr);
                                $error_insert .= $error_string.",";
                            }
                            }

                        }//End of If condition for object vars

                    }//End of check for object
                }//End of arrayOfObjects

            }//End of arrayOfObjects count check
            if(count($arr) !=0)
            {
                $insert = substr_replace($insert,';', -1);
                //echo "insert ".$insert;
                $dbconn->multi_query($insert);

            }

            if(count($err_arr) !=0)
            {
                $error_insert = substr_replace($error_insert,';', -1);
                //echo "error insert ".$error_insert;
                $dbconn->multi_query($error_insert);

            }

        $dbconn->close();


        }//end of file checks for current and parent directories    

    }// end of foreach of files in the directory
  }//End of If condition
   //////////To delete files code here//
  //if ($handle = opendir('./excel_files/')) {
//    
//    /* This is the correct way to loop over the directory. */
//      $file_array = array();
//    while (false !== ($file = readdir($handle))) {
//        $file_array[]  = $file;
//    }
//    closedir($handle);
//  }
//if ($handle1 = opendir('./excel_files_backup/')) {
//    
//    /* This is the correct way to loop over the directory. */
//      $file_array1 = array();
//    while (false !== ($file1 = readdir($handle1))) {
//        $file_array1[]  = $file1;
//    }
//    closedir($handle1);
//  }
//  if(count($file_array) >2)
//  {
//    
//    foreach($file_array as $file_key=>$file_value)
//    {
//        if( !is_dir($file_value))
//        {
//            copy("./excel_files/".$file_value,"./excel_files_backup/");                
//
//        }
//    }
//  }        
//  
//  

  /*$folder = 'excel_files';
  $backup = 'excel_files_backup';
  full_copy($folder, $backup);
  function full_copy( $source, $target ) 
     {      
       if ( is_dir( $source ) )      
         {   
          @mkdir( $target );   
          $d = dir( $source ); 
          while ( FALSE !== ( $entry = $d->read() ) )
           { 
           if ( $entry == '.' || $entry == '..' )  
           {     continue; 
             }         
              $Entry = $source . '/' . $entry;   
              //print_r($Entry);exit;
              if ( ! is_dir( $Entry ) )  
              {   
              full_copy( $Entry, $target . '/' . $entry );
              continue;                }      
               copy($target . '/' . $entry);     
                }      
                // unlink($source);
                  $d->close();     
                     }      else     
                        {      
                         copy( $source, $target );    
                             }    }
  */



    if ($handle = opendir('./excel_files/')) {

    /* This is the correct way to loop over the directory. */
      $file_array = array();
    while (false !== ($file = readdir($handle))) {
        $file_array[]  = $file;
    }
    closedir($handle);
  }

  if(count($file_array) >2)
  {

    foreach($file_array as $file_key=>$file_value)
    {
        if( !is_dir($file_value))
        {
            unlink("./excel_files/".$file_value);                

        }
    }
  }        

?>

Edited 3 Years Ago by Dani: Formatting fixed

This article has been dead for over six months. Start a new discussion instead.