[RESOLVED] arrays from MySQL

clairence

I'm quite confused.

I've got a MySQL table where one column is an array, with data separated by commas. The data in this column is dynamic in nature (user inputs change the length and values there).

I'm sure it's very simple, but after looking at and trying out things like explode, split, and $arraysize 'for' loops, I still don't know what I'm doing.

The following bit of code displays the results shown below it:

$four = @("SELECT * FROM afcteams")
or die('<p>Players from afcteams FOUR '. mysql_error());
while($row = mysql_fetch_array($four)) {
$used = "";
echo '<br> team '. $row['TeamName'];
echo '|| teamID '. $row['TeamID'];
echo '|| players '. $row['Players'] . '||';

$used[] = $row['Players'];
print_r ($used);

team Cleveland Browns|| teamID 4|| players 14,18,17||Array ( [0] => 14,18,17 )
team Denver Broncos|| teamID 5|| players 18,17,16,11||Array ( [0] => 18,17,16,11 )
team Houston Texans|| teamID 6|| players ||Array ( [0] => )

I can see all the data there that should be there. But how to work with it?
What I want to do is split the array and check to see if a specific value exists there.

Sounds simple, but I can't seem to figure it out.

kkobashi

Instead of fetching by array:

$row = mysql_fetch_array($four)

fetch by row:

$row = mysql_fetch_row($four);

More here:
http://us2.php.net/manual/en/function.mysql-fetch-row.php

clairence

I'm afraid I don't see what that does for me.

To clarify,
I'm trying to determine if there are values in the last column of each row, then if there are multiple values split them into an array, then determine if any of those values match the value entered by a user.

intenz

Check out these functions:

http://php.net/serialize
http://php.net/unserialize

kkobashi

clairence wrote:
I'm afraid I don't see what that does for me.

To clarify,
I'm trying to determine if there are values in the last column of each row, then if there are multiple values split them into an array, then determine if any of those values match the value entered by a user.

When I first read your post, I felt uncomfortable about your database schema because all the player numbers were being stored in one column as an array. I wanted to post on it but refrained from doing so.

But here it goes...

I think you need a better database schema design. Why don't you create two tables like so:

Table Team

TeamID Name

0 Cleveland Browns
1 San Francisco 49ers
2 Green Bay Packers
.
.
etc.

Table Players

PlayerNum TeamID FirstName LastName PositionID

4 2 Brett Favre 0
4 1 Andy Lee 1
4 1 Andy Lee 3
8 2 Ryan Longwell 2
9 0 Charlie Frye 1
13 1 Tim Rattay 0
.
.
etc.

Table Position (players can play multiple positions)

Id Position

0 QB
1 K
2 P
3 SP (special teams)
.
.
etc.

This way you get better design than stuffing all the player numbers in one field and being forced to split it up. For example, suppose you wanted to know the name of the player who has the number 8 on the Packers? How about position he plays?

The database design above would allow that kind of flexibility. If you don't design with this in mind, a new set of requirements will come up and you will have to rewrite your entire PHP code over again.

Just thinking ahead.

clairence

I understand what you're saying about database schema. I think you have misinterpreted my ultimate goal.

I hadn't explained the entire setup because I didn't think it was relevant to the question. But here goes:

This is something of a fantasy football site. Each player chooses an afc team and an nfc team each week to win. Each player gets to choose each team only once throughout the season.

I've got five tables.
Players: PlayerID, PlayerName
Weeks: WeekID, Date
AFC Teams: TeamID, TeamName, Players
NFC Teams: TeamID, TeamName, Players
GameData: EntryID, WeekID, PlayerID, AFCTeam, NFCTeam

After a player selects a team, I add their PlayerID to the Players column of the team they chose.

The next time that player attempts to select teams, they will only have the option of selecting teams they have not yet picked. The entry form is customized based on the date and the player. I determine the date and only allow selections in future weeks. I determine what teams they've picked and eliminate them from the select box in the form.

The problem I'm having (so far) is splitting up the Players column (in AFCTeam and NFCTeam) and determining if the PlayerID exists there.

I fully expect to have much more difficulty but for now I can't seem to get past this bit.

Thanks for the help.

kkobashi

I see... so the players column contains not the nfl players themselves, but rather the people who are playing the fantasy football contest. Thus, the reason why they are associated per team (i.e. if a player is in the array it means they chose that team to win). A better symbol would have been contestant to avoid the confusion.

What is inside the $row['players'] column is a comma separated string. It's not an array.

When you did this:
$used[] = $row['Players'];
print_r ($used);

You assigned a string to an array.

Try this:

$playerTokens = explode("," , $row['player']);
$found = false;
for ($i=0; $i < count($playerTokens); $i++)
{
if ($playerTokens[$i] == $playerNumber) //$playerNumber is what you are looking for
{
$found = true;
break;
}

if ($found)
{
     do whatever;
}
else
{
     do whatever;
}

clairence

Thank you for your help, Kerry.
Between your example and some of my old trials, I found a combination that works as desired:

$AFCTeamsLeft = "";
$four = @("SELECT * FROM afcteams")
or die('<p>Players from afcteams FOUR '. mysql_error());
while($row = mysql_fetch_array($four)) {
$TeamTest = explode (",", $row['Players']);
$arraysize = count ($TeamTest);

$found = false;
for ($i=0; $i<$arraysize; $i++) {
$TeamTestValue = $TeamTest[$i];

if ($TeamTest[$i] == $pla)
{
$found = true;
break;
}
}
if (!$found) //if not found, add this team to the SELECT list
{
$AFCTeamsLeft = $AFCTeamsLeft . ',' . $row['TeamName'];
}
}

$afcchars = preg_split('/,/', $AFCTeamsLeft, -1, PREG_SPLIT_NO_EMPTY);

I can then run this in my form, which also seems to work as desired:

<select name="AFC">
<?php
for ($i=0; $i< count($afcchars);$i++) {
echo '<option value=\"$afcchars[$i]\">' .$afcchars[$i];
}
?>
</select>

Thanks again!

kkobashi

Excellent... glad I could be of assistance.