Dynamic page title from different tables with SELECT

[zing]

Hi dear phpfreaks,

Sorry if the topic name doesn't fit what are here but I dont really know how to introduce this.

I dont know as many as I would like about mysql and php. So I am stucked with defining page titles dynamically.

This would be a cup site witch shows the match details. I would like to set the page title like this: Match Details >> Clan1 Vs. Clan2

 

MySQL Version: 5.5.24

 

 

cup_matches table

*clan1

*clan2

 

it contains only the matches where aren't clan names just IDs

 

cup_all_clans table

*name

*ID

 

ID contains the clan ids which is

 

Here is my try but it doesn't seems working. It only shows  the following: Match Details >> Clan1 vs. Clan1

 

case 'cup_matches':
if(isset($_GET['cupID'])) $cupID = (int)$_GET['cupID'];
else $cupID = '';

$clan_1=mysql_fetch_array(safe_query("SELECT clan1 FROM ".PREFIX."cup_matches WHERE cupID=$cupID"));
$getclan1=mysql_fetch_array(safe_query("SELECT name FROM ".PREFIX."cup_all_clans` WHERE ID=$clan_1"));
$clan_2=mysql_fetch_array(safe_query("SELECT clan2 FROM ".PREFIX."cup_matches WHERE cupID=$cupID"));
$getclan2=mysql_fetch_array(safe_query("SELECT name FROM ".PREFIX."cup_all_clans` WHERE ID=$clan_2"));

define('PAGETITLE', settitle($_language->module['matchdetails'].'  »  '.$getclan1['name'].' '.$_language->module['versus'].' '.$getclan2['name']));
	break;

 

It is a bit complicated to explain so sorry if not clear.

Is it possible to do this?

[Christian F.]

Well, besides the fact that the queries should have been only one, using JOINS, the code looks good. With the possible exception of the settitle () or the PAGETITLE constant. Not sure what settitle () does, so it might be required.

 

As for if it's possible: Yes, very much so. I've done similar things all the time.

[jardrake]

Whoa, whoa, whoa. You're working way to hard. Google sql joins and that will help you consolidate your queries into one. It also gives you a better ability to extract exactly what you want.

[fenway]

"SELECT clan1.name AS clan1Name, clan2.name AS clan2Name
FROM ".PREFIX."cup_matches AS matches 
INNER JOIN SELECT name FROM ".PREFIX."cup_all_clans` AS clan1 ON ( clan1.ID = matches.clan1 )
INNER JOIN SELECT name FROM ".PREFIX."cup_all_clans` AS clan2 ON ( clan2.ID = matches.clan2 )
WHERE matches.cupID=$cupID"

Should do the trick.