mysql query help! (kinda tricky)

[cruz610]

I am trying to build a query that will select values from one table, and compare them to values in another table to see if they exist or not. Here is my code thus far:

$startselect = mysql_query("SELECT userid, username FROM bb1_users ORDER BY userid ASC");
while ($select = mysql_fetch_array($startselect)) {
$userid = $select[userid];
$check = mysql_query("SELECT starterid, COUNT(starterid) AS count FROM bb1_threads WHERE starterid=$userid AND boardid IN (4, 6, 7, 9, 36, 37, 38, 40, 49, 60, 12, 13, 40, 41, 42, 58, 59, 64) GROUP BY starterid");
eval ("\$nopubscanbit .= \" ".$tpl->get("nopubscanbit")."\";");
}
eval("\$tpl->output(\"".$tpl->get("nopubscan")."\");");

This is going to be used for a bulletin board, thus the tpl output stuff. What I want it to be doing is taking the userid, and then, using the $check query, compare it to see if the user has started any threads in that forum (the boardid IN part). It then outputs a tpl file which prints the data all nice and pretty. Only problem is, I dunno if the queries are correctly nested/constructed because while it outputs 800+ tables, there are no values in them 🙁 So i know it gets to the first and second eval statements. Can anyone help me out with this one? Thanks! 🙂

[ultraslacker]

I am trying to build a query that will select values from one table, and compare them to values in another table to see if they exist or not.

What you want is a JOIN as this is the proper way to handle relations.

[tsinka]

Hi,

where do you fetch the results of the second query ?

[ultraslacker]

SELECT userid, username, count(userid) AS user_count
FROM bb1_users, bb1_threads WHERE
starterid = userid AND boardid IN (4, 6, 7, 9, 36, 37, 38, 40, 49, 60, 12, 13, 40, 41)
GROUP by userid, username
ORDER BY userid

count is a reserved word, avoid it as an alias.

avoid nested queries.

[cruz610]

i actually fixed this one myself 😃
thanks for all the help from everyone, here is what i did:

$startselect =  mysql_query("SELECT userid, username FROM bb1_users ORDER BY userid ASC");
while ($select = mysql_fetch_array($startselect)) {
$checkselect =  mysql_query("SELECT starterid, starter, count(starterid) AS user_count FROM bb1_threads WHERE starterid = $select[userid] AND boardid IN (4, 6, 7, 9, 36, 37, 38, 40, 49, 60, 12, 13, 40, 41, 42, 58, 59, 64) GROUP by starterid ORDER BY starterid ASC");
if (mysql_num_rows($checkselect) == 1) {$count1="posted";}
else {$count1="has not posted";}
eval ("\$nopubscanbit .= \" ".$tpl->get("nopubscanbit")."\";");
}
eval("\$tpl->output(\"".$tpl->get("nopubscan")."\");");