JOIN Querys?

Sayian

I have a table in mysql called "shocc_active" it keeps track of who is actively on mysite. After they login , it places the username , time and IP of the user into the shocc_active table.

Im trying to build a list of who's online , a php page

In the list im trying to have other details from the shocc_users table , which stores the user's info , such as city , state , ect ...

I have read and found that a join is the best way to do this? although im not having very much success.

The username coloum in shocc_active and the username colum in shocc_users are the relationship.

<?
require_once('./system/config.php');

$sql = "SELECT `username`,`age`,`city`,`state` FROM `shocc_users` JOIN shocc_active on shocc_users.username = shocc_active.username";
$query = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($query) <> 0) {
	while($array = mysql_fetch_array($query)) {
		echo $array['username'];
		echo $array['age'];
		echo $array['state'];
		echo $array['city'];
	}
}

?>

That doesnt yeild me any results , only generating an error from MYSQL saying that the field "username" is ambigious?

Am i doing the query wron?G is this not the right way to go?

Trying to pull data from shocc_users , from each user who is in shocc_active

Thanks for any help

toplay

The error you're getting is because of the "username" column right after the "SELECT". Since both tables have that as a column name, MySQL doesn't know which one you want!

Simply change the first part of your query to:

SELECT shocc_active.username ...

It doesn't matter which one you use in this inner join you're doing.

hth.

Sayian

That gives me an error stating unknown colum shocc_active.username in field list?

toplay

Change it to shocc_users.username then.

Sayian

Toplay , let me as you a couple questions about joins .. you seem to be pretty wise in this matter.

Where is the where clause coming from in this?

$sql = "SELECT `shocc_active.username`,`age`,`city`,`state` FROM `shocc_active` JOIN shocc_users on shocc_users.username = 'Shocc'";

What im trying to do , is take the username from shocc_active , and get info from shocc_user's that has the same username .. but for multiple users .. so can i also use mysql_fetch_array with this?

Then how is the data formated?

mysql_fetch_array = $row

then i could use $row['username']
$row ['city']

ect.. just like normal?

Im kinda lost , and ive readu tutorials , i just still dont get it.