1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Zend Query question.

Discussion in 'XenForo Development Discussions' started by Renari, Dec 12, 2012.

  1. Renari

    Renari Member

    Code:
    $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).
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Renari Member

    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).
     
  4. Chris D

    Chris D XenForo Developer Staff Member

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

    PHP:
    $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)

    PHP:
    $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 likes this.
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Code:
    $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));
    
     
    HWS and Chris D like this.
  6. Renari

    Renari Member

    Thanks, I didn't know you could join tables like that.
    Code:
    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}"
     

Share This Page