Complex query call in php

I've got two tables.

CFBP & CFBG

I need to call from these two where CFBP.Game_ID = CFBG.ID AND (Heres where I'm lost) When in CFBP.Pick =1 Then CFBP.Pick = CFBG.Away  AND When CFBP.Pick =2 Then CFBP.Pick = CFBG.Home WHERE CFBP.User_ID = $user;

Thats the thought process behind it but how do you actually write that???

WOW  I had to read that about 30 times lol

$one=mysql_query("SELECT * FROM `CFBP` WHERE `Game_ID`='$id' AND ....

ok wow I don't think I can do this lol 

I think from the sounds of it you're looking to use two whiles and maybe a couple if statements.

That is all I can really say to help, unless maybe you could write it out a little better?

Pretty difficult to understand...

lol 

Its confusing to write out. I was hoping someone would understand it so i could do it all in one query statement.

oh hmmm... Any time I do stuff with two tables I just usually do something a long the lines of this, which is actually part of a script i wrote i while ago:

<?php
$iag=mysql_query("SELECT * FROM game") or die(mysql_error());
if(mysql_num_rows($iag)>0){
echo "<table width='100%' align='center'>";
echo "<tr><td colspan='2' width='50%' class='header' align='center'>Games</td></tr>\n";

$a=0;

$res=mysql_query("SELECT * FROM game_cat ORDER BY RAND() LIMIT 0,2") or die(mysql_error());
while($row=mysql_fetch_array($res)){
   $cat=clean_up(numeric_only($row['cat_id']));

$cn=mysql_fetch_array(mysql_query("SELECT * FROM game_cat WHERE cat_id='$cat'"));
   $catname=clean_up($cn['cat_name']);
   
   if ($a=='0'){echo "<tr>";}
   
   echo "<td width='50%'>";
   echo "<table width='100%'>";
   echo "<tr><td class='header' align='center' colspan='2'><b>$catname</b></td></tr>";

$ng=mysql_query("SELECT * FROM game WHERE game_cat='$cat'") or die(mysql_error());

if(mysql_num_rows($ng) == 0){
echo "<tr width='100%'><td class='error' width='100%'>There are no $catname games!</td></tr>";
}else{
      
   $res2=mysql_query("Select * FROM game WHERE game_cat='$cat' ORDER BY RAND() LIMIT 0,3") or die(mysql_error());
   while($row2=mysql_fetch_array($res2)){
      $id=clean_up(urlencode($row2['game_id']));
      $views=clean_up($row2['game_views']);
      $name=clean_up($row2['game_name']);
      $thumb=clean_up($row2['game_thumb']);
      $des=clean_up(substr($row2['game_des'], 0, 38));
      $type=clean_up($row2['game_type']);
      
      if($type=='h'){
         $image="<center><img src='games/thumbs/$thumb' height='60' width='60' /></center>";
         }else{
            $image="<center><img src='$thumb' height='60' width='60' /></center>";
            }
      
      echo "<tr width='100%'>";
      echo "<td class='content' width='25%'><a href='index.php?action=game&id=".urlencode($id)."'>$image</a></td>";
      echo "<td class='content' width='85%'><a href='index.php?action=game&id=".urlencode($id)."'><b>$name</b></a><br /><small>$des...<hr><b>Played $views Times</b></small><br />";


$v=mysql_query("SELECT * FROM `vote` WHERE `pageid`='$id' AND pagecat='game'");

for($i=0,$j=0;($row=mysql_fetch_array($v));$i++){
	$j+=$row['rating'];
}

     if($j){
$average=ceil($j/$i);

echo "<small>Rating: <i> $average</i></small>";
  }else{
}
echo"</td>";
      echo "</tr>";
    }
      }//end while
   
   echo "</table><div class='content'><a href='index.php?action=browseg&cat=".urlencode($cat)."'><small>More $catname Games</small></a></div>";
   echo "</td>";
   
   $a++;
   
   if ($a=='2'){echo "</tr>";$a=0;}
   
   }//end while

echo "</table>";
}else{
echo "<div class='error'>No games exist!</div>";
}

?>

I hope that helps a little.. I'm not sure how to do it all in one query, sorry.

WHERE CFBP.Game_ID = CFBG.ID AND ((CFBP.Pick =1 AND CFBP.Pick = CFBG.Away) OR (CFBP.Pick =2 AND CFBP.Pick = CFBG.Home)) AND CFBP.User_ID = $user; 

No that still doesn't work

Thanks anyway.

Your script returns only one line and it returns home and away as if both were picked which isn't even possible

Not nearly enough information to help you out.

Maybe use UNION to combine separate statements for each condition?

Your explanation is not so clear... do you mean that you need to select some columns from table CFBP and from table CFBG and in case that CFBP.Pick =1 select the column CFBG.Away and in case CFBP.Pick=2 select the column CFBG.Home ?  (I didn't include the WHERE to simplify).

If that is what you want then the function ELT should solve it... like

SELECT CFBP.<some column>,

            ELT(CFBP.Pick, CFBG.Away, CFBG.Home) AS "something"

FROM CFBP LEFT JOIN CFBG ON CFBP.Game_ID = CFBG.ID

WHERE CFBP.User_ID = $user;

Hi

Mixing up methods to JOIN tables, something like this

SELECT *
FROM CFBP
INNER JOIN CFBG ON CFBP.Game_ID = CFBG.ID
WHERE ((CFBP.Pick = 1 AND CFBP.Pick = CFBG.Away)
OR (CFBP.Pick = 2 AND CFBP.Pick = CFBG.Home))
AND CFBP.User_ID = $user

All the best

Keith