Complex query call in php

[Lucky2710]

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???

[adamlacombe]

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...

[Lucky2710]

lol 

 

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

[adamlacombe]

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.

[sasa]

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; 

[Lucky2710]

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

[fenway]

Not nearly enough information to help you out.

[jdavidbakr]

Maybe use UNION to combine separate statements for each condition?

[mikosiko]

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;

 

 

 

[kickstart]

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