Jump to content

Compairing 2 tables and worse!

law

By law
in PHP Coding Help

 Share

Recommended Posts

law Member

law

Posted
Posted

Ok.. I cannot even wrap my head around how to accomplish this task.. im a novice can anyone help me? I know i need to produce a loop but I have no idea how to do that given my circumstances. (this code is for a football pool website)

HERE IS WHAT I WANT TO ACCOMPLISH

  • the users PICKS table must be compared with the WINNERS table (the winners table should have higher precedence)
  • using the comparison between PICKS and WINNERS generate a table with the # of correct picks
  • the list should display user name and sort the list by the highest # of correct picks
  • i also need to generate a link after the # of correct items and it will display all of the picks made by that user

 

MEMBERS TABLE
CREATE TABLE `members` (                                 
           `id` int(4) NOT NULL auto_increment,                   
           `nickname` varchar(30) NOT NULL default '',            
           `password` varchar(30) NOT NULL default '',            
           `email` varchar(30) default '',                        
           `name` varchar(30) default '',                         
           `payment` int(1) default '0',                          
           `picks` int(1) default '0',                            
           PRIMARY KEY  (`id`),                                   
           UNIQUE KEY `nickname` (`nickname`)                     
         ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1  


PICKS TABLE
picks   CREATE TABLE `picks` (                                                                  
          `id` int(4) NOT NULL default '0',                                                     
          `Game1` int(1) default NULL,                                                          
          `Game2` int(1) default NULL,                                                          
          `Game3` int(1) default NULL,                                                          
          `Game4` int(1) default NULL,                                                          
          `Game5` int(1) default NULL,                                                          
          `Game6` int(1) default NULL,                                                          
          `Game7` int(1) default NULL,                                                          
          `Game8` int(1) default NULL,                                                          
          `Game9` int(1) default NULL,                                                          
          `Game10` int(1) default NULL,                                                         
          `Game11` int(1) default NULL,                                                         
          `Game12` int(1) default NULL,                                                         
          `Game13` int(1) default NULL,                                                         
          `Game14` int(1) default NULL,                                                         
          `Game15` int(1) default NULL,                                                         
          `Game16` int(1) default NULL,                                                         
          `Game17` int(1) default NULL,                                                         
          `Game18` int(1) default NULL,                                                         
          `Game19` int(1) default NULL,                                                         
          `Game20` int(1) default NULL,                                                         
          `Game21` int(1) default NULL,                                                         
          `Game22` int(1) default NULL,                                                         
          `Game23` int(1) default NULL,                                                         
          `Game24` int(1) default NULL,                                                         
          `Game25` int(1) default NULL,                                                         
          `Game26` int(1) default NULL,                                                         
          `Game27` int(1) default NULL,                                                         
          `Game28` int(1) default NULL,                                                         
          `Game29` int(1) default NULL,                                                         
          `Game30` int(1) default NULL,                                                         
          `Game31` int(1) default NULL,                                                         
          `Game32` int(1) default NULL,                                                         
          `gamepoints` int(3) default NULL,                                                     
          `qbpassyards` int(3) default NULL,                                                    
          PRIMARY KEY  (`id`)                                                                   
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC 

 

Once a user makes a PICKS for a game it is stored in the database as "1" for the FAVORITE team and "2" for the UNDERDOG

the WINNER table is identical to the PICKS table except it has no id field.. the winner table will have the winning teams number in it.. everyone of the user's PICKS that are the same as the WINNER table numbers should add up and produce a total # of correct answers

i hope this makes since.. thanks for any advice or help in advance

Link to comment
Share on other sites
cooldude832 Advanced Member

cooldude832

Posted
Posted

what field links the user to the "picks" table? also you shouldn't have 2 primary keys be named the same across multiple tables so make the users table is be User_ID and picks id be Pick_ID

 

as you are making this harder by storing 30 games in 1 row store 1 game in 1 row and add on a week to them or something else to link them internalyl

 

Link to comment
Share on other sites
cooldude832 Advanced Member

cooldude832

Posted
Posted

this is what I'd do if I had to do it with tables  as

 

USERS

UserID (Primary Key Auto Inc Integer)

Username (email)

etc.

 

PICKS

PickID

GameID

PickerID (UserID of picker)

Pick (Bool 0 or 1)

 

WINNERS

GameID (same as Picks ID)

Winner (Bool 0 or 1)

 

This query would work perfectly

<?php
define("WINNERS_TABLE", "winners");
define("PICKS_TABLE", "picks");
define("USERS_TABLE", "members");
$fields = array(
			"COUNT(".PICKS_TABLE.".PickID) as Win_count",
			USERS_TABLE.".nickname as User_username",
			USERS_TABLE.".id as User_id"
		);
		$fields = implode(" , ",$fields);
		$q = "Select ".$fields." from `".USERS_TABLE."`, `".WINNERS_TABLE."` , `".PICKS_TABLE."`
		Where  ".PICKS_TABLE.".PickerID=".USERS_TABLE.".UserID  and ".WINNERS_TABLE.".Winner = ".PICKS_TABLE.".Pick
		and ".WINNERS_TABLE.".GameID = ".PICKS_TABLE.".GameID
		Group by ".USERS_TABLE.".UserID
		Order by Win_count";
		$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
		$i = 0;
		while($row = mysql_fetch_assoc($r)){
			foreach($row as $key => $value){
				$data[$i][$key] = $value;
			}
			$i++;
		}
		print_r($data);
?>

 

 

Link to comment
Share on other sites
law Member

law

Posted
  • Author
Posted

ok i understand  what your saying about restructuring the tables in that fashion..  and it makes since but i would have to rewrite my pickregistration process.. and im a novice so once again it would require some sort of while statement.. i am able to read your code and understand what you are doing but... i don't know how to "think about it" the way you do to come up with how it should work? i guess that comes with practice?? here is my current pick registration system

 

1st page is a Form with game names and team names and radio buttons.. the user just selects the teams corresponding radio button

2nd page is the actual processing of the forum (displayed below)

<?php session_start();
if(isset($_SESSION['ses_user'])){
$tmp=$_SESSION['ses_user'];	

$var1 = $_POST['game1'];
$var2 = $_POST['game2'];
$var3 = $_POST['game3'];
$var4 = $_POST['game4'];
$var4 = $_POST['game4'];
$var5 = $_POST['game5'];
$var6 = $_POST['game6'];
$var7 = $_POST['game7'];
$var8 = $_POST['game8'];
$var9 = $_POST['game9'];
$var10 = $_POST['game10'];
$var11 = $_POST['game11'];
$var12 = $_POST['game12'];
$var13 = $_POST['game13'];
$var14 = $_POST['game14'];
$var15 = $_POST['game15'];
$var16 = $_POST['game16'];
$var17 = $_POST['game17'];
$var18 = $_POST['game18'];
$var19 = $_POST['game19'];
$var20 = $_POST['game20'];
$var21 = $_POST['game21'];
$var22 = $_POST['game22'];
$var23 = $_POST['game23'];
$var24 = $_POST['game24'];
$var25 = $_POST['game25'];
$var26 = $_POST['game26'];
$var27 = $_POST['game27'];
$var28 = $_POST['game28'];
$var29 = $_POST['game29'];
$var30 = $_POST['game30'];
$var31 = $_POST['game31'];
$var32 = $_POST['game32'];
$var33 = $_POST['gamepts'];
$var34 = $_POST['qbyards'];

include_once("./dbconfig.php");

$idresult = mysql_query("SELECT id FROM members WHERE nickname = '$tmp'") or die(mysql_error());
$id = mysql_fetch_array ($idresult) or die(mysql_error());
//	echo "$id[0]";

	include_once('./includes/head.inc');
	include_once('./includes/logo.inc');
	include_once('./includes/membermenu.inc'); 	

	$result=mysql_query("INSERT INTO picks (id, game1, game2, game3, game4, game5, game6, game7, game8, game9, game10, game11, game12, game13, game14, game15, game16, game17, game18, game19, game20, game21, game22, game23, game24, game25, game26, game27, game28, game29, game30, game31, game32, gamepoints, qbpassyards) VALUES('$id[0]', '$var1', '$var2', '$var3', '$var4','$var5','$var6','$var7','$var8','$var9','$var10','$var11','$var12','$var13','$var14','$var15','$var16','$var17','$var18','$var19','$var20','$var21','$var22','$var23','$var24','$var25','$var26','$var27','$var28','$var29','$var30','$var31','$var32','$var33','$var34')") or die("<font size='3' color='red'><b>ERROR:</b>Please check to make sure that you have selected a team  from <b>EVERY</b> game, and that you have filled in the <b>TIEBREAKERS</b>.</font>");
// echo "$result";
	$addpicks=mysql_query("UPDATE members SET picks = 1 WHERE id = '$id[0]'") or die(mysql_error());
	print "** Thank you <B><font color=#adfcff>";
	print $tmp;
	print "</B></font> for registering your picks. **";

include_once('./includes/memberpicksbody.inc');
include_once('./includes/footer.inc');      
} else {
header("Location:index.php?notloggedin=1");
}
?>

The session as you can see from the code is the user name which i use to find the user ID. As you may have notcied in my tables pick.id=user.id. I am going to change my tables to the resemble the ones you have shown above. Given that how should i restructure my code to fit your new table design? Thank you very much

Link to comment
Share on other sites
cooldude832 Advanced Member

cooldude832

Posted
Posted

to input data just have a form that has grouped radios like


<input type="radio" name="picks['game_ID']k" value="0" />Underdog<br />

<input type="radio" name="picks['game_ID']" value="1" />Favorite<br />

 

So then you have an arary of "picks" in your $_POST and you can quickly say

<?php
$userid = 5; #Set this as it needs to be
foreach($_POST['picks'] as $key => $value){
   $q = "Insert into `picks` (UserID, GameID, Pick) VALUES('".$userid."', '".$key."', '".$value."')";
   $r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
}
?>

 

And alll the picks will be stored according to the gameID and the UserID then when the scores are in and the winners table is update the stats will adjsut accordingly.

 

To help you out a good idea would be to have a 4th table called "games" that stores all the data relating to that GameID  and then reference in your queries like I did.

 

Also in the winners table make the winner column be a int(2) so you can have values of 0,1,2 in there if its 2 that means the game is undecided if its 0 it means the game was won by underdog if its 1 game was won by favortie so the add to your query when calculating scores to your where clause

and winners.winner != '2'

 

that will prevent it from mis scoring games that are not already entered (actually it really doesn't matter, but if you pre populate the winners table you can't have a blank value in the winner field and since I used 0 as the underdog choice you need to substitute in a non choice with 2 or some other integer).

Link to comment
Share on other sites
law Member

law

Posted
  • Author
Posted

Thank you very much! I think that i have most of your queries up and working on my site! I just have one last question when i use your query i get a result like this

Array ( [0] => Array ( [example] => hgjkhgkjhgkjghjkhgkjhgk

as my print out.. how can i grab just the information and put it into a nice looking table?

I have seen some "while looping" tables before.. but i don't really understand how to format the results to make a readable table.

Thank you again very much! This forum is a great asset! I really appreciate what you guys do here.

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.

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

×
 Share
Go to topic listing
×
×
  • 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.