MyBB Community Forums
Fetch and match data from 2 tables? - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Extensions (https://community.mybb.com/forum-201.html)
+--- Forum: Plugins (https://community.mybb.com/forum-73.html)
+---- Forum: Plugin Development (https://community.mybb.com/forum-68.html)
+---- Thread: Fetch and match data from 2 tables? (/thread-171800.html)

Pages: 1 2


Fetch and match data from 2 tables? - marcus123 - 2015-06-11

My PHP sucks big time please help!

I have 2 tables A and B which both contain column called TID

How can I fetch TIDs from column A that are not in the column B?


RE: Fetch and match data from 2 tables? - Yumi - 2015-06-12

(2015-06-11, 02:57 PM)marcus123 Wrote: 2 tables A and B
[...]
column A [...] column B?
Trying to treat tables as columns may be your problem...

Assuming you meant 'table' throughout, there may be a few ways to write the SQL. Perhaps the most obvious is a correlated query:
SELECT tid FROM A WHERE tid NOT IN (SELECT tid FROM B)

If the tid columns are unique, a join could also work:
SELECT A.tid FROM A
LEFT JOIN B ON A.tid=B.tid
WHERE B.tid IS NULL



RE: Fetch and match data from 2 tables? - marcus123 - 2015-06-12

Thanks buddy for reply.

Here is what I mean. I have a table called Ads which has a column called tid right. This TID column stores tids that correspond to forum real TIDs

I would like to fetch all thread tids and then all ads tids compare them and then output Thread tids that are not in the Ads tid column.


$query = $db->query("
        SELECT t.tid
        FROM ".TABLE_PREFIX."threads t
        WHERE t.tid NOT IN (how to fetch ads tid from here)
    ");

When I fetch tids they all show like one long number. How to put a comma in the middle to use it in NOT IN expression

$query2 = $db->query("
        SELECT t.tid
        FROM ".TABLE_PREFIX."threads t
		WHERE t.tid NOT IN (SELECT a.tid
        FROM ".TABLE_PREFIX."ads a)
    ");

Is this OK to use? Looks ugly to me!


RE: Fetch and match data from 2 tables? - Omar G. - 2015-06-12

Well it looks kind of beautiful to me.


RE: Fetch and match data from 2 tables? - Yumi - 2015-06-13

I'm not sure what you mean by "ugly".
Be aware that if you have a lot of threads, that query could be expensive (you'll need to test that), if it's run often.


RE: Fetch and match data from 2 tables? - marcus123 - 2015-06-13

I mean that looks like it's gonna generate a ton of queries! I have like 4000+ threads + as many ads.

How to fetch thread tids separately to only generate one query and then use that value or array in this other query?

Is there a way to call $tids from custom page?


RE: Fetch and match data from 2 tables? - Destroy666 - 2015-06-13

How exactly can one query with a subquery generate ton of queries?


RE: Fetch and match data from 2 tables? - marcus123 - 2015-06-14

D666 do you think it will only generate one query? Since this is a custom page that I will only use one time I don't think it will put too much stress on the script right?


RE: Fetch and match data from 2 tables? - Cedric - 2015-06-17

(2015-06-14, 02:40 PM)marcus123 Wrote: D666 do you think it will only generate one query? Since this is a custom page that I will only use one time I don't think it will put too much stress on the script right?

It doesn't depend on the number of threads. The number of queries depends upon how many times $db->query(); method is executed.

So if you execute it once, only 1 query will be generated.


RE: Fetch and match data from 2 tables? - Omar G. - 2015-06-17

Depending on the query it may be expensive to run it.

But since you are only going to run it once (apparently) just do it and be done with it.