Forum Moderators: coopster
The command i am using for the 14 days is this, but i added a report yesterday and still not getting any results.
$query ="SELECT * FROM jobs WHERE date > DATE_SUB(now(), INTERVAL 14 DAY)";
Can someone see where i am going wrong.
`datedate`date`For more information: [dev.mysql.com...]
but still having problems, so im going to give you all the details so maybe then someone can help.
I have a table called jobs. Where there is a field called date. The date field is just a standard VARCHAR, and i input the date as 02/19/2006 (MM/DD/YYYY).
Please ask if you need any more information
$query ="SELECT * FROM jobs WHERE STR_TO_DATE(`date`, '%m/%d/%Y') > DATE_SUB(CURDATE(), INTERVAL 14 DAY)";
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(`date`, '%m/%d/%Y') > DATE_SUB(CURDATE(), INTERVAL 14 DAY)' at
Also, although both points have been recognized here, I would take them one step further. If you aren't too far along in your development and can easily change that table without having to rewrite many lines of code, I would do two things --
So the code i wrote for this was:
<?php echo date("l, F d, Y", ' . $date . ');?>
I keep just getting 31 December 1969. Which after some research have found this is caused when the date() cant find a usable date, ie. empty. How come it sais its empty when $date definetly exists. As when i do echo $date it works fine, only when trying to convert to words it mucks up.
echo date("l, F d, Y", $date); perhaps?
<?php $date2 = explode("-", $date);?>
<?php $year = $date2[0];?>
<?php $month = $date2[1];?>
<?php $day = $date2[2];?>
<?php echo date("l, F d, Y", mktime($year, $month, $day));?>
SELECT DATE_FORMAT('2006-02-20', '%W, %M %e, %Y');
-- will return:
-- Monday, February 20, 2006 $date = '2006-02-20';
list($year, $month, $day) = explode('-', $date);
if (checkdate($month, $day, $year)) {
echo date("l, F d, Y", mktime(0,0,0,$month,$day,$year));
} else {
// bad date
}
[edited by: coopster at 2:24 pm (utc) on Feb. 20, 2006]
Ill give you full details to how this is working. I have a page called view_jobs.php, this page lists all the jobs, and most jobs having different dates.
======= START OF CODE
<?php
include('result_top.inc');
require_once ('mysql_connect.php');
$query ="SELECT * FROM jobs WHERE `date` > DATE_SUB(CURDATE(), INTERVAL 14 DAY)";
$result = mysql_query($query);
$num=mysql_numrows($result);
if ($num > 0) {
}
if (!$result)
die(mysql_error());
while ($r = mysql_fetch_array($result)) {
extract($r);
$job_id=mysql_result($result,$i,"job_id");
$client=mysql_result($result,$i,"client");
$date=mysql_result($result,$i,"date");
$time=mysql_result($result,$i,"time");
$location=mysql_result($result,$i,"location");
$machine=mysql_result($result,$i,"machine");
$operator=mysql_result($result,$i,"operator");
$date2 = explode("-", $date);
$year = $date2[0];
$month = $date2[1];
$day = $date2[2];
?>
<span class="style3">
<tr>
<td><font face="Arial" size="2"><?php echo $job_id;?></font></td>
<td><font face="Arial" size="2"><?php echo $client;?></font></td>
<td><font face="Arial" size="2"><?php echo $date2;?></font></td>
<td><font face="Arial" size="2"><?php echo date("l, F d, Y", mktime($year, $month, $day));?></font></td>
<td><font face="Arial" size="2"><?php echo $time;?></font></td>
<td><font face="Arial" size="2"><?php echo $location;?></font></td>
<td><font face="Arial" size="2"><?php echo $machine;?></font></td>
<td><font face="Arial" size="2"><?php echo $operator;?></font></td>
<td><a href="view_job.php?id=<?php echo $job_id;?>"><img src="view.jpg" alt="View Job" width="12" height="16" border="0" /></a> </td>
<td><a href="edit_job.php?id=<?php echo $job_id;?>"><img src="edit.png" alt="Edit Job" border="0" /></a> </td>
<td><a href="javascript:confirmDelete('delete_job.php?id=<?php echo $job_id;?>')"><img src="delete.png" alt="Delete Job" width="16" height="16" border="0" /></a>
</td>
<td><a href="print_job.php?id=<?php echo $job_id;?>"><img src="printer.jpg" alt="Print Job" width="19" height="17" border="0"></a></td>
</tr>
</span>
<?php
$i++;
}
echo "</table>";
?>
======= END OF CODE
$query = "SELECT
*,
DATE_FORMAT(date, '%W, %M %e, %Y') as dateFormatted
FROM jobs
WHERE
date > DATE_SUB(CURDATE(), INTERVAL 14 DAY)
";
$result = mysql_query($query);
Now, loop through the result set and build the HTML (also note the use of htmlentities):
// No need to fetch each row index as you already extracted them all.
// Also, if you are going to extract, you may want to consider just
// using mysql_fetch_assoc as opposed to mysql_fetch_array, even
// though PHP won't put the numerical indexes into the global scope
// because they would be invalid variable names.
while ($r = mysql_fetch_assoc [php.net]($result)) {
extract($r);
?>
<span class="style3">
<tr>
<td><font face="Arial" size="2"><?php echo htmlentities [php.net]($job_id);?></font></td>
<td><font face="Arial" size="2"><?php echo htmlentities($client);?></font></td>
<td><font face="Arial" size="2"><?php echo htmlentities(dateFormatted);?></font></td>
<td><font face="Arial" size="2"><?php echo htmlentities($time);?></font></td>
<td><font face="Arial" size="2"><?php echo htmlentities($location);?></font></td>
<td><font face="Arial" size="2"><?php echo htmlentities($machine);?></font></td>
<td><font face="Arial" size="2"><?php echo htmlentities($operator);?></font></td>
<td><a href="view_job.php?id=<?php echo $job_id;?>"><img src="view.jpg"
alt="View Job" width="12" height="16" border="0" /></a></td>
<td><a href="edit_job.php?id=<?php echo $job_id;?>"><img src="edit.png"
alt="Edit Job" border="0" /></a></td>
<td><a href="javascript:confirmDelete('delete_job.php?id=<?php
echo $job_id;?>')"><img src="delete.png" alt="Delete Job"
width="16" height="16" border="0" /></a></td>
<td><a href="print_job.php?id=<?php echo $job_id;?>"><img src="printer.jpg"
alt="Print Job" width="19" height="17" border="0"></a></td>
</tr>
</span>
<?php
}
?>
As far as the result set returning only the 15th of May, you've got me baffled here. Your query says to return only rows where the date is less than two weeks ago. Either your server date is off, or ... did you reformat your DATE data before you altered that column? You may want to run a query against the table and have a look at that column's output -- I bet you forgot to reformat the date data.
Yes, the code will work, but I truly hope you were more focused on the how it works and a better understanding of PHP retrieving and displaying information from a MySQL database. There is a primer in our PHP Forum Library [webmasterworld.com] that offers some more details on the Basics of extracting data from MySQL using PHP [webmasterworld.com]. It is light reading and might help in better understanding the process.
Scripting languages combined with a database are powerful, and fun too. You can pull and format data in so many ways. The value of understanding how the two can work together and knowing that the database can perform some of the work and formatting becomes a balance. The important thing to remember is that the data coming from the database can often be prepared to look just the way we want it to before it gets handed back to us. Often, but not always. How we retrieve the data is pretty much always the same. We open a database connection handler, execute the query, and process the result set.
I'm happy that you are moving forward with this project and with PHP, brodie_r. If you have any more questions, you know where to find us.
By the way, there is a script in the PHP Forum Library [webmasterworld.com] that you can use to see another of PHP's date manipulation functions. It's in message #6 of Good PHP solutions to small problems [webmasterworld.com]. More tricks of the trade, so to speak. That whole thread is loaded with some goodies.
