News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

MySQL, selecting from 2 tables ..

Started by sangwe11, November 23, 2009, 10:40:43 AM

Previous topic - Next topic

Arantor

Why? This way you get the name or 0, single column, dual purpose.

sangwe11

Quote from: Arantor on November 26, 2009, 05:46:20 PM
Why? This way you get the name or 0, single column, dual purpose.

Yes, but it means I'm getting a duplicate name.

I already have it, so I don't need it again.

Array (
  • => Array ( [category] => Main [created] => Main ) [1] => Array ( [category] => Main2 [created] => 0 ) )

Arantor

Just because you get it from the query doesn't mean you have to explicitly shove it in to the array.

sangwe11

Quote from: Arantor on November 26, 2009, 05:50:05 PM
Just because you get it from the query doesn't mean you have to explicitly shove it in to the array.

Its needed though lol :D

The first bit is the name of the category got from page_categories. The second bit should be whether its created or not.

Arantor

You're missing my point.

SELECT IFNULL(c.name, 0) AS category
FROM {db_prefix}wiki_page_categories AS pc
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE pc.page = {text:name}[/quote]

0 if it didn't exist, name if it did.

Faster than the alternative:
[code]SELECT pc.category, IF(c.name == NULL, 1, 0) AS created
FROM {db_prefix}wiki_page_categories AS pc
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE pc.page = {text:name}



Be careful of quoting numbers, can lead to all kinds of trouble with type juggling. Not to mention $smcFunc doesn't like " in queries much.

sangwe11

Doh!

SELECT pc.category, (CASE WHEN c.name IS NULL THEN 0 ELSE 1 END) AS created
FROM {db_prefix}wiki_page_categories AS pc
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE pc.page = {text:name}


That works for MySQL, but would it pass and work with other database backends ?

Quote from: Arantor on November 26, 2009, 06:05:17 PM
You're missing my point.

SELECT IFNULL(c.name, 0) AS category
FROM {db_prefix}wiki_page_categories AS pc
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE pc.page = {text:name}[/quote]

0 if it didn't exist, name if it did.

Faster than the alternative:
[code]SELECT pc.category, IF(c.name == NULL, 1, 0) AS created
FROM {db_prefix}wiki_page_categories AS pc
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE pc.page = {text:name}



Be careful of quoting numbers, can lead to all kinds of trouble with type juggling. Not to mention $smcFunc doesn't like " in queries much.

No, I need the name everytime, not just when it doesn't exist.

Its needed for the category tag parsing later.

Arantor

QuoteNo, I need the name everytime, not just when it doesn't exist.

If it doesn't exist you can't get the name anyway. If it does exist, return the name. That's what my query did.

sangwe11

Quote from: Arantor on November 26, 2009, 06:12:55 PM
QuoteNo, I need the name everytime, not just when it doesn't exist.

If it doesn't exist you can't get the name anyway. If it does exist, return the name. That's what my query did.

The name is gathered from a different table, the page_categories table.

Basically, for each category tag, I check if($page['categories'][$cat]['created']) and if so, remove the tag, and if not, ecoh a link to create it.

Arantor

Why not just avoid populating the array if it doesn't exist? Saves you memory in the long run at marginal or no extra CPU cost.

sangwe11

Quote from: Arantor on November 26, 2009, 06:15:47 PM
Why not just avoid populating the array if it doesn't exist? Saves you memory in the long run at marginal or no extra CPU cost.

Yea, I've done this instead now :D

SELECT pc.category AS name
FROM {db_prefix}wiki_page_categories AS pc
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE pc.page = {text:name}
AND c.name = pc.category

Advertisement: