INNER JOIN causing odd problem, PHP Problem I think!

[Lamez]

Ok I just created a INNER JOIN mysql query, and now it is causing a odd problem.

 

This script is on the profile page, and it see if the user got the football team right, and if so it puts a check by the team name, and the point value. Well all the last game it shows everyone got it right, and it shows they got the points, but most did not. Why?

 

Here is the main code:

<?php
if(siteStat() == "Football"){	 
 $q = mysql_query("SELECT foot_picks.* FROM foot_picks INNER JOIN foot_bowls ON foot_picks.id = foot_bowls.id ORDER BY foot_bowls.id");



 	   echo '<table width="100%" border="0">
  <tr>
    <td width="25%"><strong>Bowl Name </strong></td>
    <td width="25%"><strong>Pick</strong></td>
    <td width="25%"><strong>Correct</strong></td>
<td width="25%"><center><strong>Points Earned</strong></center></td>
  </tr>';
  

     while($r = mysql_fetch_array($q)){



$ts = mysql_query("SELECT * FROM `foot_win` WHERE `bowl_name` = '".$r['bowl_name']."'");
$t = mysql_num_rows($ts);
$pn = mysql_fetch_array($ts);

$v = mysql_query("SELECT * FROM `foot_win` WHERE `team` = '".$r['team']."'")or die(mysql_error());
$v = mysql_num_rows($v);

$pa = mysql_query("SELECT * FROM `foot_bowls` WHERE `name` = '".$pn['bowl_name']."'")or die(mysql_error());
$p = mysql_fetch_array($pa);

if($t > 0){//1st IF

   if($v > 0){//Second IF
     $s = '<img src="'.$path.'main/style/img/c.png" />';
     $pnt = $p['pnt_val'];
}else{//Second IF
    $s = '<img src="'.$path.'main/style/img/r.png" />';
    $pnt = "0";
}//Secon IF

}else{//1st IF
   $s = "";
   $pnt = "";
}//1st IF
   
$id = mysql_query("SELECT * FROM `foot_bowls` WHERE `name` = '".$r['bowl_name']."'");
$id = mysql_fetch_array($id);
$id = $id['id'];
?>
<tr>
    <td><a href="<?php echo $path;?>user/foot/bowl.php?bowl_id=<?php echo $id; ?>"><?php echo $r['bowl_name']; ?></a></td>
    <td><?php echo $r['team']; ?></td>
    <td><?php echo $s; ?></td>
<td><center><?php echo $pnt; ?></center></td>
</tr>
<?php

     }//end 1st loop
     echo '</table></p>';

 //Other Site Settings...
    }else
      if(siteStat() == "Basketball"){
       echo "BSKT";
    }else{
      echo "No Pools are Open";
    }
}
?>

 

Its the mysql querys at the beginning, but I think the main problem is that inner join query, does anybody have any idea why?

[Lamez]

do I need to use a LEFT JOIN or a RIGHT JOIN?

[Lamez]

I found the problem! I forgot the where clause, but when I add it nothing is being pulled from the DB at all! Any ideas?

 

<?php 
 $q = mysql_query("SELECT foot_picks.* FROM foot_picks INNER JOIN foot_bowls ON foot_picks.id = foot_bowls.id WHERE foot_picks.user = '".$u."' ORDER BY foot_bowls.id")or die(mysql_error());
?>

 

 

[Lamez]

anyone?

[Lamez]

last bump.

[premiso]

Are you sure $u contains what it should?

[Lamez]

yes, $u = to the username!

 

Now I get this,

Unknown column 'foot_picks.user' in 'where clause'

but user is in the foot_picks table.

[premiso]

If you have access to mysql command line or phpMyAdmin copy that query and test it out there. Are you sure it is called "user" and not "users" ?

[Lamez]

no it is called user, but nothing is being returned.

 

I used phpmyadmin, and I got this

 

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0015 sec)

 

Why!?

[premiso]

Are you sure you have data? Are you also sure you have data for that user? Are you also sure that there is data in picks and bowls that are linked together?

[Lamez]

The answer to the first two questions, are yes. I am not too sure what you mean by the last question. I have fixed my query, I am just not getting anything returned.

[premiso]

You have to link foot_bowls and foot_picks together with a Foreign key. What a foreign key is this:

 

Let's say the picks is the main table and bowls is the subtable. You will need a colum in bowls that is say, pickid which is linked to the pick table on foot_pick.id.

 

This tells the query to pull the results from table bowls and pick where both the pickids are the same. What you probably have is bowls has its own unique generated id and picks has it's own unique generated id. Both need this, but the bowls table also needs an extra column to hold the related picks id.

 

This is how relational databases work. They have to know how to link 2 tables together and usually this is done by a common id, or foreign key. Hope that helps ya. I would read up on Relational databases and foreign keys.

[Lamez]

well, so I need to make a id column in my foot_picks?

 

all I am trying to do is take the id from foot_bowls and order my other query using that id from foot_bowls.

 

Am I headed in the right direction?