Page 1 of 1

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

Posted: Wed Nov 29, 2006 12:22 pm
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>";
?>

Posted: Mon Dec 04, 2006 5:38 pm
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);