Page 1 of 1

Joins and whatnot going all wrong

Posted: Fri May 29, 2009 7:21 am
by shiznatix
Ok, I basically have this PHP code that does what I want but I know I can make it into a better query. First, the code:

Code: Select all

$sql = '
    SELECT
        field7, userid
    FROM
        website_forum.rbf_userfield
';
 
$query = mysql_query($sql) or die(mysql_error());
 
$countries = array();
 
while ($info = mysql_fetch_assoc($query))
{
    $sql = '
        SELECT
            count(id) AS thecount
        FROM
            rb_usermap
        WHERE
            fk_vb_user_id = "'.$info['userid'].'"
        AND
            status = "1"
    ';
    
    $query2 = mysql_query($sql) or die(mysql_error());
    $info2 = mysql_fetch_assoc($query2);
    
    if (!array_key_exists($info['field7'], $countries))
    {
        $countries[$info['field7']] = 0;
    }
    
    $countries[$info['field7']] += $info2['thecount'];
}
dump($countries);
Basically rbf_userfield has the country that each user is located i (userid = the users user id, field7 = country name). rb_usermap holds all the deals they have with us, any user can have as many or as few as they want, I just want the ones with a status of "1". Now this script works just perfect, no problems but I know that this can be done in SQL but I can't get the code correct. Here is what I have tried but I always get a count of "11" returned for the USA when, using my script, the number is actually "6571". My attempted sql (just for the americans):

Code: Select all

SELECT
    count(um.id)
FROM
    website_site.rb_usermap AS um
LEFT JOIN
    website_forum.rbf_userfield AS uf
ON
    um.fk_vb_user_id = uf.userid
WHERE
    um.STATUS = 1
AND
    uf.field7 = 'United States'
but, like I said, this is returning "11" and I can't figure this one out.

Re: Joins and whatnot going all wrong

Posted: Fri May 29, 2009 9:05 am
by VladSun
[sql]SELECT        somedbnamehere.myconfusingname3here.field7 AS myconfusingname1here,        count(myconfusingname4here.id) AS myconfusingname2here    FROM        somedbnamehere.rbf_userfield AS myconfusingname3here    LEFT JOIN            somedbnamehere.rb_usermap AS myconfusingname4here ON                myconfusingname4here.fk_vb_user_id = myconfusingname3here.userid                AND                myconfusingname4here.`STATUS`= "1"GROUP BY    myconfusingname3here.field7[/sql]

:?
VladSun wrote:Why, even professional ones, IT guys use one letter table names aliases in their SQL queries. And it's worse - the more complicated a SQL query is, the more people tend to use aliases. It's like using a one-letter-named variables in programming language, but it seems that while nearly nobody do this in his source code, a lot will do it in SQL...
I really can't understand it