[SOLVED] Algorithm Help - MySql Help

[Lamez]

Ok, I am trying to order the information that is being displayed by the way it went in, well information  in a different table, with the users team pick, and the bowl name, have no organization at all, However the other table does.

 

Example:

 

-Table: foot_bowls

 

id bowl name team_a team_b

 

-Table: foot_picks

 

bowl_name team user

 

so really, my question is this, how can I take the id from the foot_bowls, and use it to order my other query?

 

My Code:

<?php
ob_start();
$path = "../../";
$title = "Change Your Picks!";
$rank = "yes";
$u_login = "yes";
$ban = "yes";
include ($path."main/include/cons/head.php");
if(siteStat() !== "Football"){ header ("Location: ../index.php"); }
$ck = mysql_query("SELECT `user` FROM `foot_picks` WHERE `user` = '".$user_."' LIMIT 1");
$ck = mysql_num_rows($ck);
if($ck == 0){
  header("Location: ../index.php");
}
  
    $num = 0;
        
        echo '<p class="header">'.$title.'</p><p class="maintext"><form action="ch_pks_proc.php" method="post" name="pks" id="pks">';

	$v = mysql_query("SELECT * FROM `foot_picks` WHERE `user` = '".$user_."'")or die(mysql_error());
        while($n = mysql_fetch_array($v)){
        $nt = $n['team'];
       
        $a = mysql_query("SELECT * FROM `foot_bowls` WHERE `team_a` = '".$nt."' OR `team_b` = '".$nt."'")or die(mysql_error());
        $b = mysql_fetch_array($a);

        $team_a = $b['team_a'];
        $team_b = $b['team_b'];
	$name = $b['name'];
	$pnt_val = $b['pnt_val'];

	$find_a = mysql_query("SELECT `confer` FROM `foot_teams` WHERE '".$team_a."' = `team`")or die(mysql_error());
        $con_a = mysql_fetch_array($find_a);

        $find_b = mysql_query("SELECT `confer` FROM `foot_teams` WHERE `team` = '".$team_b."'");
        $con_b = mysql_fetch_array($find_b);

        $confer_a = $con_a['confer'];
        $confer_b = $con_b['confer'];

        $img_a = $path."main/style/img/Helmets/".$confer_a."/".$team_a.".jpeg";
        $img_b = $path."main/style/img/Helmets/".$confer_b."/".$team_b.".jpeg";

        if($team_a == $nt){
	  $team_ab = "<b>".$team_a."</b>";
	  $team_bb = $team_b;
	  $sa = 'checked="checked"';
	  $sb = "";
	}
	if($team_b == $nt){
	  $team_bb = "<b>".$team_b."</b>";
	  $team_ab = $team_a;
	  $sb = 'checked="checked"';
	  $sa = "";
	}

  $num+=1;
?>
  <table width="100%" border="0">
    <tr>
      <td width="7%">Bowl Name: </td>
      <td width="47%"><b><?php echo $name ?></b>
      <input name="name-<?php echo $num; ?>" type="hidden" id="name-<?php echo $num; ?>" value="<?php echo $name; ?>" /></td>
      <td width="7%">Point Value: </td>
      <td width="39%"><?php echo $pnt_val; ?></td>
    </tr>
    <tr>
      <td colspan="2" align="center" valign="top"><img alt="<?php echo $team_a; ?>" src="<?php echo $img_a; ?>" border="0" /></td>
      <td colspan="2" align="center" valign="top"><img alt="<?php echo $team_b; ?>" src="<?php echo $img_b; ?>" border="0" /></td>
    </tr>
    <tr>
      <td colspan="2" align="center" valign="top"><?php echo $b['rank_a']." ".$team_ab." (".$b['rec_a'].")"; ?></td>
      <td colspan="2" align="center" valign="top"><?php echo $b['rank_b']." ".$team_bb." (".$b['rec_b'].")"; ?></td>
    </tr>
    <tr>
      <td colspan="2" align="center" valign="top"><input name="team-<?php echo $num; ?>" type="radio" value="<?php echo $team_a; ?>" <?php echo $sa; ?> /></td>
      <td colspan="2" align="center" valign="top"><input name="team-<?php echo $num; ?>" type="radio" value="<?php echo $team_b; ?>" <?php echo $sb; ?> /></td>
    </tr>
  </table>
<hr />
<?php
}//end while loop

echo '<input type="submit" name="Submit" value="Submit" /></form></p>';
include ($path."main/include/cons/foot.php");		
?>

 

 

[Lamez]

no clue?

 

Would I need to use a inner join? if so how?

[fenway]

Yes, you'll need to join all these tables, twice for each team table.  Show us your complete DB structure.

[Lamez]

like my entire database, or just those two tables?

[fenway]

Your script refers to 3 tables...

[Lamez]

-foot_bowls

id

name

pnt_val

team_a

team_b

image

date

time

network

place

rank_a

rank_b

rec_a

rec_b

 

-foot_teams

confer

team

 

-foot_picks

id

bowl_name

team

user

[fenway]

Very confusing that your PK / FK don't have the word "id" in them -- also, I'm not sure how you're relating bowls to picks... using the name of the bowl? why not the id????

 

Does this seem to work?  Obviously, "*" isn't appropriate here, but you get the idea.

 

select fp.*, fb.*, fta.*, ftb.*
from
foot_picks as fp
inner join foot_bowls as fb on ( fb.name = fp.bowl_name )
inner join foot_teams as fta on ( fta.team = fb.team_a )
inner join foot_teams as ftb on ( ftb.team = fb.team_b )
where fp.user = '$user'

[Lamez]

Ok, it uses the name, because the name never changes, and it is less scripting on my end to try to find what bowl_name goes with what id.

 

Now I am little bit confused by your code. How in the world do I use such a thing? I thought ordering by the ID in a different table would be easier that what you have compiled there.

[fenway]

Ok, it uses the name, because the name never changes, and it is less scripting on my end to try to find what bowl_name goes with what id.

I don't see how that can possibly be true.

 

Now I am little bit confused by your code. How in the world do I use such a thing? I thought ordering by the ID in a different table would be easier that what you have compiled there.

How to use it? What do you mean? Order by ID? huh?

[Lamez]

how to use it, let me see if I can get it to work to satisfy my needs.

[Lamez]

holy crap, I did it!

 

I used this:

 

<?php
mysql_query("SELECT foot_picks.* FROM foot_picks INNER JOIN foot_bowls ON foot_picks.id = foot_bowls.id ORDER BY foot_bowls.id");
?>

 

-Thanks!