Zend Query question.

[Renari]

$query = $db->query("SELECT * FROM `xf_user_field_value`");
while ($userdata = $query->fetch())
{
    $user = $db->fetchRow("SELECT * FROM xf_user WHERE user_id = ?", $userdata['user_id']);
}

The above code, never reaches the second iteration of the while loop, my only understanding for this is that fetchRow is overwriting the query. Does anyone know why this is happening or an alternative way to do this. It's entirely possible that this isn't even my problem (there's more code than this but it shouldn't effect the loop causing it to never reach it's next iteration).

 

[Jake Bunce]

What are you trying to accomplish with that code? There is probably a better way.

 

[Renari]

I'm trying to get the value from a custom field in the xf_user_field_value table as well as all the user data belonging to said user (e.g. username).

 

[Chris D]

Are you trying to find all field values for a user?

$db->fetchAll('
	SELECT
		value.*,
		user.*
	FROM xf_user AS user
	INNER JOIN xf_user_field_value AS value ON
		(user.user_id = value.user_id)
	WHERE user.user_id = ?
', $userId);

You may want to join the field and value tables also (you might not need to)

$db->fetchAll('
	SELECT
		value.*,
		user.*,
		field.*
	FROM xf_user AS user
	INNER JOIN xf_user_field_value AS value ON
		(user.user_id = value.user_id)
	INNER JOIN xf_user_field AS field ON
		(field.field_id= value.field_id)
	WHERE user.user_id = ?
', $userId);

 

[Jake Bunce]

You could just do a join to get all information in one query:

$user = $db->fetchRow("
	SELECT *
	FROM xf_user_field_value AS ufv
	LEFT JOIN xf_user AS u ON (u.user_id = ufv.user_id)
	WHERE ufv.field_id = ?
	AND ufv.user_id = ?
", array('field_id', 1));

 

[Renari]

Thanks, I didn't know you could join tables like that.

SELECT xf_user.user_id, username, field_value FROM xf_user, xf_user_field_value WHERE xf_user.user_id = xf_user_field_value.user_id AND field_id = "{desired_field}"