Solved

Query

Posted on 2011-02-15
34
417 Views
Last Modified: 2012-05-11
Hopefully I can explain this well enough for someone to understand.
I am working with a database that has Baseball stats in it. Within the database there are 3 tables that i am concerned with.

1. Master - basically has all the personal information about all players. Birthday, college, etc.
2. Batting - has all the batting stats for each hitter for each year. homeruns, hits, walks, etc.
3. Fielding - Basic fielding stats. In this table if 1 player played two positions that year there are two records.

All the tables are linked with 1 field called playerID. I am running a query to pull records and do some basic statistical analysis. See a simplified version of the query below. What I want to do is have a field for position where I can see all the positions each player played more than 20 games last year. So in the that field if player X played just 1B all year it would say simply 1B. If he split time between 1st and 3rd it would say 1B 3B. I  and then write it into the one field.
Just not sure how to add to my query to pull that data out and then write it to the field. Any help would be appreciated
$query="SELECT concat(nameFirst, ' ', nameLast) AS name,yearID, yearID-birthyear as AGE, G, AB, H, 2B, 3B, HR, RBI, R, BB, SB, MLB2010.Batting.playerID,FROM MLB2010.Batting, MLB2010.Master WHERE MLB2010.Batting.playerID=MLB2010.Master.playerID AND yearID=2010 ORDER BY totalpoints DESC LIMIT 300";

Open in new window

0
Comment
Question by:akalbfell
  • 17
  • 15
  • 2
34 Comments
 
LVL 2

Expert Comment

by:TimBare
ID: 34900934
how is your table set up? do you have anything in row 2B or 3B if it's not there (a '0' or the like) or is it empty / NULL?

What i'd do is loop through the resulting array and look for positions - if it's there, add 1. then, when you're done, if the count is greater than 20, add it to the array to send from the function (assuming you're using a function to get the information).

Give me a little more background on the code, and I'll see what I can do to help out some more.
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34901091
No, there would just be no record. I did a quick export of 1 player in 2010 to show you how that table is setup. see the screenshot. You can see that each record correlates to that player, in that year, at that position showing how many games he played there.
So for this player I would be looking to extract "1B" and "OF" since he played > 20 games at each position. Then I would be putting that into a field with my results like "1B OF"




example.JPG
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34901371
this is untested as I don't have a mysql db set up here at work, but give this ago.

I would set up a separate function to pull the positions with something like this (note: I always use prepared statements in my mysql queries to prevent injection attacks)

<?php
function fGetPositions($playerID)
	{
	$query = "PREPARE DeleteRandomQuote0 FROM 'SELECT G, POS FROM MLB2010.Batting, MLB2010.Master WHERE MLB2010.Batting.playerID = ? AND yearID=2010 ORDER BY totalpoints DESC LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE DeleteRandomQuote0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$i = 0;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		if($row['G'] >= 20)
			{
			$posArray[$i][0] = $row['POS'];
			$i++;
			}
		}
	return $posArray;
	}

$positionsPlayed =  fGetPositions($playerID);
print_r($positionsPlayed);
?>

Open in new window


Basically, it loops through all players w/ the 'playerID' passed into the function, then, if the 'G' column is greater than or equal to 20, it adds it to the '$posArray' array, and returns $posArray. print_r($positionArray) will simply dump the array, so you can see what data is there, and manipulate it from there. (display each in the cell, etc...)

Let me know if this helps or of you'd like a little more clarification on it, or if it's just plain not what you're looking for..,
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34901618
Thanks. I'll have to read through this to see how to implement it with what i currently have which I attached.

<html>
<body>
<?php
$username="xxxxxxxxx";
$password="xxxxxxxxxxxxx";
$database="MLB2010";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="$query=SELECT concat(nameFirst, ' ', nameLast) AS name,yearID, yearID-birthyear as AGE, G, AB, H, 2B, 3B, HR, RBI, R, BB, SB, MLB2010.Batting.playerID,FROM MLB2010.Batting, MLB2010.Master WHERE MLB2010.Batting.playerID=MLB2010.Master.playerID AND yearID=2010 ORDER BY HR DESC LIMIT 300";

$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();
?>
<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Year</font></th>
<th><font face="Arial, Helvetica, sans-serif">Age</font></th>
<th><font face="Arial, Helvetica, sans-serif">G</font></th>
<th><font face="Arial, Helvetica, sans-serif">AB</font></th>
<th><font face="Arial, Helvetica, sans-serif">H</font></th>
<th><font face="Arial, Helvetica, sans-serif">2B</font></th>
<th><font face="Arial, Helvetica, sans-serif">3B</font></th>
<th><font face="Arial, Helvetica, sans-serif">HR</font></th>
<th><font face="Arial, Helvetica, sans-serif">RBI</font></th>
<th><font face="Arial, Helvetica, sans-serif">R</font></th>
<th><font face="Arial, Helvetica, sans-serif">BB</font></th>
<th><font face="Arial, Helvetica, sans-serif">SB</font></th>
<th><font face="Arial, Helvetica, sans-serif">PlayerID</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {
$f1=$i+1;
$f2=mysql_result($result,$i,"name");
$f3=mysql_result($result,$i,"yearID");
$f4=mysql_result($result,$i,"AGE");
$f5=mysql_result($result,$i,"G");
$f6=mysql_result($result,$i,"AB");
$f7=mysql_result($result,$i,"H");
$f8=mysql_result($result,$i,"2B");
$f0=mysql_result($result,$i,"3B");
$f10=mysql_result($result,$i,"HR");
$f11=mysql_result($result,$i,"RBI");
$f12=mysql_result($result,$i,"R");
$f13=mysql_result($result,$i,"BB");
$f14=mysql_result($result,$i,"SB");
$f15=mysql_result($result,$i,"playerID");

?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f9; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f10; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f12; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f13; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f14; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f15; ?></font></td>
</tr>

<?php
$i++;
}
?>
</body>
</html>

Open in new window

0
 
LVL 2

Expert Comment

by:TimBare
ID: 34901654
what is the
$f8=mysql_result($result,$i,"2B");
$f0=mysql_result($result,$i,"3B");

Open in new window

? are they positions?
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34901703
should be f8 and f9, typo. had to scale a lot of this code down to make it more simple and understandable because normally it has a lot more calculations.

No those represent doubles and triples. none of that code implements anything related to the fielding table that has the info I am trying to extract. These are all just hitting stats.

Just to sum up what I am trying to do, i want to show say the top 50 homerun hitters from 2010 and I also want to show what position they played that year. The complication comes that some guys played more than 1 position and I want to see that as well.
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34901864
gotcha... put the everything (except the print_r) i provided before your "mysql_close();"

then you can use $positionsPlayed below.

better yet, move yoru mysql_close(); to the bottom of the page, put the the function only at the top of the page (below your '$num = ..'

then you can use:

$positionsPlayed =  fGetPositions($f15); (using the id that you've got pulled....)

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34901866
"the top 50 homerun hitters from 2010 and I also want to show what position they played that year. The complication comes that some guys played more than 1 position and I want to see that as well. "

The first part of that would be to SELECT playerID ORDER BY homeruns DESC LIMIT 50.  That will give you the playerID set that you need.

The second part of that might be to iterate over the results set from the first query and SELECT playerID, POS, G WHERE G > 20 and playerID = current player id.

Then you can iterate over that results set with something like this:
$positions = NULL;
while ($row = mysql_fetch_assoc($res))
{
    $positions .= $row["POS"] . ' ';
}

So for your jsmith1 example, the $positions variable would contain 1B OF
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34902497
Sorry Tim you lost me a little bit there.
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34902584
change the top to:
<?php
$username="xxxxxxxxx";
$password="xxxxxxxxxxxxx";
$database="MLB2010";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="$query=SELECT concat(nameFirst, ' ', nameLast) AS name,yearID, yearID-birthyear as AGE, G, AB, H, 2B, 3B, HR, RBI, R, BB, SB, MLB2010.Batting.playerID,FROM MLB2010.Batting, MLB2010.Master WHERE MLB2010.Batting.playerID=MLB2010.Master.playerID AND yearID=2010 ORDER BY HR DESC LIMIT 300";

$result=mysql_query($query);

$num=mysql_num_rows($result);

function fGetPositions($playerID)
	{
	$query = "PREPARE DeleteRandomQuote0 FROM 'SELECT G, POS FROM MLB2010.Batting, MLB2010.Master WHERE WHERE G > 19 AND MLB2010.Batting.playerID = ? AND yearID=2010 ORDER BY totalpoints DESC LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE DeleteRandomQuote0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$i = 0;
	$positions = NULL;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		$positions .= $row['POS'];
		}
	return $positions;
	}
?>

Open in new window


then when you're making all of your calls:
--snip--
$f14=mysql_result($result,$i,"SB");
$f15=mysql_result($result,$i,"playerID");

$positionsPlayed =  fGetPositions($f15);
?>

<tr>
--snip--

Open in new window

add a new column for position played, and put in '$positionsPlayed' in that column...

then:
<?php
mysql_close();
?>

Open in new window

at the end of the file...
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34902745
Noticed some syntax errors in what you wrote, for example line 7 in the first part

query="$query=SELECT concat(nameFirst, ' ', nameLast)

I tried to clean it up a little bit but i am still getting an error. Here is the code I am using and the error

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /var/www/stats.php on line 13



<html>
<body>
<?php
$username="xxxxxxxx";
$password="xxxxxxxxx";
$database="MLB2010";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT concat(nameFirst, ' ', nameLast) AS name, yearID, yearID-birthyear as AGE, G, AB, H, 2B, 3B, HR, RBI, R, BB, SB, MLB2010.Batting.playerID,FROM MLB2010.Batting, MLB2010.Master, MLB2010.Fielding WHERE MLB2010.Batting.playerID=MLB2010.Master.playerID AND yearID=2010 ORDER BY HR DESC LIMIT 300";

$result=mysql_query($query);

$num=mysql_num_rows($result);

function fGetPositions($playerID)
	{
	$query = "PREPARE DeleteRandomQuote0 FROM 'SELECT G, POS FROM MLB2010.Batting, MLB2010.Master, MLB2010.Fielding WHERE G > 19 AND MLB2010.Batting.playerID = ? AND yearID=2010 ORDER BY HR DESC LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE DeleteRandomQuote0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$i = 0;
	$positions = NULL;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		$positions .= $row['POS'];
		}
	return $positions;
	}
?>


<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Number</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Year</font></th>
<th><font face="Arial, Helvetica, sans-serif">Age</font></th>
<th><font face="Arial, Helvetica, sans-serif">G</font></th>
<th><font face="Arial, Helvetica, sans-serif">AB</font></th>
<th><font face="Arial, Helvetica, sans-serif">H</font></th>
<th><font face="Arial, Helvetica, sans-serif">2B</font></th>
<th><font face="Arial, Helvetica, sans-serif">3B</font></th>
<th><font face="Arial, Helvetica, sans-serif">HR</font></th>
<th><font face="Arial, Helvetica, sans-serif">RBI</font></th>
<th><font face="Arial, Helvetica, sans-serif">R</font></th>
<th><font face="Arial, Helvetica, sans-serif">BB</font></th>
<th><font face="Arial, Helvetica, sans-serif">SB</font></th>
<th><font face="Arial, Helvetica, sans-serif">PlayerID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Position</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {
$f1=$i+1;
$f2=mysql_result($result,$i,"name");
$f3=mysql_result($result,$i,"yearID");
$f4=mysql_result($result,$i,"AGE");
$f5=mysql_result($result,$i,"G");
$f6=mysql_result($result,$i,"AB");
$f7=mysql_result($result,$i,"H");
$f8=mysql_result($result,$i,"2B");
$f9=mysql_result($result,$i,"3B");
$f10=mysql_result($result,$i,"HR");
$f11=mysql_result($result,$i,"RBI");
$f12=mysql_result($result,$i,"R");
$f13=mysql_result($result,$i,"BB");
$f14=mysql_result($result,$i,"SB");
$f15=mysql_result($result,$i,"playerID");
$positionsPlayed =  fGetPositions($f23)
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f9; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f10; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f12; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f13; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f14; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f15; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $positionsPlayed; ?></font></td>

</tr>

<?php
$i++;
}
?>
<?php
mysql_close();
?>
</body>
</html>

Open in new window

0
 
LVL 2

Expert Comment

by:TimBare
ID: 34902779
That was all yours -- I've never used "mysql_num_rows" -- try $num=count($result);
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34902979
ok fixed that and a few other errors and now everything is running without error and the output is correct. The only problem is it is repeating the position like 200 times in that field. any idea why?

Here is the code again.

 
 
<html>
<body>
<?php
$username="xxxxxxxxx";
$password="xxxxxxx";
$database="MLB2010";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT concat(nameFirst, ' ', nameLast) AS name,yearID, yearID-birthyear as AGE, G, AB, H, 2B, 3B, HR, RBI, R, BB, SB, MLB2010.Batting.playerID FROM MLB2010.Batting, MLB2010.Master WHERE MLB2010.Batting.playerID=MLB2010.Master.playerID AND yearID=2010 AND AB > 275 ORDER BY HR DESC LIMIT 35";

$result=mysql_query($query);

$num=mysql_num_rows($result);

function fGetPositions($playerID)
	{
	$query = "PREPARE DeleteRandomQuote0 FROM 'SELECT Games, POS FROM MLB2010.Master, MLB2010.Fielding WHERE Games > 19 AND MLB2010.Fielding.playerID = ? AND yearID=2010 LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE DeleteRandomQuote0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$i = 0;
	$positions = NULL;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		$positions .= $row['POS'];
		}
	return $positions;
	}
?>


<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Number</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Year</font></th>
<th><font face="Arial, Helvetica, sans-serif">Age</font></th>
<th><font face="Arial, Helvetica, sans-serif">G</font></th>
<th><font face="Arial, Helvetica, sans-serif">AB</font></th>
<th><font face="Arial, Helvetica, sans-serif">H</font></th>
<th><font face="Arial, Helvetica, sans-serif">2B</font></th>
<th><font face="Arial, Helvetica, sans-serif">3B</font></th>
<th><font face="Arial, Helvetica, sans-serif">HR</font></th>
<th><font face="Arial, Helvetica, sans-serif">RBI</font></th>
<th><font face="Arial, Helvetica, sans-serif">R</font></th>
<th><font face="Arial, Helvetica, sans-serif">BB</font></th>
<th><font face="Arial, Helvetica, sans-serif">SB</font></th>
<th><font face="Arial, Helvetica, sans-serif">PlayerID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Position</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {
$f1=$i+1;
$f2=mysql_result($result,$i,"name");
$f3=mysql_result($result,$i,"yearID");
$f4=mysql_result($result,$i,"AGE");
$f5=mysql_result($result,$i,"G");
$f6=mysql_result($result,$i,"AB");
$f7=mysql_result($result,$i,"H");
$f8=mysql_result($result,$i,"2B");
$f9=mysql_result($result,$i,"3B");
$f10=mysql_result($result,$i,"HR");
$f11=mysql_result($result,$i,"RBI");
$f12=mysql_result($result,$i,"R");
$f13=mysql_result($result,$i,"BB");
$f14=mysql_result($result,$i,"SB");
$f15=mysql_result($result,$i,"playerID");
$positionsPlayed =  fGetPositions($f15)
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f9; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f10; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f12; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f13; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f14; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f15; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $positionsPlayed; ?></font></td>

</tr>

<?php
$i++;
}
?>
<?php
mysql_close();
?>
</body>
</html>

Open in new window

0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903045
is that the only field that's repeated?

Something I noticed is the "$i = 0;" on line 21 isn't needed...

I would change "DeleteRandomQuote0" to "GetPositions" or the like...

Can you post a sample of the table so I can see why it'd be repeating? my guess is there's multiple entries for that position w/ the games >= 20... we may have to add another if statement saying that if it's already in the result, don't add it again...
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34903124
Yes that is the only row repeating. The data is correct though.

here is a screenshot screenshot
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903143
i don't know if the "i=0;" on line 21 is the culprit, but try this (changed a couple names, added a space b/w the positions, removed the $i=0; and put the $i++ and mysql_close() in the same php tags at the bottom):

<html>
<body>
<?php
$username="xxxxxxxxx";
$password="xxxxxxx";
$database="MLB2010";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT concat(nameFirst, ' ', nameLast) AS name,yearID, yearID-birthyear as AGE, G, AB, H, 2B, 3B, HR, RBI, R, BB, SB, MLB2010.Batting.playerID FROM MLB2010.Batting, MLB2010.Master WHERE MLB2010.Batting.playerID=MLB2010.Master.playerID AND yearID=2010 AND AB > 275 ORDER BY HR DESC LIMIT 35";

$result=mysql_query($query);

$num=mysql_num_rows($result);

function fGetPositions($playerID)
	{
	$query = "PREPARE GetPositions0 FROM 'SELECT Games, POS FROM MLB2010.Master, MLB2010.Fielding WHERE Games > 19 AND MLB2010.Fielding.playerID = ? AND yearID=2010 LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE GetPositions0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$positions = NULL;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		$positions .= $row['POS'].' ';
		}
	return $positions;
	}
?>


<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Number</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Year</font></th>
<th><font face="Arial, Helvetica, sans-serif">Age</font></th>
<th><font face="Arial, Helvetica, sans-serif">G</font></th>
<th><font face="Arial, Helvetica, sans-serif">AB</font></th>
<th><font face="Arial, Helvetica, sans-serif">H</font></th>
<th><font face="Arial, Helvetica, sans-serif">2B</font></th>
<th><font face="Arial, Helvetica, sans-serif">3B</font></th>
<th><font face="Arial, Helvetica, sans-serif">HR</font></th>
<th><font face="Arial, Helvetica, sans-serif">RBI</font></th>
<th><font face="Arial, Helvetica, sans-serif">R</font></th>
<th><font face="Arial, Helvetica, sans-serif">BB</font></th>
<th><font face="Arial, Helvetica, sans-serif">SB</font></th>
<th><font face="Arial, Helvetica, sans-serif">PlayerID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Position</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {
$f1=$i+1;
$f2=mysql_result($result,$i,"name");
$f3=mysql_result($result,$i,"yearID");
$f4=mysql_result($result,$i,"AGE");
$f5=mysql_result($result,$i,"G");
$f6=mysql_result($result,$i,"AB");
$f7=mysql_result($result,$i,"H");
$f8=mysql_result($result,$i,"2B");
$f9=mysql_result($result,$i,"3B");
$f10=mysql_result($result,$i,"HR");
$f11=mysql_result($result,$i,"RBI");
$f12=mysql_result($result,$i,"R");
$f13=mysql_result($result,$i,"BB");
$f14=mysql_result($result,$i,"SB");
$f15=mysql_result($result,$i,"playerID");
$positionsPlayed =  fGetPositions($f15)
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f9; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f10; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f12; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f13; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f14; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f15; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $positionsPlayed; ?></font></td>

</tr>

<?php
$i++;
}

mysql_close();
?>
</body>
</html>

Open in new window

0
 
LVL 8

Author Comment

by:akalbfell
ID: 34903241
with that it looks like there are a lot less but still a bunch.


Screenshot-1.png
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Expert Comment

by:TimBare
ID: 34903347
i've got it narrowed down a little bit, but still not 100% why it's doing it yet -- we have Limit 300 in our query, and it's repeating 300 times...

try replacing the function with this (added the playerID to the select):
function fGetPositions($playerID)
	{
	$query = "PREPARE GetPositions0 FROM 'SELECT MLB2010.Fielding.playerID, Games, POS FROM MLB2010.Master, MLB2010.Fielding WHERE Games > 19 AND MLB2010.Fielding.playerID = ? AND yearID=2010 LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE GetPositions0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$positions = NULL;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		$positions .= $row['POS'].' ';
		}
	return $positions;
	}

Open in new window

0
 
LVL 8

Author Comment

by:akalbfell
ID: 34903376
same result. you are correct that the results are tied to that LIMIT number.
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903390
What's your DB look like for Jose Bautista? how many rows do you have for him?
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34903403
There are 6 total but only 3 qualify for the over 20 games. OF RF and 3B. Those are the same 3 that show in the results.
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903430
I don't like this as a fix, but it willl make a decent test -- replace the function with this one. basically, it's checking if POS is in an array. if it's not, it adds it to $positions, then adds it to the temp. array. so next time it loops, it should see that it's already in the array, and not add it again. Even if this works, i'll keep looking for a better / cleaner solution, but this should at least get you going a little quicker...

function fGetPositions($playerID)
	{
	$query = "PREPARE GetPositions0 FROM 'SELECT MLB2010.Fielding.playerID, Games, POS FROM MLB2010.Master, MLB2010.Fielding WHERE Games > 19 AND MLB2010.Fielding.playerID = ? AND yearID=2010 LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE GetPositions0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$positions = NULL;
	$tempArray = array();
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		if(!in_array($row['POS'], $tempArray)
			{
			$positions .= $row['POS'].' ';
			$tempArray[$j] = $row['POS'];
			$j++;
			}
		}
	return $positions;
	}

Open in new window

0
 
LVL 2

Accepted Solution

by:
TimBare earned 500 total points
ID: 34903438
SHOOT! I forgot to add $j = 0; between lines 8 and 9 of the code above...

function fGetPositions($playerID)
	{
	$query = "PREPARE GetPositions0 FROM 'SELECT MLB2010.Fielding.playerID, Games, POS FROM MLB2010.Master, MLB2010.Fielding WHERE Games > 19 AND MLB2010.Fielding.playerID = ? AND yearID=2010 LIMIT 300'"; $result = mysql_query($query) or die(mysql_error());
	$query = "set @a = '" . $playerID . "'"; $result = mysql_query($query) or die(mysql_error());
	$query = "EXECUTE GetPositions0 USING @a"; $result = mysql_query($query) or die(mysql_error());
	$result = mysql_query($query) or die(mysql_error());
	$positions = NULL;
	$tempArray = array();
	$j = 0;
	while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
		{
		if(!in_array($row['POS'], $tempArray)
			{
			$positions .= $row['POS'].' ';
			$tempArray[$j] = $row['POS'];
			$j++;
			}
		}
	return $positions;
	}

Open in new window

0
 
LVL 8

Author Comment

by:akalbfell
ID: 34903470
Parse error: syntax error, unexpected '{' in /var/www/test.php on line 27

line 27 corresponds to line 11 in your code above. the { right under the while
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903493
forgot a ')' at the end of the if:

		if(!in_array($row['POS'], $tempArray))

Open in new window

my bad
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903498
btw, if this yeilds the same results, it means the function itself is getting called more than once, but the LIMIT deal makes me think that's not the case...
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34903520
WORKING!!!

Thank you so much for all your help. I really appreciate it. Now I just have to try to understand what you did here. Thank you again.
0
 
LVL 8

Author Closing Comment

by:akalbfell
ID: 34903525
Great help. Much appreciated.
0
 
LVL 2

Expert Comment

by:TimBare
ID: 34903545
haha... no problem...

the purpose of the function is for re-usability. by passing the ID that we got from your previous query in your while loop into the function, we're looking for any record for that ID.

the compexity of my function (the prepared statement) is keeping sql injection attacks at bay, but since there's no user input, it's probably overkill, but still good practice.

then, we're taking the result of that query, and cycling through them, and adding to a temp array to prevent the duplication from occurring.

Like I said, i'm not thrilled with that because it does offer the possibility of making the function crash (with a high limit, for example), so I'll keep looking for a better solution, but for tonight, i'm tired, and ready for bed.

Glad I could help, and if I find a better solution, I'll post back here.
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34906367
Would you mind explaining these things to me?

1. PREPARE GetPositions0 FROM - What does this do and why use it instead of just the Select query on its own?

2. In the query you have MLB2010.Fielding.playerID = ?, what is the use of the question mark?

3. What is this doing? "EXECUTE GetPositions0 USING @a"


0
 
LVL 2

Expert Comment

by:TimBare
ID: 34906669
Good questions -- this is the "prepared statement" i was talking about.

1. Prepare GetPositions0 FROM -- This prepares the statement named "GetPositions0" (which could be anything, really...) FROM the query in quotes behind it.

2. MLB2010.Fielding.playerID = ? -- The question mark is what we are passing in using the @a below.

3. EXECUTE GetPositions0 USING @a -- This is actually executing the query named "GetPositions0" (from line 1) using the [ "set @a = '" . $playerID . "'";] that you didn't ask about.

4. "set @a = '" . $playerID . "'"; -- this is setting @a (think variable) to $playerID that we are passing into the function...

Let me know if you need more clarification -- if you want to read up more on prepared statements, you can check our mysql.com's page.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34906857
Regarding this, "OF RF and 3B" -- as a practical matter wouldn't Right Field be a subset of Outfield?

You might enjoy this book -- it will help you get a foundation in PHP and MySQL:
http://www.sitepoint.com/books/phpmysql4/

Good luck with your project, ~Ray
0
 
LVL 8

Author Comment

by:akalbfell
ID: 34906928
Thanks for those explanations Tim.

Ray,
You are exactly right. What they did was kind of strange. If a player played 50 games at RF and 50 games in CF it would have 3 records. 1 for each of those but also a 3rd for OF with a games played of 100. Since I only care that a guy played the outfield and not the particular position I just ran a quick DELETE query to remove all the records with CF, RF or LF in them.

Thanks for that book suggestion. It's been about 5 years since I have done any work with PHP/MYSQL as I am a network engineer/ system admin by trade but I am working on this project as a hobby so anything I can learn is appreciated. I'll definitely check that book out.

0
 
LVL 2

Expert Comment

by:TimBare
ID: 34906967
any time... and as Ray said, good luck with the rest of your project...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now