Help please!

[BeanieMan]

hi, i am trying to make a page that displays my players discoveries within my game.

I need to display either 'Undiscovered' or the 'Discovery Name' .

Currently i do a query for each trait, example..

$traits = SELECT COUNT(*) as `count` FROM `discoveries` WHERE `discovery_name`='Trait1';
if ($traits->count > 0)
$trait1 = 'Trait 1';
else
$trait1 = 'Undiscovered';
$tpl->assign(trait1, $trait1);

Im sure there must be an easier and more dynamic way to do this..

I have 2 tables in a MySQL database.

table discoveries
player, discovery_name

table traits
id, trait_name, type

i need to check if the discovery_name exists in the discoveries table for each 1 of the trait_names in the traits table

Thanks

[traq]

SELECT `trait_name` 
FROM `traits` 
WHERE `trait_name` 
IN(
  SELECT `discovery_name` 
  FROM `discoveries` 
)

or

SELECT DISTINCT `traits`.`trait_name`
FROM `traits`
JOIN `discoveries` 
ON `traits`.`trait_name` = `discoveries`.`discovery_name`

just from initial testing, the JOIN seems to be more efficient. I'm sure someone else can offer a more definitive answer on this subject...

[BeanieMan]

thanks for your Traq,

using your join query, how would i pass to the template is the trait has been discovered or not.. would i have to loop though the result?

[traq]

do you mean your [font=monospace]tpl->assign()[/font] method?

I don't have any idea how your method works or what it does. you would probably need to loop through the result, yes.

[BeanieMan]

anyone have any more advice on this please?
im really stuck on this, im trying to avoid over 100 queries per page request and having to add more queries everytime i add i new trait.
im not sure how to create a loop from traq's method..

[traq]

figure out:

--what you need to do
--what info (variables) you need to do it
--how many times you need to do it (once for every row in $result...?)

ask if you get stuck with a specific problem, but you need to provide info and explain what you're doing - can't help if we don't know what's going on.

[Roger_Ramjet]

With all respect to traq, this should be a LEFT JOIN query.

SELECT t.trait_name, d.discovery_name
FROM traits t LEFT JOIN discoveries d ON t.trait_name = d.discovery_name
WHERE d.player = $player

I am assuming this is per player so the WHERE clause is required, if not then leave it out.

SELECT DISTINCT t.trait_name, d.discovery_name
FROM traits t LEFT JOIN discoveries d ON t.trait_name = d.discovery_name
ORDER BY t.trait_name

Either way, if there is no trait in the discoveries table then d.discovery_name will be NULL which you process thus

foreach ($result as $row) {
  if (is_null($row['discovery_name'])) {
     $trait1 = 'Undiscovered';
  } else {
     $trait1 = $row['trait_name'];
  }
}

[traq]

Roger Ramjet;11003971 wrote:

With all respect to traq, this should be a LEFT JOIN ...

Thanks!