php/mysql multiple tables query help :(

Question

julzk 0 Light Poster

15 Years Ago

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?

mysql php

2 Contributors
14 Replies
163 Views
1 Day Discussion Span
Latest Post 15 Years Ago Latest Post by Atli

Atli 182 Posting Pro

15 Years Ago

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);
}
?>

nav33n commented: Good answer.. +5

Atli 182 Posting Pro

15 Years Ago

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

Edited 15 Years Ago by Atli because: Added the P.S.

Atli 182 Posting Pro

15 Years Ago

Ok, I see.

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

Edited 15 Years Ago by Atli because: n/a

Atli 182 Posting Pro

15 Years Ago

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?

Reply to this topic

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.

julzk 0 Light Poster · Answer 1 · 2009-11-17T11:32:10+00:00

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());
}

julzk 0 Light Poster · Answer 2 · 2009-11-17T20:06:15+00:00

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

julzk 0 Light Poster · Answer 3 · 2009-11-17T21:33:45+00:00

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.

julzk 0 Light Poster · Answer 4 · 2009-11-17T21:48:46+00:00

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

Atli 182 Posting Pro · Answer 5 · 2009-11-17T22:10:49+00:00

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)

julzk 0 Light Poster · Answer 6 · 2009-11-17T22:19:53+00:00

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

julzk 0 Light Poster · Answer 7 · 2009-11-17T22:30:00+00:00

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

julzk 0 Light Poster · Answer 8 · 2009-11-17T23:23:41+00:00

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?

julzk 0 Light Poster · Answer 9 · 2009-11-17T23:36:30+00:00

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! :)

Atli 182 Posting Pro · Answer 10 · 2009-11-18T03:41:08+00:00

Atli 182 Posting Pro

15 Years Ago

That's great! Glad it worked out :)