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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.