By icecreamyou on

I'm trying to get an entire array from the database because I want to use it with the array_diff and the array_intersect functions. AFAIK the only options are db_fetch_array and db_fetch object, but those only return the current row. I wrote a little process to use db_fetch_array to build a new, whole array, but I don't think it's working. The code below is an example.

$result = db_query("SELECT uid, name FROM {users}");
$i = 0;
$new_array = array();
//assigns the actual values into an array
while($value = db_fetch_array($result)) {
  $new_array[$i] = $value;
  $i++;
}

Any ideas?

⋅ Categories: Drupal 5.x

Comments

drupalfantwo’s picture

drupalfantwo commented 
<?php
	$a=array();

	for($n=1;$n<4;$n++){

		$b  =array(1,2,3,4);
		$a[]=$b;

	}//$n

	echo '<pre>';
	var_dump($a);
	echo '</pre>';
?>
icecreamyou’s picture

icecreamyou commented

Thanks, but that doesn't help me. I'm trying to turn a database resource ID into an array, and your code assumes I already know what the array is.

styro’s picture

styro commented

eg:

<?php
$result = db_query("SELECT uid, name FROM {users}");
$new_array = array();
//assigns the actual values into an array
while($value = db_fetch_array($result)) {
  $new_array[] = $value;
}
?>

--
Anton
New to Drupal? | Troubleshooting FAQ
Example knowledge base built with Drupal

drupalfantwo’s picture

drupalfantwo commented

thanks :)

icecreamyou’s picture

icecreamyou commented

I'd actually tried that. It either didn't work, or I was testing my results the wrong way.

Here's my entire script, if you don't mind taking a look; I'm building a block to display Facebook-style "Users you might know" using User Relationships.

global $user;
$cur_user = $user->uid;
$user_rel = db_query("
SELECT u.name, u.uid, COUNT(ur.requester_id OR ur.requestee_id) as count
FROM {users} AS u
LEFT JOIN {user_relationships} AS ur ON (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
WHERE (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
  AND (ur.requester_id = $cur_user OR ur.requestee_id = $cur_user)
  AND (ur.approved = 1)
  AND (ur.rtid = 2)
GROUP BY (u.name)
ORDER BY RAND() DESC
"); //rtid = 2 indicates the "Friend" relationship on my setup

//this is what I was just asking about
$user_rel_com = array();
mysql_data_seek($user_rel, 0);
//assigns the actual values into an array
while($value = db_fetch_array($user_rel)) {
  $user_rel_com[] = $value;
}



echo "<strong>Users you might know:</strong>";

$i = 0;
while($row = db_fetch_array($user_rel)){
  if ($i = 0) {
    $req_req_id_list .= "ur.requester_id = " . $row['uid'] . "OR ur.requestee_id = " . $row['uid'];
  } else {
    $req_req_id_list .= " OR ur.requester_id = " . $row['uid'] . "OR ur.requestee_id = " . $row['uid'];
  }
  $i++;
}

$user_rel_rel = db_query("
SELECT u.name, u.uid, COUNT(ur.requester_id OR ur.requestee_id) as count
FROM {users} AS u
LEFT JOIN {user_relationships} AS ur ON (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
WHERE (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
  AND ($req_req_id_list)
  AND (ur.approved = 1)
  AND (ur.rtid = 2)
GROUP BY (u.name)
ORDER BY count DESC
");

$user_rel_rel_com = array();
mysql_data_seek($user_rel_rel, 0);
//assigns the actual values into an array
while($value = db_fetch_array($user_rel_rel)) {
  $user_rel_rel_com[] = $value;
}
$remaining = array_diff($user_rel_rel_com,$user_rel_com);

echo "<ul>";
foreach ($remaining as $value) {
  echo "<li><a href=\"http://www.babelup.com/user/" . $value['uid'] . "\">" . $value['name'] . "</a></li>";
}
echo "</ul>";
styro’s picture

styro commented

I did try the code from my post in the execute PHP window from the devel module and it produced the correct result.

Does your query (minus the {drupal_table_name} brackets) work at the mysql console? Can you inspect the values of variables as you go to make sure each step is correct? I've never seen any Drupal code need mysql_data_seek() before.

The devel module is good for looking at queries and running test code.

I don't have time to dig through your code, but I can spot lots of no-nos or not quite right things in there.

You are putting PHP variables directly in your SQL - you need to use db_query() with placeholders for the query arguments.

Generally using echo in Drupal isn't a good idea as you could turn up anywhere depending on when it is executed and might not be in the right context.

Ideally you should turn this code into a function somewhere (eg in a module) and have it return the output to the calling code.

Ideally you should use the theme layer for the HTML output instead of directly embedding HTML in the logic. There are theme functions for lists, and API functions for better handling link creation.

You seem to have two loops over the same result - surely the second one won't work?

--
Anton
New to Drupal? | Troubleshooting FAQ
Example knowledge base built with Drupal

icecreamyou’s picture

icecreamyou commented

Thanks for looking at this... I was tired yesterday so I'm not at all surprised that there are some errors.

After some testing, I can confirm that your code does produce the correct array. The problem, or at least the first problem, occurs in the assignment of $req_req_id_list:

$i = 0;
while($row = db_fetch_array($user_rel)){
  if ($i = 0) {
    $req_req_id_list .= "ur.requester_id = " . $row['uid'] . "OR ur.requestee_id = " . $row['uid'];
  } else {
    $req_req_id_list .= " OR ur.requester_id = " . $row['uid'] . "OR ur.requestee_id = " . $row['uid'];
  }
  $i++;
}

$row['uid'] isn't working. I don't know why--if you have any idea that'd be great.

To answer your questions:

The queries produce the correct results at the console.

I'm using mysql_data_seek only because I was researching the question I originally asked (about assigning arrays) and a comment on the PHP documentation for mysql_fetch_array said to use that with array_push().

I'm using PHP variables directly in the SQL because I don't know the syntax if you have more than one variable you need to replace.

This is going in a block, so I know exactly where echo is going. I don't want to write a module yet because I don't want the overhead, although eventually I probably will contribute it.

I don't know anything about theming, but it doesn't matter until I turn it into a module.

The first loop assigns the result into an array, the second builds a sub-query that I can insert into the next db call. The second does indeed appear not to work though--that's probably what I need to fix.

j_ten_man’s picture

j_ten_man commented

How about $row['u.uid']? I don't know if that will work. It seems like it shouldn't make a difference, but I don't know for sure in this case with an array.

icecreamyou’s picture

icecreamyou commented

Alright, I'm almost there. Everything works except that array_diff() is returning an empty array, even though the arrays that it's diff-ing are different. :P

global $user;
$cur_user = $user->uid;
$user_rel = db_query("
SELECT u.name, u.uid, COUNT(ur.requester_id OR ur.requestee_id) as count
FROM {users} AS u
LEFT JOIN {user_relationships} AS ur ON (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
WHERE (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
  AND (ur.requester_id = $cur_user OR ur.requestee_id = $cur_user)
  AND (ur.approved = 1)
  AND (ur.rtid = 2)
GROUP BY (u.name)
ORDER BY RAND() DESC
");

$user_rel_com = array();
mysql_data_seek($user_rel, 0);
//assigns the actual values into an array
while($value = db_fetch_array($user_rel)) {
  $user_rel_com[] = $value;
}


echo "<strong>Users you might know:</strong>";

foreach($user_rel_com as $row){
  $req_req_id_list .= " OR ur.requester_id = " . $row['uid'] . " OR ur.requestee_id = " . $row['uid'];
}
$len = strlen($req_req_id_list);
$req_req_id_list = substr($req_req_id_list, 4, $len);

$user_rel_rel = db_query("
SELECT u.name, u.uid, COUNT(ur.requester_id OR ur.requestee_id) as count
FROM {users} AS u
LEFT JOIN {user_relationships} AS ur ON (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
WHERE (u.uid = ur.requester_id OR u.uid = ur.requestee_id)
  AND ($req_req_id_list)
  AND (ur.approved = 1)
  AND (ur.rtid = 2)
GROUP BY (u.name)
ORDER BY count DESC
");

$user_rel_rel_com = array();
mysql_data_seek($user_rel_rel, 0);
//assigns the actual values into an array
while($value = db_fetch_array($user_rel_rel)) {
  $user_rel_rel_com[] = $value;
}
$remaining = array_diff($user_rel_rel_com,$user_rel_com);  //returns only "Array();"

echo "<ul>";
foreach ($remaining as $value) {
  echo "<li><a href=\"http://www.babelup.com/user/" . $value['uid'] . "\">" . $value['name'] . "</a></li>";
}
echo "</ul>";
icecreamyou’s picture

icecreamyou commented

I just wrote my own process to replace array_diff:

$remaining = array();
foreach($user_rel_rel_com as $value){
  if ( !array_search($value,$user_rel_com) ) {
    $remaining[] = $value;
  }
}

It's working now, I just need to limit the list.

Thanks, all, for your help.