Table Join

[thisisrealnifty]

Hello I was wondering if someone could write me a query so that my code will do the following:

 

I want it to take the $task_id that is active for a page and look on the table tasks. Using the $task_id thats active, grab the user_id that's on its row. Then, on the table users, grab the email from the row who has the same user_id.

 

Below is the code I have so far. When its done is should send an email to the user who is associated with the $task_id. I dont know much about PHP and MySql, and am just looking for some help.

 

Where it says $to_them, thats the where it should send the email to the user.

 

<?php
include('auth.php');
include('functions.php');


if($_POST['submit']){
	$com_id = $_POST['com_id'];
	$mode = $_POST['mode'];

	$project_id = $_POST['project_id'];
	$task_id = $_POST['task_id'];
	$comment = $_POST['comment'];





	$post_getmessage = $_REQUEST['comment']; 
	$to_me = "email address here"; # your email address 
	$headers = "From: email address here"; # your site
        $to_subject ="New Client Portal Comment!"; # retrieves subject 
        $to_message = $post_getmessage; # retrieves messages 

	$valid = 1;
	if($mode == 'add'){
		if(strlen($comment) > 0){
			$cdate = time();
			if($con_access_level == 'admin'){
				mysql_query("insert into comments(task_id, comment, cdate, user_id, uread, aread) values ('$task_id', '$comment', '$cdate', '$con_user_id', 'N', 'Y')");
				mail($to_them, $to_subject, "$to_message", $headers);
			}else{
				mysql_query("insert into comments(task_id, comment, cdate, user_id, uread, aread) values ('$task_id', '$comment', '$cdate', '$con_user_id', 'Y', 'N')");
				mail($to_me, $to_subject, "$to_message", $headers);
			}
		}else{
			$err = "<div class=err>* Comment cannot be empty!</div>";
			$valid = 0;
		}
	}elseif($mode == 'edit'){
				//security check start
				if($con_access_level <> 'admin') {
				echo "<p><a href='main.php'>Access Denied</a></p>";
				exit();
				}
				//security check end
		if(strlen($comment) > 0){
			mysql_query("update comments set comment = '$comment' where com_id = $com_id");
		}else{
			$err = "<div class=err>* Comment cannot be empty!</div>";
			$valid = 0;
		}
	}else{
		mysql_query("delete from comments where com_id = $com_id");
	}

	if($valid == 1){
		header("Location: task.php?project_id=$project_id&task_id=$task_id&mode=edit&nc=y");
		exit();
	}
}elseif($_POST['exit']){
	$project_id = $_POST['project_id'];
	$task_id = $_POST['task_id'];
	header("Location: task.php?project_id=$project_id&task_id=$task_id&mode=edit");
	exit();
}else{
	$com_id = $_GET['com_id'];
	$project_id = $_GET['project_id'];
	$task_id = $_GET['task_id'];
	$mode = $_GET['mode'];
}

if($mode != 'add'){
	$com = mysql_fetch_array(mysql_query("select * from comments where com_id = $com_id"));
}

?>

[mapleleaf]

Looks like you have the task_id in the $_POST so you can simply get it without any joins

 

"SELECT email from users WHERE task_id = $_POST['task_id']"

 

You will of course want to run some security on all you $_POST variables before inserting into the database

[thisisrealnifty]

It had an error but i did a search and found that  $_POST['task_id'] should be  $_POST[task_id].

 

but, it still doesnt send an email to the user. and that sql looks like it should to me.. but i dont know much about it.

 

heres the revised code:

 

<?php
include('auth.php');
include('functions.php');


if($_POST['submit']){
	$com_id = $_POST['com_id'];
	$mode = $_POST['mode'];

	$project_id = $_POST['project_id'];
	$task_id = $_POST['task_id'];
	$comment = $_POST['comment'];


	$to_them = mysql_query("SELECT email from users WHERE task_id = $_POST[task_id]");


	$post_getmessage = $_REQUEST['comment']; 
	$to_me = "[email protected]"; # your email address 

	$headers = "From: [email protected]"; # your site
        $to_subject ="New Client Portal Comment!"; # retrieves subject 
        $to_message = $post_getmessage; # retrieves messages 

	$valid = 1;
	if($mode == 'add'){
		if(strlen($comment) > 0){
			$cdate = time();
			if($con_access_level == 'admin'){
				mysql_query("insert into comments(task_id, comment, cdate, user_id, uread, aread) values ('$task_id', '$comment', '$cdate', '$con_user_id', 'N', 'Y')");
				mail($to_them, $to_subject, "$to_message", $headers);
			}else{
				mysql_query("insert into comments(task_id, comment, cdate, user_id, uread, aread) values ('$task_id', '$comment', '$cdate', '$con_user_id', 'Y', 'N')");
				mail($to_me, $to_subject, "$to_message", $headers);
			}
		}else{
			$err = "<div class=err>* Comment cannot be empty!</div>";
			$valid = 0;
		}
	}elseif($mode == 'edit'){
				//security check start
				if($con_access_level <> 'admin') {
				echo "<p><a href='main.php'>Access Denied</a></p>";
				exit();
				}
				//security check end
		if(strlen($comment) > 0){
			mysql_query("update comments set comment = '$comment' where com_id = $com_id");
		}else{
			$err = "<div class=err>* Comment cannot be empty!</div>";
			$valid = 0;
		}
	}else{
		mysql_query("delete from comments where com_id = $com_id");
	}

	if($valid == 1){
		header("Location: task.php?project_id=$project_id&task_id=$task_id&mode=edit&nc=y");
		exit();
	}
}elseif($_POST['exit']){
	$project_id = $_POST['project_id'];
	$task_id = $_POST['task_id'];
	header("Location: task.php?project_id=$project_id&task_id=$task_id&mode=edit");
	exit();
}else{
	$com_id = $_GET['com_id'];
	$project_id = $_GET['project_id'];
	$task_id = $_GET['task_id'];
	$mode = $_GET['mode'];
}

if($mode != 'add'){
	$com = mysql_fetch_array(mysql_query("select * from comments where com_id = $com_id"));
}

?>

[thisisrealnifty]

I looked at that code you gave me and it seems to miss a step. If Im reading it correctly it looks like its saying that the task_id is on the table users, but its not. the user_id is on the table users and the user_id is on tasks. and the email which is what im after is on users, but the column they both share is user_id. make since? im horrible at describing this. :/

[thisisrealnifty]

i made this: $to_them = mysql_query("SELECT email from users WHERE users.user_id == tasks.user_id = $_POST[task_id]");

 

it doesnt work obviously but it includes all the stuff i think it should and it may give you a better idea of the table setup.

 

is there a way to the above??