query help

[Protoman]

I'm trying to write an SQL query but I really don't know what I'm doing :rolleyes:

SELECT userfield.field66, user.username FROM userfield INNER JOIN Orders ON userfield.userid=user.userid

I want it to do this: (probably in a couple different queries)

take $variable and look for matches in table userfield column field66

then return all matching usernames of members with that userfield value in an array if possible.
(maybe i'll end up moving this to service requests) :ermm:

[Adrian Schneider]

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

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

^maybe?

explain what you want a little more

[Boofo]

This is not a table. ;)

TABLE_PREFIX . "user.username,

[Adrian Schneider]

Indeed. :)

..?

[Boofo]

Yoiu have that in the code you posted among other things that also aren't tables. ;)

[Adrian Schneider]

I was under the impression you have to stick the table name before the fieldname when working with multiple tables?

TABLE_PREFIX . "user.username = vb3_user.username

Maybe I'm missing something obvious. :)

[Boofo]

Nope, only on the tables. ;)

[Boofo]

So your query should actually be:

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

And ALWAYS use tablename AS tablename when you are accessing more than 1 table in a query.

;)

[Adrian Schneider]

that sure gets rid of alot of extra table_prefixes eh :)

apart from that, what does that affect?

[Boofo]

You lost me, what does what affect? ;)

[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.

$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.