How to execute this query? 1

[ERuiz]

I am trying to ececute this query on a php file but just can't get it to work. I'm a newbie, so please don't laugh at my attempt! hahahahaha

What I need is to get the total amount of fields from a table named JOS_THEMES, where VARIABLE = PROFILE_DOMICILE and VALUE = KMIA and VARIABLE = PROFILE_DIVISION and VALUE = APR

Here is my attempt at this:

	$query="SELECT COUNT( ID_MEMBER ) AS count_users"
	. "\n FROM smf_themes"
	. "\n WHERE variable = 'profile_domicile'"
	. "\n AND value = 'KMIA'"
	. "\n AND variable = 'profile_division'"
	. "\n AND value = 'APR'"
	;

Obviously, it's returning the count as "0", because I know that syntax is incorrect. Can anyone help me please?

 

[ERuiz]

Like this?

SELECT count(a.id_member)  FROM `smf_themes` AS a LEFT JOIN smf_themes AS b  LEFT JOIN smf_themes AS c ON a.id_member = b.id_member WHERE a.variable='profile_domicile' AND a.value='KMIA' AND b.variable='profile_division' AND b.value='APR' AND c.variable='profile_rank' AND c.value='Trainee'

Will give it a shot now!

 

[ERuiz]

DRJ478, using the code I posted above, it will not display a result. I think it's missing something in the ON a.id_member = b.id_member part. Have no idea what to do.

 

[DRJ478]

The join has to involve the tables that are joined.
The ON part tells the SQL server how to match up the recods.
In this case it needs to be ON a.ID_MEMBER = c.ID_MEMBER

It is important that you develop an understanding why this is working. Then, in the future you can construct and extend these queries yourself without having to rely on our help.

Your last try was already pretty good - almost there.

 

[ERuiz]

Thanks for your help, and believe me, even though I have no programming knowledge, I enjoy trying to learn little by little.

Nonetheless, here is my next try at it but it still won't give me a result.

$query="SELECT count(a.id_member)  FROM `smf_themes` AS a LEFT JOIN smf_themes AS b  LEFT JOIN smf_themes AS c ON a.id_member = c.id_member WHERE a.variable='profile_domicile' AND a.value='KMIA' AND b.variable='profile_division' AND b.value='APR' AND c.variable='profile_rank' AND c.value='Trainee'";

 

[DRJ478]

All the joins need ON conditions.

$query="SELECT count(a.id_member)  
FROM `smf_themes` AS a 
LEFT JOIN smf_themes AS b [COLOR=red]ON a.id_member = b.id_member[/color]
LEFT JOIN smf_themes AS c ON a.id_member = c.id_member 
WHERE a.variable='profile_domicile' AND a.value='KMIA' AND b.variable='profile_division' AND b.value='APR' AND c.variable='profile_rank' AND c.value='Trainee'";

 

[ERuiz]

Ahhhhhh I see. I didn't know that! hahahah Gonna give it a try now.

 

[ERuiz]

Ditto! Worked perfectly! And believe me, DRJ478, I am a quick learner. Just need a few examples of how to get things accomplished, and I figure out how to build on that. But prepare, as I need yet another query!

 

[ERuiz]

Ok, DRJ478, I played around a bit and got this query working na displaying only the user with the highest profile_current_hours, using the ORDER BY command and LIMIT 1 to display just the top user.

	$query="SELECT *
	FROM `smf_members` AS a
	LEFT JOIN smf_themes AS b ON a.id_member = b.id_member
	LEFT JOIN smf_themes AS c ON a.id_member = c.id_member
	WHERE b.variable='profile_domicile' AND b.value='KMIA' AND c.variable='profile_division' AND c.value='APX'
	ORDER BY 'b.profile_current_hours' ASC
	LIMIT 1";
	$database->setQuery($query);
	$rows = $database->loadObjectList();
	foreach($rows as $row) {
	echo "Most Hours Logged:</td><td width=\"250\"><b>" .$row->memberName. " - APR" .$row->ID_MEMBER. " [" .$row->profile_current_hours. " hrs]</b></td>\n";
	}

The ONLY thing not working is the output of $->profile_current_hours

I figure that it's because I am selecting data from smf_members and profile_current_hours is in smf_themes.

Any idea? And to be honest, this is my last query needed!!! ;-)

 

[ERuiz]

Please?

 

[DRJ478]

Just add b.profiel_hours after SELECT *
(Don't forget the comma).