Jump to content

INNER JOIN causing odd problem, PHP Problem I think!


Recommended Posts

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:

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

     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'];
    <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>

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

 //Other Site Settings...
      if(siteStat() == "Basketball"){
       echo "BSKT";
      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?

Link to comment
Share on other sites

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?


 $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());



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.