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?

Hey.

A single LEFT JOIN should be able to do that.

Consider this:

<?php
// Open a database connection
$dbLink = new mysqli('host', 'usr', 'pwd', 'dbName');
if(mysqli_connect_errno()) {
    trigger_error(mysqli_connect_error(), E_USER_ERROR);
}

// Create and execute the query.
$sql = "SELECT
            j.jobs_id, 
            d.jobs_done_id,
            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";
$result = $dbLink->query($sql);

if($result)
{
    while($row = $result->fetch_assoc())
    {
        // 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($row['jobs_done_id'] != null)
        {
            echo "<span>{$row['jobs_user']}</span>";
            
        }
        else
        {
            echo '<input type="checkbox" etc... />';
        }
    }
}
else
{
    // The query failed. Show to error.
    trigger_error($dbLink->error, E_USER_ERROR);
}
?>
commented: Good answer.. +5

Thank you kindly! I had to modify the connection data, but it works a charm!! :)

Here's my end result code that works:

// Create and execute the query.
$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_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)
        {
            //echo "<span>{$r['jobs_user']}</span> test"; 
            if($i%2 == 0){
			echo "<tr class='rowresult1'>\n";
			echo "<td class='txt' align='left' width='100%'>&nbsp;$jobs_description</td>\n";
			echo "<td class='txt' align='right' colspan='2'>$jobs_comment - $jobs_user - $thour:$tmin</td>\n";
			echo "</tr>\n";
			$i++;
			}else{
			echo "<tr class='rowresult2'>\n";
			echo "<td class='txt' align='left' width='100%'>&nbsp;$jobs_description</td>\n";
			echo "<td class='txt' align='right' colspan='2'>$jobs_comment - $jobs_user - $thour:$tmin</td>\n";
			echo "</tr>\n";
			$i++;
			}
        }
        else
        {
            if($i%2 == 0){
			echo "<tr class='rowresult1'>\n";
			echo "<td class='txt' align='left' width='100%'>&nbsp;$jobs_description</td>";
			echo "<td class='txt' align='right'><input name='jobs_comment[]' style='width: 50px;'></td>";
			echo "<td class='txt' valign='top' align='center'><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' width='100%'>&nbsp;$jobs_description</td>";
			echo "<td class='txt' align='right'><input name='jobs_comment[]' style='width: 50px;'></td>";
			echo "<td class='txt' valign='top' align='center'><input name='checkbox[]' type='checkbox' value='$jobs_id'></td>";
			echo "</tr>\n";
			$i++;
		    }
        }
    }
}
else
{
    die(mysql_error());
}

Ok.. It turns out it kinda works.. 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.

I am assuming it has something to do with the

if($r['jobs_done_id'] != null)

part. :(

If you only want the jobs done today, you need to add that to the SQL 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')
AND DATE(d.jobs_date) = DATE(NOW())
ORDER BY jobs_id ASC

This is your query. I just added the AND clause to the WHERE clause. If that isn't the correct field, you just need to swap it out for the correct one.

P.S.
I left that out in my first code because I couldn't understand the date thing in your first post xD
Should have mentioned that in my first post. Sorry :-)

Hmm, I triedthe query by adding the

AND DATE(d.jobs_date) = DATE(NOW())

. But that is removing the unchecked jobs from the display and still only displaying the completed jobs from yesterday 17-11-2009.

My tbl_jobs_done fields look like this:

jobs_done_id - jobs_id - jobs_date - jobs_comment - jobs_datedone - jobs_timedone - jobs_user
513 - 49 - 2009-11-18 - - 2009-11-18 - 01:31:15 - KRYSJ

I think some kind of check needs to be implemented in to check my $currdate variable (yyyy-mm-dd) if jobs have been added for X jobs_id in tbl_jobs_done

I waited for midnight to come to see what happens come the next day. 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.

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..

Ok, I see.

Edit... didn't see that last post. Give me a sec.

Try moving the AND clause into the LEFT JOIN.
Like:

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
    AND DATE(d.jobs_date) = DATE(NOW())
WHERE j.jobs_datetype IN('$everyday', '$dayname', '$wtype')
ORDER BY jobs_id ASC

This should filter out the old completed jobs and only use todays.

Was what I was trying to do earlier, but it got all mixed up in my head. (I'm working on a bit of a SQL mess of my own as well, which got me confused xD)

Ok, first of all, thank you Alti for taking your time in assisting me. I'll try my best to explain.

What you posted works great. I can tick a box from one of my daily jobs, and it will check it off and display new completed data over the top of the job template.

I assumed that at midnight when it rolls over from 2009-11-17 to 2009-11-18, it would check the tbl_jobs_done table to see if any jobs have been completed. However it does not, so now since it's 2009-11-18, it is displaying jobs as normal, but they are already completed. Even though within the table tbl_jobs_done, the jobs_date and jobs_datedone fields have 2009-11-17 in them.

Does this make sense? It's 03:20 here :)

I tried the new SQL query you posted, but it's displaying the data like this: http://img697.imageshack.us/img697/6389/jobs.jpg

Just so it's fresh, this is the current 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
			AND DATE(d.jobs_date) = DATE(NOW())
        WHERE j.jobs_datetype IN('$everyday', '$dayname', '$wtype')
        ORDER BY jobs_id ASCimage upl");

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

So it is displaying the data as if it were still yesterday?

Where is your SQL server hosted? In your timezone?

I ask because I have test server hosed on a timezone 6 hours later than where I live, and for the longest time I couldn't figure out why my date filtering queries were so far off :)

Perhaps this is the same for you?
If you do: SELECT NOW() .
Does this match the time you would expect it to?

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?

Woot! It works! It clears the old completed jobs and has new jobs ready for completion!

$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
			AND DATE(d.jobs_date) = DATE(NOW()+INTERVAL 16 HOUR)
        WHERE j.jobs_datetype IN('$everyday', '$dayname', '$wtype')
        ORDER BY jobs_id ASC");

Because it's UTC, I had to add +INTERVAL 16 HOUR) to the end of your line and it works! :)

That's great! Glad it worked out :)

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.