query help

[Marco van Herwaarden]

Adding those TABLE_PREFIX's in front of the table.fieldname is not wrong, but like Buffy suggested using ...user AS user take away a lot of those prefixes. Also $variable is not cleaned and could lead to SQL injections.

PS You want to select from userfield and try to get the corresponding username, so userfield should be your primary table with a left join on use.

PHP Code:

 $getusers = $db->query_read(" 
    SELECT user.username, userfield.field66 
    FROM " . TABLE_PREFIX . "userfield AS userfield 
    LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid=userfield.userid)
    WHERE userfield.field66 = '" . $vbulletin->db->escape_string($variable) . "' 
"); 

$allusers = array();
while ($user = $db->fetch_array($getusers)) 
{ 
    // code will run for every result returned 
    // $user contains your resultset for each result returned 
    
    // (i think this is what you want?) 
    $allusers[] = $user; 
} 

// $allusers is now an array containing each result 


[Protoman]

wow, thx. this is sorta confusing but I'll see what I can do with it.

I'm basically trying to make a member locator script that generates an array of numbers then returns the usernames of people that have those numbers.
The custom profile field is for a zip code.. which I would pass to a DB table that converts it to lattitude/longitude coordinates. then do a distance calculation based on the coordinates.