The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
|||
|
|||
Driving Me Crazy (MYQL Help Please)
I have a table (links_favorites) with two columns (linkid and userid).
I want to write something that counts the number of matches (in linkid) between two different userids. So it something that grabs all the linkids from userid A - and then all the linkids from userid B - and then counts all the places where this intersects. However, I simply can't figure out the best/easiest way to do this. Could someone help please? |
#2
|
|||
|
|||
I don't have your database to test this. So here you can test it.
Code:
<?php $sql="SELECT linkid, userid, FROM links_favorites WHERE MATCH (linkid,userid)"; $result = mysql_query($sql); $resultsnumber = mysql_numrows($result); echo "$resultsnumber"; ?> |
#3
|
|||
|
|||
Problem with this is that I need to be able to specify the userids ...
IE: there are 4 matches in LINKID between userid-A and userid-B Where would I specify those in this example? --------------- Added [DATE]1208335849[/DATE] at [TIME]1208335849[/TIME] --------------- Code:
$sql="SELECT linkid, userid FROM links_favorites WHERE userid=1083"; $result = mysql_query($sql); $number1 = mysql_fetch_array($result); $sql2="SELECT linkid, userid FROM links_favorites WHERE userid=1"; $result2 = mysql_query($sql2); $number2 = mysql_fetch_array($result2); $result3 = array_intersect($number1, $number2); $joined = count($result3); echo $joined; --------------- Added [DATE]1208335899[/DATE] at [TIME]1208335899[/TIME] --------------- Code:
$sql="SELECT linkid FROM links_favorites WHERE userid=1083"; $result = mysql_query($sql); $number1 = mysql_fetch_array($result); $myids=implode(",", $number1); $sql2="SELECT COUNT(linkid) as count FROM links_favorites WHERE userid=1 AND linkid IN (".$myids.")"; $result2 = mysql_query($sql2); $number2 = mysql_fetch_array($result2); --------------- Added [DATE]1208337252[/DATE] at [TIME]1208337252[/TIME] --------------- Code:
$fandom = $vbulletin->db->query_read("SELECT * FROM links_favorites AS links_favorites LEFT JOIN links_favorites AS favs2 ON (links_favorites.linkid=favs2.linkid) WHERE links_favorites.userid=".$vbulletin->userinfo['userid']." AND favs2.userid=".$friend[userid]." "); $joined= $db->num_rows($fandom); |
#4
|
|||
|
|||
PHP Code:
|
#5
|
|||
|
|||
If I am understanding your question correctly and you want to know how many links User 1 and User 2 share, then you could use this query:
[SQL]SELECT COUNT(DISTINCT t1.linkid) AS shared_links FROM link_favorites t1 INNER JOIN link_favorites t2 ON t1.linkid = t2.linkid WHERE t1.userid = 1 AND t2.userid = 2[/SQL] Your inclination to use a self-join in your example is pretty close to the mark, except that you could get duplicates and you might as well let MySQL handle the counting. Also, a left join would return all of user 1's links, whether they matched or not. |
#6
|
||||
|
||||
or you could use a nested query:
[sql] select count(distinct linkid) from link_favourites where userid=1 and linkid in (select distinct linkid from link_favourites where userid=2) [/sql] |
#7
|
|||
|
|||
Thank you.
|
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|