create an array from database - use the array to pull record

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lemonfreshmedia
Forum Commoner
Posts: 26
Joined: Fri Dec 02, 2005 7:14 pm

create an array from database - use the array to pull record

Post by lemonfreshmedia »

Hello everyone.
Goal: Create an array from a database, then use that array to look through the database and pull records using properties gathered from the first array accordingly.
Situation:
I am creating a css representation of a football players, baseball player, hockey players ,lacrosse etc which will put each player in the proper position on the field...

Each 'Sport' is it's own database.

When I call the 'Football' databse, I populate an array using the 'position' column.
It figures out how many positions there are in the 'Football' database(mine includes all offense, defense and special teams, which turns out to be alot of positions......)
I sort the array by using 'asort'.
Now I have the following details from my database in an array:

Code: Select all

$retrieved_positions[0] = "Tackle";
.........
$retrieved_positions[20] = "Kicker";
[/color]


I want to create CSS for each position in the following format:

Code: Select all

<div id="Tackle">  [b]lfirst player <br> second player <br>.... etc [/b]</div>
[/color]

I will move each position around as I please(using css) overtop of a football field or graphic of some sort.

I am able to get the first position filled properly but cannot seem to get anything after the first div properly filled.
Here's essentially what im getting with the attached attached code.

All the tackles are inside the proper div

Code: Select all

<div id="Tackle">  johnny <br> james <br> joey <br> </div>
<div id="kicker"></div>.......<div id="quarterback"></div>
!!all other positions/div with proper id are being displayed but are empty.......!!

I'm using a 'while loop' and 'mysql_fetch_assoc' to fill my divs but for some reason it only satisfies the first position in my datbase generated array......
I've tried to explain as best I can, if anyone can help, i'd appreciate it...

Here's the complete code:
I'VE NOTED WHERE I THINK THE PROBLEM IS TOWARD THE BOTTOM OF THE PAGE

Code: Select all

<?php
// EVERYTHING UP TO THIS POINT IS FINE
// GET SPORTS POSITIONS
$retrieved_positions = array();
$sql_position_grabber = "SELECT *  FROM  {$_GET['league_table']} GROUP BY position" ;
$queryResource2 = mysql_query($sql_position_grabber, $dbConn);

$i=0;
$number_of_positions = mysql_num_rows($queryResource2);  
	  while ($row = mysql_fetch_assoc($queryResource2)) {
	
			 $retrieved_positions[$i] = $row['position'];
   			 $i++;
   		
 		}
//SORT THE POSITIONS
asort($retrieved_positions);



//TRY AND FILL THE DIV'S NOW
$table = " FROM  {$_GET['league_table']}";

$teamexplode  = $_GET['team'];
		$remove_the_hyphen = " ";
		$team = trim(ereg_replace('-', $remove_the_hyphen, $teamexplode ));
		echo $team;
		echo "<br><br>";
		$where = " WHERE team like '$team' AND position !='' ORDER BY position";


$sql = "SELECT * " . $table . $where;

$queryResource = mysql_query($sql, $dbConn);
 //CREATE THE SPORTS FIELD USING MY CSS
echo "<div id=\"";
echo $_GET['league_db'];
echo "_depth\">";

	for ($i = 1; $i <= $number_of_positions; $i++) {
 		$j=$i;
 		echo $j;
		//START POSITION DIV
 	 	echo "<div id=\"". $retrieved_positions[$i]."\">";
	  //*********** HERE'S WHERE I THINK THE PROBLEM IS , WHY DOES IT ONLY FILL THE FIRST POSITION?
	  	while ($row = mysql_fetch_assoc($queryResource)) {
	
			if ($row['position'] == $retrieved_positions[$j]){echo $row['player'];
			echo ",";
			echo $row['position'];
			echo "<br>";
			}
	
		}

		echo "</div>";


   }
   echo "</div>";
?>
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Post by chris12295 »

It is because you can only traverse a result set once and then you have to "rewind". Put this in front of the while loop to reset the pointer back to the first row.

Code: Select all

mysql_data_seek($queryResource, 0);
Post Reply