Page 1 of 2
PHP/MySQL Query help with Two Tables
Posted: Mon Nov 16, 2009 4:22 am
by julzk
I have an SQL Query question that I'd like help with if possible
I have two tables "tbl_jobs" and "tbl_jobs_done"
Here are the fields for both tables:
tbl_jobs
-jobs_id
-jobs_description
-jobs_datestart
-jobs_dateend
-jobs_datetype
-jobs_user
-jobs_updateuser
tbl_jobs_done
-jobs_done_id
-jobs_id
-jobs_date
-jobs_comment
-jobs_datedone
-jobs_timedone
-jobs_user
How this works is, I have an SQL Query which goes something like this:
Code: Select all
$result = mysql_query("SELECT * FROM tbl_jobs WHERE jobs_datetype IN('$everyday', '$dayname', '$wtype') ORDER BY jobs_id ASC");
Now, someone ticks a checkbox and clicks on the complete button which submits the completed job data and inserts it into tbl_jobs_done.
How I want it to display is, it will by default check the jobs_id field from both tables to see if it has been done and by current date yyyy-mm-dd within the jobs_date field in tbl_jobs_done (
Code: Select all
using something like $dayname=date('l');
). If it has not been completed, it will show my tickbox next to each displayed item from tbl_jobs, however if it has been completed and can see it's been done within the tbl_jobs_done table, then it'll just return the the same information from the first tbl_jobs, except instead of showing my field with the tickbox, it's going to show the jobs_user field from the tbl_jobs_done.
Is there anyway I can do this? Let it be within PHP else/if statements or the SQL query itself?
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 9:21 am
by julzk
Ok.. I've made some progress.. I've managed to get it to do what I want it to do, but now I'm having another problem. It does the job and appears to be working.. However, I waited for midnight to come to see what happens come the next day. and well, I'm going to cry. It's keeping the same results that I have completed in the list from yesterday instead of starting with a new clean slate for the new day. Come the new day, yyyy-mm-dd, I want the jobs list to be empty, so I need to implement some check on the SQL Query or some php code to change the jobs list for the next day. Anyone know how I can do this?
Here's my code:
Code: Select all
if(!isset($cmd))
{
$longdate=date('d-m-Y');
$everyday=Everyday;
$dayname=date('l');
if($wtype=date('l'))
{
if ($wtype=="Saturday" or $wtype=="Sunday")
{
$wtype=WEEKEND;
}
elseif ($wtype=="Monday" or $wtype=="Tuesday" or $wtype=="Wednesday" or $wtype=="Thursday" or $wtype=="Friday")
{
$wtype=WEEKDAY;
}
}
echo "<form method='post' action=''><table width='98%' cellspacing='1' cellpadding='0' border='0'>\n";
echo "<tr>\n";
echo "<td class='sectiontitle' valign='top' align='left' width='100%'>$longdate Jobs</td>";
echo "<td class='txt' colspan='2' valign='top' align='center'><input name='submit' type='submit' value='Complete'></td>";
echo "</tr>\n";
$result = mysql_query("SELECT
j.jobs_id,
j.jobs_description,
d.jobs_done_id,
d.jobs_timedone,
d.jobs_comment,
d.jobs_user
FROM tbl_jobs AS j
LEFT JOIN tbl_jobs_done AS d
ON j.jobs_id = d.jobs_id
WHERE j.jobs_datetype IN('$everyday', '$dayname', '$wtype')
ORDER BY jobs_id ASC");
if($result)
{
while($r=mysql_fetch_array($result))
{
// Cross Table Fields
$jobs_id=$r["jobs_id"];
$jobs_user=$r["jobs_user"];
// tbl_jobs Fields
$jobs_description=str_replace("\r\n","<br>",$r["jobs_description"]);
$jobs_datestart=$r["jobs_datestartf"];
$jobs_dateend=$r["jobs_dateendf"];
$jobs_datetype=$r["jobs_datetype"];
$jobs_updateuser=$r["jobs_updateuser"];
// tbl_jobs_done Fields
$jobs_date=$r["jobs_date"];
$jobs_datedone=$r["jobs_datedone"];
$jobs_timedone=$r["jobs_timedone"];
$jobs_comment=$r["jobs_comment"];
list($thour, $tmin, $tsec) = explode(':', $jobs_timedone);
// Because we used a LEFT JOIN, if the job did not have a
// counterpart in the jobs_done table, the jobs_done_id will
// be null.
if($r['jobs_done_id'] != null)
{
if($i%2 == 0){
echo "<tr class='rowresult1'>\n";
echo "<td class='txt' align='left' height='25' width='100%'> <font class='completedjob'>$jobs_description</font>";
if (!empty($jobs_comment)) {
echo "<br> <img src='img/jobs_comment.gif'> <font class='rowaddeddata'>Comment:</font> <font class='rowadded'>$jobs_comment</font><br>\n";
}
echo "</td>";
echo "<td class='txt' align='right' height='25'>$jobs_user <br><font class='rowaddeddata'>$thour:$tmin </font></td>";
echo "<td class='txt' align='center' height='25'><img src='img/tick1.gif'></td>";
echo "</tr>\n";
$i++;
}else{
echo "<tr class='rowresult2'>\n";
echo "<td class='txt' align='left' height='25' width='100%'> <font class='completedjob'>$jobs_description</font>";
if (!empty($jobs_comment)) {
echo "<br> <img src='img/jobs_comment.gif'> <font class='rowaddeddata'>Comment:</font> <font class='rowadded'>$jobs_comment</font><br>\n";
}
echo "</td>";
echo "<td class='txt' align='right' height='25'>$jobs_user <br><font class='rowaddeddata'>$thour:$tmin </font></td>";
echo "<td class='txt' align='center' height='25'><img src='img/tick2.gif'></td>";
echo "</tr>\n";
$i++;
}
}
else
{
if($i%2 == 0){
echo "<tr class='rowresult1'>\n";
echo "<td class='txt' align='left' height='25' width='100%'> $jobs_description</td>";
echo "<td class='txt' align='right' height='25'><input name='jobs_comment[]' style='width: 50px;'></td>";
echo "<td class='txt' align='center' height='25'><input name='checkbox[]' type='checkbox' value='$jobs_id'></td>";
echo "</tr>\n";
$i++;
}else{
echo "<tr class='rowresult2'>\n";
echo "<td class='txt' align='left' height='25' width='100%'> $jobs_description</td>";
echo "<td class='txt' align='right' height='25'><input name='jobs_comment[]' style='width: 50px;'></td>";
echo "<td class='txt' align='center' height='25'><input name='checkbox[]' type='checkbox' value='$jobs_id'></td>";
echo "</tr>\n";
$i++;
}
}
}
}
else
{
die(mysql_error());
}
echo "</table></form>\n";
}
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 9:35 am
by iankent
Not 100% sure what it is your looking for. Do you want to start with a clean slate on tbl_jobs or tbl_jobs_done? Either way I think you need to add another condition to the WHERE clause to ignore anything where the date is less than today, so if you want to clear the tbl_jobs_done list for a new day, use something like:
Code: Select all
SELECT j.jobs_id, j.jobs_description, d.jobs_done_id, d.jobs_timedone, d.jobs_comment, d.jobs_user FROM tbl_jobs AS j LEFT JOIN tbl_jobs_done AS d ON j.jobs_id = d.jobs_id
WHERE j.jobs_datetype IN('$everyday', '$dayname', '$wtype') AND d.jobs_timedone > TODAY()
ORDER BY jobs_id ASC");
change the d.jobs_timedone column depending on whether you want to clear tbl_jobs or tbl_jobs_done, and TODAY() should be ok as it is (TODAY() returns todays date with a time of 00:00:00, i.e. anything since the previous midnight)
let me know if I've got the wrong end of the stick

hth
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 9:47 am
by julzk
That does not quite work. I tried the following code: (notice my AND statement, similar to your > TODAY())
Code: Select all
SELECT
j.jobs_id,
j.jobs_description,
d.jobs_done_id,
d.jobs_timedone,
d.jobs_comment,
d.jobs_user
FROM tbl_jobs AS j
LEFT JOIN tbl_jobs_done AS d
ON j.jobs_id = d.jobs_id
WHERE j.jobs_datetype IN('$everyday', '$dayname', '$wtype')
AND DATE(d.jobs_date) = DATE(NOW())
ORDER BY jobs_id ASC
But the problem is, when I use the above query, it's simply displaying completed jobs only from tbl_jobs_done (my completed jobs table). I want it so after midnight, when it rolls over from 2009-11-17 to 2009-11-18, the daily jobs list refreshes itself and can be used all over again. My tbl_jobs table is simply my jobs template table, and the IN('$everyday', '$dayname', '$wtype') SQL part, is simply to check the tbl_jobs table if the daily job should be displayed for today (Wednesday) or Thursday, based upon day of the week. That's why my tbl_jobs field jobs_datetype basically has entries like WEDNESDAY,THURSDAY,FRIDAY,MONDAY,SUNDAY etc..
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 9:52 am
by iankent
Hmm, what about:
Code: Select all
AND DATE(d.jobs_date) > TODAY() AND DATE(d.jobs_date) < TODAY() + INTERVAL 1 DAY
should return anything where the jobs_date is greater than the previous midnight but earlier than the next midnight (i.e., sometime today) - if you'll never have jobs in the table later than today you can drop the second AND
not too sure why you need DATE() around jobs_date? do I assume its stored as varchar/text rather than a datetime column?
edit: you shouldn't need to use your jobs_datetype column, as comparing the dates is enough to tell you which day the jobs fall on
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 9:58 am
by julzk
I tried your AND statement and it returns the following:
FUNCTION tsdportal.TODAY does not exist
In regards the DATE(), I was trying to experiment

My date fields are set as DATE and not DATETIME, so they store only YYYY-MM-DD data.
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 10:06 am
by iankent
sounds like its looking for a stored procedure called TODAY, could be cause there is no mysql today function
I do apologise

I meant CURDATE() not TODAY()
if they're stored as date fields you should just be able to do:
hth
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 10:14 am
by julzk
That does not quite work.
when I add
It's no longer displaying any uncompleted jobs. It's only displaying jobs that are already completed that have the day 2009-11-17 in the jobs_date and jobs_datedone field. However the day right now is 2009-11-18, so my daily jobs page should be displaying a new set of jobs from my template table "tbl_jobs". Instead, it's displaying jobs that were completed yesterday 2009-11-17.
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 10:17 am
by iankent
julzk wrote:That does not quite work.
when I add
It's no longer displaying any uncompleted jobs. It's only displaying jobs that are already completed that have the day 2009-11-17 in the jobs_date and jobs_datedone field. However the day right now is 2009-11-18, so my daily jobs page should be displaying a new set of jobs from my template table "tbl_jobs". Instead, it's displaying jobs that were completed yesterday 2009-11-17.
out of interest, where in the world are you, and where in the world is your mysql server? its still 17-11-09 for me, so if you're now in 2009-11-18 and your mysql server is in a different timezone, CURDATE() could be returning an unexpected value?
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 10:37 am
by julzk
I'm in Australia
It's currently 03:31 AEDT here. The SQL server and the unix box I'm working from are both set to UTC. So you can see what I am talking about, look at this screenshot:
http://img697.imageshack.us/img697/6389/jobs.jpg
Because the day has rolled over to 2009-11-18 from 2009-11-17, it SHOULD display my job list, but nothing should be checked off/completed already as the jobs_date and jobs_datedone within tbl_jobs_done have date values of 2009-11-17
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 10:47 am
by iankent
Well the fact your in Australia and UTC is (almost) equivalent to GMT explains the day difference, you therefore can't use CURDATE() and will need to manually give the correct date (or do something like DATE(NOW() + INTERVAL 660 MINUTE) to adjust for your timezone)
try that first to see what comes up, and if its still showing jobs incorrectly completed then it could be a join problem, but lets get the date sorted first

Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 10:57 am
by julzk
I don't need to change my timezone, it's already changed. When midnight came, the data changed, as it changed to Wednesday, and an extra job appeared in the list as it turned Wednesday, but the existing jobs that were completed were still present.
High up, I have my timezone set here:
Code: Select all
// ##### Date and Timezone Formatting ##### Start ->
$currenttime=gmt2aest(strftime("%Y-%m-%d %H:%M:%S"));
$tmp0=explode(" ",$currenttime);
$currdate=$tmp0[0];
$currtime=$tmp0[1];
$datetoday=gmt2aest(strftime('0 days'));
$tmp1=explode(" ",$datetoday);
$datetodaydate=$tmp1[0];
$datetodaytime=$tmp1[1];
$dateyesterday=gmt2aest(strftime('-1 days'));
$tmp2=explode(" ",$dateyesterday);
$dateyesterdaydate=$tmp2[0];
$dateyesterdaytime=$tmp2[1];
// Daylight Savings function
function gmt2aest ($time){
date_default_timezone_set('GMT');
$date = new DateTime($time);
$aest_time= new DateTimeZone('Australia/Hobart');
$date->setTimezone($aest_time);
$newtime = $date->format("Y-m-d H:i:s");
return $newtime;
}
// Daylight Savings function
function aest2gmt ($time){
date_default_timezone_set('Australia/Hobart');
$date = new DateTime($time);
$gmt_time=new DateTimeZone('GMT');
$date->setTimezone($gmt_time);
$newtime = $date->format("Y-m-d H:i:s");
date_default_timezone_set('GMT');
return $newtime;
}
if(function_exists('date_default_timezone_set'))
{
date_default_timezone_set("Australia/Hobart");
}else{
putenv("TZ=Australia/Hobart");
}
// ##### Date and Timezone Formatting ##### End <-
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 11:03 am
by iankent
I may be wrong, but I don't think that makes any difference to MySQL, so you'll still need to add something like DATE(NOW()+INTERVAL 660 MINUTE) within your query, or alternatively run this query before any others:
SET time_zone = +11:00;
or
SET time_zone = Australia/Hobart;
If you want to confirm it, try running this query, and I'm pretty sure it'll tell you its the 17th!
SELECT NOW()
edit: it's not that I don't believe you btw, I just don't think MySQL will be getting its dates wrong on a SELECT query using CURDATE() unless it genuinely thinks its in a different timezone
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 11:23 am
by julzk
Ahhh.. That may be the problem.. My PHP is passing the correct time/date with set timezone I have further up in my code. But the SQL Server is set to UTC. After running SELECT NOW(), it returned 2009-11-17 12:21:34. So, next step, how to change it? I can't change it on the SQL server itself, so I am assuming something within the SQL query?
Re: PHP/MySQL Query help with Two Tables
Posted: Tue Nov 17, 2009 11:25 am
by iankent
add something like DATE(NOW()+INTERVAL 660 MINUTE) within your query, or alternatively run this query before any others:
SET time_zone = +11:00;
or
SET time_zone = Australia/Hobart;

up to you which method you go for. set time_zone will affect all subsequent queries, the DATE(NOW() + INTERVAL 660 MINUTE) will affect only the column you're comparing against
edit:
another option is "SET GLOBAL time_zone = timezone" if you have superuser privileges on your mysql server, which afaik only needs to be run once and then its done forever
