# Query

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";``
TimBare

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

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
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);
?>``````

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

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
\$database="MLB2010";
@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();
?>
<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>``````
what is the
``````\$f8=mysql_result(\$result,\$i,"2B");
\$f0=mysql_result(\$result,\$i,"3B");``````
? are they positions?

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

"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

Sorry Tim you lost me a little bit there.
change the top to:
``````<?php
\$database="MLB2010";
@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;
}
?>``````

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--``````
add a new column for position played, and put in '\$positionsPlayed' in that column...

then:
``````<?php
mysql_close();
?>``````
at the end of the file...

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
\$database="MLB2010";
@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;
}
?>

<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>``````
That was all yours -- I've never used "mysql_num_rows" -- try \$num=count(\$result);

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
\$database="MLB2010";
@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;
}
?>

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

Yes that is the only row repeating. The data is correct though.

here is a screenshot
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
\$database="MLB2010";
@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;
}
?>

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

with that it looks like there are a lot less but still a bunch.

Screenshot-1.png
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;
}``````

same result. you are correct that the results are tied to that LIMIT number.
What's your DB look like for Jose Bautista? how many rows do you have for him?

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.
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;
}``````
TimBare

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

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
forgot a ')' at the end of the if:

``		if(!in_array(\$row['POS'], \$tempArray))``
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...

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.

Great help. Much appreciated.
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.

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"

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