You are here:  » Insert into database based on date


Insert into database based on date

Submitted by globologic on Mon, 2010-03-15 08:43 in

Hi,

Firstly let me say I totally enjoy this product, it gets well used and abused.

I was wondering how you would insert records into a database based on the date of a specific field.

So it would be something like INSERT into table WHERE date_posted > '2010-03-15 0:0:0:0'

Here is my code, it works 100% inserting into a database, but can't work out where to put the WHERE statement:

<?php
//Used to insert records from .xml into a database after parsing
// written by Jason De Mamiel
  
require("MagicParser.php");
  
mysql_connect("","","") or die(mysql_error());
  
mysql_select_db("") or die(mysql_error());
  
$counter 0;
  function 
myRecordHandler($jobslist)
  {
  global 
$counter;
     global 
$sql;
  
$counter++;
  
mysql_query($sql);
     
$sql "INSERT INTO xml_job_search (job_reference,title, description, ad_details, advertiser_id, advertiser_name, application_method, date_posted)
  VALUES (
'"
.mysql_real_escape_string($jobslist["JOB-REFERENCE"])."',
'"
.mysql_real_escape_string($jobslist["TITLE"])."',
'"
.mysql_real_escape_string($jobslist["DESCRIPTION"])."',
'"
.mysql_real_escape_string($jobslist["ADDETAILS"])."',
'"
.mysql_real_escape_string($jobslist["ADVERTISERID"])."',
'"
.mysql_real_escape_string($jobslist["ADVERTISERNAME"])."',
'"
.mysql_real_escape_string($jobslist["APPLICATIONMETHOD"])."',
'"
.mysql_real_escape_string($jobslist["DATEPOSTED"])."')";
  
mysql_query($sql);
  }
  
// empty table before inserting records
  
$sql "TRUNCATE xml_job_search";
  
mysql_query($sql);
  
// start parse XML to load new records
MagicParser_parse("http://www.jobxapps.com/xml_files/jobx/jobxAU.xml","myRecordHandler","xml|JOBS/CLIENT/JOB/");
   print 
"<p>Processed ".$counter." records.</p>";
print 
"<p>Last SQL statement was: ".$sql."</p>";
print 
mysql_error();
echo 
"Data Inserted!";
exit()
?>

Submitted by support on Mon, 2010-03-15 10:11

Hi,

WHERE clauses within SQL only apply to SELECT statements; so in this scenario
what (as I understand it) you need to do is simply to conditionally perform
the import based on a comparison between $jobslist["DATEPOSTED"] and the
date you're interested in.

PHP's strtotime() function should do the trick; have a go with something
like this:

  function myRecordHandler($jobslist)
  {
     global $counter;
     global $sql;
     // abort (return from function) if DATEPOSTED is before $baseline
     $baseline = strtotime("2010-03-15");
     $dateposted = strtotime($jobslist["DATEPOSTED"]);
     if ($dateposted < $baseline) return;
     // rest of function continues as normal...
     $counter++;
     mysql_query($sql);
     $sql = "INSERT INTO xml_job_search (job_reference,title, description, ad_details, advertiser_id, advertiser_name, application_method, date_posted)
  VALUES (
'".mysql_real_escape_string($jobslist["JOB-REFERENCE"])."',
'".mysql_real_escape_string($jobslist["TITLE"])."',
'".mysql_real_escape_string($jobslist["DESCRIPTION"])."',
'".mysql_real_escape_string($jobslist["ADDETAILS"])."',
'".mysql_real_escape_string($jobslist["ADVERTISERID"])."',
'".mysql_real_escape_string($jobslist["ADVERTISERNAME"])."',
'".mysql_real_escape_string($jobslist["APPLICATIONMETHOD"])."',
'".mysql_real_escape_string($jobslist["DATEPOSTED"])."')";
      mysql_query($sql);
  }

Hope this helps!
Cheers,
David.

Submitted by globologic on Tue, 2010-03-16 02:56

Hi,

Thanks for that.

How would you do it to automatically just pick yesterdays date only so that
it only inserts records for the previous day?

Submitted by support on Tue, 2010-03-16 10:04

Hi,

To do that, in place of:

     $baseline = strtotime("2010-03-15");

use:

     $baseline = strtotime(date("Y-m-d"))-86400;

Hope this helps!
Cheers,
David.