PHP Date Problems

Question

Facte 0 Light Poster

15 Years Ago

Hi there everyone.

I have a script that has been written to generate a feed for sites that mine links to.

It is supposed to return all jobs from my mySQL database that have not expired.
No matter what I do I can only get it to display all jobs or no jobs at all.

The expiry date in the database is in this format 06/26/2009 (which cannot be changed in the database) as I am aware that it makes a difference.

If anyone can help me with this it would be massively appreciated.

<?php
include "development/StripAttributes.php";
$username = "***"; //Database Username
$password = "***"; //Database Password
$hostname = "0.0.0.0"; //Database Host
$dbname = "****"; //Database (or Catalog in MySQL parlance) name
$dbh = mysql_connect($hostname, $username, $password) or die("Could not connect to database server");
$selected = mysql_select_db($dbname, $dbh) or die("Database not found or problem connecting");
$result = mysql_query("SELECT expiry, position, postdate, jobref, salary, jobid, subcounty, country, description FROM jobs");

function stripchars($str)
{
	$row = htmlentities("$str");
    $row = ereg_replace(128, "&euro;", $row); // Euro symbol
    $row = ereg_replace(133, "…", $row); // ellipses
	$row = ereg_replace("&nbsp;", " ", $row); // space
	$row = ereg_replace("&", "&amp;", $row); // and							"&", "&amp;",
	$row = ereg_replace(8226, "″", $row); // double prime
    $row = ereg_replace(8216, "'", $row); // left single quote
    $row = ereg_replace(145, "'", $row); // left single quote
    $row = ereg_replace(8217, "'", $row); // right single quote
    $row = ereg_replace(146, "'", $row); // right single quote
    $row = ereg_replace(8220, """, $row); // left double quote
    $row = ereg_replace(147, """, $row); // left double quote
    $row = ereg_replace(8221, """, $row); // right double quote
    $row = ereg_replace(148, """, $row); // right double quote
    $row = ereg_replace(8226, "•", $row); // bullet
    $row = ereg_replace(149, "•", $row); // bullet
    $row = ereg_replace(8211, "–", $row); // en dash
    $row = ereg_replace(150, "–", $row); // en dash
    $row = ereg_replace(8212, "—", $row); // em dash
    $row = ereg_replace(151, "—", $row); // em dash
    $row = ereg_replace(8482, "™", $row); // trademark
    $row = ereg_replace(153, "™", $row); // trademark
    $row = ereg_replace(169, "&copy;", $row); // copyright mark
    $row = ereg_replace(174, "&reg;", $row); // registration mark
    $row = ereg_replace("","’",$row);//fix SQL
    $final = htmlentities("$row");
	
	return $final;
	
}

// if the file exists already, delete it first to flush data
$xmlfeedfile = "rad.xml";
$filehandle = fopen($xmlfeedfile, 'w');
$itemLink  = $fullurl.'/info_jobid_'. $b[jobid].'.html';

$xmlString = '<'.'?'.'xml version="1.0" encoding="ASCII" '.'?' .'>
<source>
<publisher>***.co.uk</publisher>
<publisherurl>http://www.***.co.uk</publisherurl>';
fwrite($filehandle, $xmlString);
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {

$pos = stripslashes(strip_tags($row['position']));
$date = stripslashes(strip_tags($row['postdate']));
$ref = stripslashes(strip_tags($row['jobref']));
$desc = stripslashes(strip_tags($row['description']));
$cnt = stripslashes(strip_tags($row['country']));
$sal = stripslashes(strip_tags($row['salary']));
$loc = stripslashes(strip_tags($row['subcounty']));
$jid = stripslashes($row['jobid']);
$desc = preg_replace('/&nbsp;/', ' ', $desc);
$desc = preg_replace('/&/', '/&amp;/', $desc);
$desc = preg_replace("/&apos;/", "'", $desc);
$desc = preg_replace('/#/', '£', $desc);
$pos = preg_replace('/&nbsp;/', ' ', $pos);
$pos = preg_replace('/&/', '/&amp;/', $pos);

$xmlString = "<job>\n\t<title><![CDATA[{$pos}]]></title>\n\t<date><![CDATA[{$date}]]></date>\n\t<referencenumber><![CDATA[{$ref}]]></referencenumber>\n\t<salary>{$sal}</salary>\n\t<url><![CDATA[http://www.editorialjobs.co.uk/info_jobid_{$jid}.html]]></url>\n\t<city><![CDATA[{$loc}]]></city>\n\t<country><![CDATA[{$cnt}]]></country>\n\t<description><![CDATA[{$desc}]]></description>\n</job>"; 

fwrite($filehandle, $xmlString);
}
mysql_close($dbh);
fwrite($filehandle, "</source>");
fclose($filehandle);
?>

php

Edited 15 Years Ago by Facte because: n/a

3 Contributors
6 Replies
117 Views
7 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by Atli

Atli 182 Posting Pro

15 Years Ago

The expiry date in the database is in this format 06/26/2009 (which cannot be changed in the database) as I am aware that it makes a difference.

Hi.

If you can not replace you custom string date with a proper date, you will have to have PHP do this for you.

Like, say:

$nonExpired = array();
while($row = mysql_fetch_assoc($sqlResult)) {
  // Assuming "expiry" is the date when the row expires in your custom format.
  if(strtotime($row['expiry']) > time()) {
    $nonExpired[] = $row;
  }
}

You really should try to replace those dates with a proper DATE field, as it takes a lot less space, and it makes it much easier to work with. That would allow you do simply do:

SELECT ... WHERE `expiry` > NOW()

mohdsh85 0 Newbie Poster

15 Years Ago

hi
you can also split the reading date from database split("-",$yourDate)
then arange it as your db engine understand it to allow right compoarism

regards

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Facte 0 Light Poster · Answer 1 · 2009-10-06T17:12:03+00:00

Thanks Atli,

I will give that a go. The only reason I can't change the date is that it is part of a huge (several thousand page) script on a live site that I can't afford to make an error modifying.

Hi.
If you can not replace you custom string date with a proper date, you will have to have PHP do this for you.
Like, say:
$nonExpired = array();
while($row = mysql_fetch_assoc($sqlResult)) {
  // Assuming "expiry" is the date when the row expires in your custom format.
  if(strtotime($row['expiry']) > time()) {
    $nonExpired[] = $row;
  }
}
You really should try to replace those dates with a proper DATE field, as it takes a lot less space, and it makes it much easier to work with. That would allow you do simply do:
SELECT ... WHERE `expiry` > NOW()

Facte 0 Light Poster · Answer 2 · 2009-10-06T17:42:24+00:00

I have tried to add that script in and can't seem to get it to work. Could you possibly let me know how I should best fit it in.

Thanks.

Hi there everyone.

I have a script that has been written to generate a feed for sites that mine links to.

It is supposed to return all jobs from my mySQL database that have not expired.
No matter what I do I can only get it to display all jobs or no jobs at all.

The expiry date in the database is in this format 06/26/2009 (which cannot be changed in the database) as I am aware that it makes a difference.

If anyone can help me with this it would be massively appreciated.

<?php
include "development/StripAttributes.php";
$username = "***"; //Database Username
$password = "***"; //Database Password
$hostname = "0.0.0.0"; //Database Host
$dbname = "****"; //Database (or Catalog in MySQL parlance) name
$dbh = mysql_connect($hostname, $username, $password) or die("Could not connect to database server");
$selected = mysql_select_db($dbname, $dbh) or die("Database not found or problem connecting");
$result = mysql_query("SELECT expiry, position, postdate, jobref, salary, jobid, subcounty, country, description FROM jobs");

function stripchars($str)
{
	$row = htmlentities("$str");
    $row = ereg_replace(128, "&euro;", $row); // Euro symbol
    $row = ereg_replace(133, "…", $row); // ellipses
	$row = ereg_replace("&nbsp;", " ", $row); // space
	$row = ereg_replace("&", "&amp;", $row); // and							"&", "&amp;",
	$row = ereg_replace(8226, "″", $row); // double prime
    $row = ereg_replace(8216, "'", $row); // left single quote
    $row = ereg_replace(145, "'", $row); // left single quote
    $row = ereg_replace(8217, "'", $row); // right single quote
    $row = ereg_replace(146, "'", $row); // right single quote
    $row = ereg_replace(8220, """, $row); // left double quote
    $row = ereg_replace(147, """, $row); // left double quote
    $row = ereg_replace(8221, """, $row); // right double quote
    $row = ereg_replace(148, """, $row); // right double quote
    $row = ereg_replace(8226, "•", $row); // bullet
    $row = ereg_replace(149, "•", $row); // bullet
    $row = ereg_replace(8211, "–", $row); // en dash
    $row = ereg_replace(150, "–", $row); // en dash
    $row = ereg_replace(8212, "—", $row); // em dash
    $row = ereg_replace(151, "—", $row); // em dash
    $row = ereg_replace(8482, "™", $row); // trademark
    $row = ereg_replace(153, "™", $row); // trademark
    $row = ereg_replace(169, "&copy;", $row); // copyright mark
    $row = ereg_replace(174, "&reg;", $row); // registration mark
    $row = ereg_replace("","’",$row);//fix SQL
    $final = htmlentities("$row");
	
	return $final;
	
}

// if the file exists already, delete it first to flush data
$xmlfeedfile = "rad.xml";
$filehandle = fopen($xmlfeedfile, 'w');
$itemLink  = $fullurl.'/info_jobid_'. $b[jobid].'.html';

$xmlString = '<'.'?'.'xml version="1.0" encoding="ASCII" '.'?' .'>
<source>
<publisher>***.co.uk</publisher>
<publisherurl>http://www.***.co.uk</publisherurl>';
fwrite($filehandle, $xmlString);
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {

$pos = stripslashes(strip_tags($row['position']));
$date = stripslashes(strip_tags($row['postdate']));
$ref = stripslashes(strip_tags($row['jobref']));
$desc = stripslashes(strip_tags($row['description']));
$cnt = stripslashes(strip_tags($row['country']));
$sal = stripslashes(strip_tags($row['salary']));
$loc = stripslashes(strip_tags($row['subcounty']));
$jid = stripslashes($row['jobid']);
$desc = preg_replace('/&nbsp;/', ' ', $desc);
$desc = preg_replace('/&/', '/&amp;/', $desc);
$desc = preg_replace("/&apos;/", "'", $desc);
$desc = preg_replace('/#/', '£', $desc);
$pos = preg_replace('/&nbsp;/', ' ', $pos);
$pos = preg_replace('/&/', '/&amp;/', $pos);

$xmlString = "<job>\n\t<title><![CDATA[{$pos}]]></title>\n\t<date><![CDATA[{$date}]]></date>\n\t<referencenumber><![CDATA[{$ref}]]></referencenumber>\n\t<salary>{$sal}</salary>\n\t<url><![CDATA[http://www.editorialjobs.co.uk/info_jobid_{$jid}.html]]></url>\n\t<city><![CDATA[{$loc}]]></city>\n\t<country><![CDATA[{$cnt}]]></country>\n\t<description><![CDATA[{$desc}]]></description>\n</job>"; 

fwrite($filehandle, $xmlString);
}
mysql_close($dbh);
fwrite($filehandle, "</source>");
fclose($filehandle);
?>

Atli 182 Posting Pro · Answer 3 · 2009-10-06T17:46:52+00:00

Try to add this *after* line #54 in your original code:

if(strtotime($row['expiry']) > time()) {
     continue; // Skip to the next iteration of the loop.
}

Atli 182 Posting Pro · Answer 4 · 2009-10-06T17:55:39+00:00

Looking closer at the code, you got a couple of syntax errors in you stripchars function.
Lines #23 through #26 have un-escaped double-quotes inside a double-quoted string.

Meaning: """ should be "\"" or '"' .
If you leave them un-escaped like that, they *should* cause a syntax error, or at least create a bug in the function.