php/mysql query help with two tables.

[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:

$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 (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]

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:

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";
}

[JustLikeIcarus]

Just an idea but why not layout the table like follows:

create table jobs (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
description TEXT,
startdate DATE,
enddate DATE,
occurrence VARCHAR2(25), 
created_by VARCHAR2(50),
completed_by VARCHAR2(50),
completion_datetime DATETIME,
completion_comments DATETIME,
completed TINYINT)

 

Then you could just select from the one table and then loop through the results doing something like:

$status = ($row[completed] == 1) ? '<img src="img/tick2.gif">' : '<input name="checkbox[]'" type="checkbox" value="'.$jobs_id.'">';

echo "<td class='txt' align='center' height='25'>$status</td>";

 

Im normally really big on db normalization however im not sure the data your storing warrants it.  Just my 2 cents anyway.