PHP/MySQL Query help with Two Tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

PHP/MySQL Query help with Two Tables

Post by julzk »

I have an SQL Query question that I'd like help with if possible :banghead:

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?
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post 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%'>&nbsp;<font class='completedjob'>$jobs_description</font>";
            if (!empty($jobs_comment)) {
                echo "<br>&nbsp;&nbsp;&nbsp;<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&nbsp;<br><font class='rowaddeddata'>$thour:$tmin&nbsp;</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%'>&nbsp;<font class='completedjob'>$jobs_description</font>";
            if (!empty($jobs_comment)) {
                echo "<br>&nbsp;&nbsp;&nbsp;<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&nbsp;<br><font class='rowaddeddata'>$thour:$tmin&nbsp;</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%'>&nbsp;$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%'>&nbsp;$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";
}
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post 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
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post 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..
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post 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
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post 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 :P
My date fields are set as DATE and not DATETIME, so they store only YYYY-MM-DD data.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post by iankent »

sounds like its looking for a stored procedure called TODAY, could be cause there is no mysql today function :banghead:

I do apologise :P I meant CURDATE() not TODAY() :)

if they're stored as date fields you should just be able to do:

Code: Select all

AND d.jobs_date = CURDATE()
hth
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post by julzk »

That does not quite work.

when I add

Code: Select all

AND d.jobs_date = CURDATE()
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.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post by iankent »

julzk wrote:That does not quite work.

when I add

Code: Select all

AND d.jobs_date = CURDATE()
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?
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post 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
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post 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 :)
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post 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 <-
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post 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
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: PHP/MySQL Query help with Two Tables

Post 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?
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: PHP/MySQL Query help with Two Tables

Post 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 :)
Post Reply