mysql_fetch_array

fnbcprog

I am having a little bit of a problem with the mysql_fetch_array function.

What I am trying to do is retrieve the user id (id_users) and username (username) from a table.

Based on that data I need to select some data from another table with the the id user (id_users) number. I can only do that once because it only searches the table based upon the first user number.

// Connection assumed present
// id_household = 1000001

$getUsers = "SELECT id_users,username FROM users 
                      WHERE id_household = '$id_household'";
$users = mysql_query($getUsers);
$num_users = mysql_num_rows($users);
$row_users = mysql_fetch_array($users);

That code above will return 2 records (as of now).
id_users = 1000002 ..... username = bharris
id_users = 1000003 ..... username = sfielden

I need to take those values and setup them equal to separate variables or store them in an array (id_users and usernames).

Next I need to connect to a different table - accounts. With each of those id numbers return an account number (acctnum)

If I did a separate search with each they would return 1 account (as of now). And set an array with the username and related account numbers.

Maybe using a multidimensional array would make sense:
[id_users][username][accounts]

I hope you can help????

ahundiak

There is a couple of approaches but you could try:


$users = null;
$users_ids = null;

$user_set = mysql_query(
"SELECT id_users,username FROM users 
WHERE id_household = '$id_household'");

while($user_rec = mysql_fetch_array($user_set))
{
  /* Grab the unique id */
  $user_id = $user_rec['id_users'];

  /* Build indexed array of users */
  $users[$user_id] = $user_rec;

  /* Build list of ids */
  if (!$user_ids) $user_ids  =      $user_id;
  else              $user_ids .= ',' . $user_id;

}

/* Now query for related data */
$account_set = mysql_query(
  "Select * from account where id_users IN ($user_ids)");

while($account_rec = mysql_fetch_array($account_set))
{
  /* Get the id again */
  $user_id = $account_rec['id_users'];

  /* And add an entry for each account */
  $users[$user_id]['accounts'][] = $account_rec;
}

fnbcprog

Can you explain the structure of the array. I am very very new to arrays and dont understand how to retrieve values or print anything.

ahundiak

I'll give it a try.

Your original design requires at least two queries to the database. Once to get the basic user information and once to get account information.

The line
$user_rec = mysql_fetch_array($user_set)
Gives you an array containing all of the basic user information. Since we have multiple users we need to store each user record somewhere for later use. We do this by creating a second array called users. We could do something like this

$users[] = $user_rec;

This would append each user record to the end of the users array. However, doing so makes it difficult to find a given entry later on. We would have to cycle through the complete array in order to find a given user record.

Fortunately,
$user_id = $user_rec['id_users'];
provides an unique key for each record. So
$users[$user_id] = $user_rec;
Allows accessing any given user as long as you know their id. I.E.
$user_rec = $users[$user_id];

So the first query simply build up an array of users. From the second query, we add a new array called accounts to each of the user records.

Review the array section in the manual. Then think about how the $users is working. Once you get past the basic ideas then it's really pretty simple.