SELECTing from multiple tables

Sharif

I was reading the mysql manual on how to select fields from multiple tables and all I found was this:

"SELECT t1.field, t2.fieldB FROM table1 AS t1, table2 AS t2 WHERE id = '$id'";

Is that the proper way to do it, or are their more better and more efficient methods?

LordShryku

You'd need to tell it how to join the tables, but that's about it

SELECT t1.field, t2.fieldB 
FROM   table1 t1, table2 t2 
WHERE  t1.someField = t2.someField
AND    id = '$id'

Sharif

Okay how's this:

SELECT t1.status, t2.actid
FROM users t1, temp t2
WHERE uid = '$id'

The field uid exists in both tables with the same characteristics

LordShryku

You still need to tell it how to join the tables

SELECT t1.status, t2.actid
FROM   users t1, temp t2
WHERE  t1.uid = t2.uid
AND    uid = '$id'

Sharif

Ok so this is my updated query:

$sql = "SELECT ";
$sql.= "t1.status, t2.activateid ";
$sql.= "FROM ";
$sql.= "users t1, temp t2 ";
$sql.= "WHERE ";
$sql.= "t1.uid = t2.uid ";
$sql.= "AND ";
$sql.= "uid = '$id'";

This gives me this error:

Column: 'uid' in where clause is ambiguous

...

LordShryku

Specify which table it should pull the uid from

$sql.= "uid = '$id'";

Should be
$sql.= "t1.uid = '$id'";
Or
$sql.= "t2.uid = '$id'";

Sharif

Okay that worked. But now I have a new problem, first here is the WHOLE code:

if(!isset($id) || !isset($aid) || empty($id) || empty($aid))
{
	echo 'You have reached this page via a faulty activation link. Please contact the administrator immediately at: <a href="mailto:' . $admin_email . '">' . $admin_email . '</a>';
}
elseif(isset($id) && isset($aid))
{
	$sql = "SELECT ";
	$sql.= "t1.status, t2.activateid ";
	$sql.= "FROM ";
	$sql.= "users t1, temp t2 ";
	$sql.= "WHERE ";
	$sql.= "t1.uid = t2.uid ";
	$sql.= "AND ";
	$sql.= "t1.uid = '$id' ";
	$sql.= "AND ";
	$sql.= "t2.uid = '$id'";
	$query = mysql_query($sql) or die(sql_error());
	while($row = mysql_fetch_array($query))
	{
		$status = $row['status'];
		$activation_id = $row['activateid'];
	}
	if($status == '2')
	{
		echo 'Your account has already been activated. Please <a href="' . $full_domain . 'login.php">click here</a> to login.';
	}
	elseif($activation_id != $aid)
	{
	echo 'You have reached this page via a faulty activation link. Please contact the administrator immediately at: <a href="mailto:' . $admin_email . '">' . $admin_email . '</a>';
	}
	elseif($status == '1' && $activation_id == $aid)
	{
		unset($sql);
		unset($query);
		$sql = "UPDATE ";
		$sql.= "users ";
		$sql.= "SET ";
		$sql.= "status = '2' ";
		$sql.= "WHERE ";
		$sql.= "uid = '$id'";
		$query = mysql_query($sql) or die(sql_error());
		unset($sql);
		unset($query);
		$sql = "DELETE ";
		$sql.= "FROM ";
		$sql.= "temp ";
		$sql.= "WHERE ";
		$sql.= "uid = '$id'";
		$query = mysql_query($sql) or die(sql_error());
		echo 'Your account has been successfully activated. Please <a href="' . $full_domain . 'login.php">click here</a> to login.';
	}
}

If the users is successfully activated, then their activation code and id is deleted from the temp table, so they don't try and activate again. If they go try and activate again, I check their status (if it's 1, they aren't, if it is 2, then they are), even if the user is activated, it totally skips the IF() that determines if the status is 2, and displays the error message that is to display if the activation id is not the same as the one in the link.

I think this happens because the first time the account is activated, the record in the temp table is deleted, and then when the users goes to activate AGAIN, the code tries to check the database for activation id instead of the status... why?

LordShryku

So if they're deleted from the temp table, do they stay in the users table? Becuase your first query is a straight join. It needs to exist in both tables to return a result.

Sharif

What happens is:

User registers:

Info from form goes into table users.
Their uid is collected from the users table, and then a random string is made. uid and the random string is inserted into the temp table.

User goes to activate, when successfully activated, the record in the temp table (holds uid and activation id) is deleted. The status field is updated to 2 in the users table.

If the users tries to activate again,it should check to see if the status is 2 first but it doesn't.

LordShryku

If the user tries activating again, you won't get a result back from your query because it doesn't exist in the temp table. You may want to split this out to two queries, or do a LEFT JOIN instead

Sharif

A left join? What is that? How is that accomplished? 😃