CODEWITHSUNDEEP
phpmysqldatabase
Julien
Julien

Reputation: 27

mysql php what is the best way to obtain from table B the entries that don't match a given column in table A

The title should be self explanatory but I'll elaborate some more.

I have a table A, called "pages", the basic structure follows:

id | name  | content 
0  | page1 | <long html text 1>
1  | page2 | <long html text 2>
2  | page3 | <long html text 3>
3  | page4 | <long html text 4>
4  | page5 | <long html text 5>
5  | page6 | <long html text 6>

I have a table B, called "menus", the basic structure follows (of course there are much:

id | link
0  | page3
1  | page5

I would like to know what is the best way, resource-wise, to get all the "names" in table A which are not already used in the "link" column in table B (that is to say: page1, page2, page4, page6) and their corresponding "content" I want the first ("names" from A which are not used in "links" from B) to be saved in an array called $name with entries like $name[0] = "page1", $name[1] = "page2" etc, and the "content" in another array called $content with entries like $content[0] = etc

Thanks in advance to all the experts out there!

Upvotes: 0

Views: 70

Answers (4)

Pieter
Pieter

Reputation: 1833

Query

SELECT name, content FROM pages WHERE name NOT IN (SELECT link FROM menus)

PHP (pseudo)

Loop through results {

    $name[] = $row['name'];
    $content[] = $row['content'];

}

Upvotes: 1

Francis Fuerte
Francis Fuerte

Reputation: 254

try a query like this:

select name,content from pages where name not in (select link from menus)

it will list all the items that are not from the menus table..

do the array thing yourself :)

Good luck and happy coding!

Upvotes: 2

Pieter
Pieter

Reputation: 1833

Other option:

Query

SELECT DISTINCT 'name' AS descr, name AS val FROM items WHERE name NOT IN (SELECT link FROM menus) UNION
SELECT DISTINCT 'content' as descr, content as val FROM items WHERE name NOT IN (SELECT link FROM menus)

PHP (pseudo)

Loop through results {

    $array[$row['descr']][] = $row['val'];

}

Upvotes: 0

Sundar
Sundar

Reputation: 4650

//this will fetch the non matched entries

Try using id

SELECT a.id, a.name, a.content, b.link FROM table_A AS a
LEFT JOIN table_B AS b ON a.id=b.id
WHERE b.link IS NULL

Try using name

SELECT a.id, a.name, a.content, b.link FROM table_A AS a
LEFT JOIN table_B AS b ON a.name=b.link
WHERE b.link IS NULL

Upvotes: 0

Related Questions