query help

[Protoman]

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

PHP Code:

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]

PHP Code:

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

PHP Code:

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