populate php array

I know I should be using msqli but I will have to tackle that conversion later.  With that said, I have the following code, but instead, I need it to be an array I can reference like $activityArray['ActivityID'] or $activityArray['UnitOfMeasure'].  How do I create/populate an array like that?

		$activityArray = array();
		$sql = "SELECT ActivityID, Activity, TeamID, UnitOfMeasure
							FROM Activities a
								Left Join UnitsOfMeasure u on u.UOM=a.UOM
							Order By (TeamID=2) desc, Activity
						   ";
		while($res = mysql_fetch_array($sql)) {
		    $activityArray[$res['ActivityID']] = ($res['Activity'];
		}		

Open in new window

LVL 7
UniqueDataAsked:
Who is Participating?
 
gr8gonzoConsultantCommented:
Ah, wait. You are missing a step in your query.

$sql = "SELECT ActivityID, Activity, TeamID, UnitOfMeasure
      FROM Activities a
      Left Join UnitsOfMeasure u on u.UOM=a.UOM
      Order By (TeamID=2) desc, Activity";

$result = mysql_query($sql);
while($activityArray = mysql_fetch_assoc($result)) {
      // Now $activityArray should look like what you're expecting. Example:
      echo $activityArray['ActivityID'];
}

The mysql_query() is what actually queries the database. It stores all of the resulting data in $result. Then the mysql_fetch_assoc() simply loops through the stored data in $result. The mysql_fetch_assoc() does not actually query the data - it only loops through the resulting data. So any records that get added after you do mysql_query() will not show up unless you do another mysql_query() again.
0
 
gr8gonzoConsultantCommented:
Use the associative fetch approach - it will give you what you're looking for automatically:

while($activityArray = mysql_fetch_assoc($sql)) {
      // Now $activityArray should look like what you're expecting. Example:
      echo $activityArray['ActivityID'];
}
0
 
UniqueDataAuthor Commented:
I need to loop through the array several times and I want to make sure each fetch is the same each time (don't get any records that may be added between each fetch). So how would I store the fetch once and reference it in several loops?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
UniqueDataAuthor Commented:
gotcha... thanks for clarifying.
0
 
UniqueDataAuthor Commented:
it seems like the second time I use
          while($activityArray = mysql_fetch_assoc($result)) {

it only loops once.  Does it not know to go back to the beginning of the results each time?  Is there a "moveFirst"?
0
 
UniqueDataAuthor Commented:
ahh.. found it:  

   mysql_data_seek($rstActivities, 0) ;
0
 
gr8gonzoConsultantCommented:
Oh, you mean you're looping through the entire result set multiple times?

That's usually a sign that there's a better way to do what you're doing - you shouldn't really ever have to loop through a result set multiple times. That's going to be a performance hit, so if you want to post some code and a description of what you're doing, we might be able to provide some suggestions on how to avoid that.

However, if you just want to get the job done, then you need to use mysql_data_seek after you finish the loop:

mysql_data_seek($result, 0);

That will send the pointer back to row 0 and you should be able to loop through again.
0
 
UniqueDataAuthor Commented:
I am using this to build a dynamic form.  
I first loop through to create a header row.  Then I loop through a Staff table and within that loop I then loop again through activities to build the columns in each Staff Row.

Part of the code can be found in another open question I have:
   http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28627210.html
0
 
gr8gonzoConsultantCommented:
I would recommend that you simply do one loop and build everything you need inside. For example:

$headerRow = null;

while($activityArray = mysql_fetch_assoc($result)) {

  // Do we need to create the header row?
  if($headerRow == null)
  {
    $headerRow = "<tr>";
    $columnNames = array_keys($activityArray);
    foreach($columnNames as $columnName)
    {
      $headerRow .= "<td>{$columnName}</td>";
    }
    $headerRow .= "</tr>";
  }

  // Your data-processing code can go here...
}

That way, you've looped through the result set (and all of the data) only once, but you've efficiently created your header row once within that same loop. The above is more of a principle than actual usable code for your situation, but just so you get an idea of how it might work.

Any time you have to loop through an array of data more than once, you are adding small amounts of performance problems to your script. It may not seem like much at first, but later on, it can make a big difference. So try to build as efficiently upfront as you can.
0
 
UniqueDataAuthor Commented:
Good point.  It is a little easier to read/follow right now, so I may wait until I get it completely working before implementing your suggestion.  

Unfortunately I am learning all my web development by google searches and as Ray Paseur has mentioned, bad habits can be learned that way.
0
 
UniqueDataAuthor Commented:
can you offer any suggesions on my other open question?
    http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28627210.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.