MySQL, selecting from 2 tables ..

[sangwe11]

I have to ways to do what I want, but I'm curious to know which would be more efficient, and cause less load.

The first way is to do 2 seperate querys, one to the main page table, and one to the revisions table.

Code Select Expand

<?PHP

$result = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}wiki_pages
WHERE name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

// Grab the page array from the database query result.
$page = $smcFunc['db_fetch_assoc']($result);

$result = $smcFunc['db_query']('', '
SELECT *
FROM  {db_prefix}wiki_revisions
WHERE name = {text:name}
AND date = {text:rev}
LIMIT 1',
array(
'name' => $page['name'],
'rev' => $page['current_revision'],
)
);

// Grab the revision array from the database query result.
$revision = $smcFunc['db_fetch_assoc']($result);

// Merge the two arrays
$page = array_merge($page, $revision);

?>

The second way is to do one query, that joins theses two tables, when selecting the row.

Code Select Expand

<?PHP

$result = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}wiki_pages, {db_prefix}wiki_revisions
WHERE {db_prefix}wiki_pages.name = {text:name}
AND {db_prefix}wiki_revisions.name = {text:name}
AND ({db_prefix}wiki_revisions.date = {db_prefix}wiki_pages.current_revision)
LIMIT 1',
array(
'name' => $pagename,
)
);

?>

Which way would be more efficient, and more code friendly (for the latter, I think the second method is)

Any comments / suggestions / feedback please

[Arantor]

First up, you REALLY should also use a JOIN clause in the latter, because this forces it to do multiple comparisons when it needn't.

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r ON (p.name = r.name AND p.current_revision = r.date)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

Tell you what though, this is a bad, bad idea. As a general rule, NEVER join tables on textual ids. The performance cost of doing this is incredible.

Suggestion: create a single id, numeric auto_increment. When you insert a new row you will automatically increment that - kind of like id_msg. Then update that in any other tables. Notionally you'd have the new column in wiki_pages and wiki_revisions would hold the page's title and simply refer to the current revision.

Let me explain how I'd see this happening, with also the index structures in play:

wiki_revisions
id_master, page_id, page_content
id_master = primary key, page_id = non-unique indexed

wiki_pages
id_page, revision_id, page_title
id_page = primary key (hmm, not sure about a secondary index, would benchmark that with EXPLAIN)

id_master and id_page are both auto increment.

So, the first page:

id_master	page_id	page_content
1	1	some stuff

id_page	revision_id	page_title
1	1	Some Page

Now we edit Some Page's content.

id_master	page_id	page_content
1	1	some stuff
2	1	some more stuff

id_page	revision_id	page_title
1	2	Some Page

Now we edit it some more.

id_master	page_id	page_content
1	1	some stuff
2	1	some more stuff
3	1	even some more stuff

id_page	revision_id	page_title
1	3	Some Page

Now we add a new page.

id_master	page_id	page_content
1	1	some stuff
2	1	some more stuff
3	1	even some more stuff
4	2	another page

id_page	revision_id	page_title
1	3	Some Page
2	4	Some Page

So as you see, you query the pages (second in the above examples) table to get the title, which will give you not only the immediate id of the latest revision, but also gives you a numeric id to group the results upon (again faster than using not) as well as selecting them later.

[sangwe11]

Ahh I see, I did think there would be a performance cost, however I didn't know that it was because I was using textual ids to join.

I'll rewrite the query, based on your post.

Anyway, if its done the way you said, which out of the two ways would be more efficient ? (2 queries or the proper join method)

[Arantor]

If it's against a textual id, difference is kind of moot; because the hurt of text ids is enough of a killer it almost doesn't matter.

Joining on properly indexed numeric ids... JOIN pretty much every time. In reality you'd get a decent enough data set and benchmark the two queries, but I'd be using that to figure out if we needed another index in there rather than changing the methodology.

[sangwe11]

Right, so this is what I have ..

Table smf_wiki_pages:

id            mediumint( 8 )
name         varchar( 80 )
current_revision   mediumint( 8 )
is_locked         tinyint( 1 )
topic_id         mediumint( 8 )

Table smf_wiki_revisions:

id         mediumint( 8 )
page       mediumint( 8 )
date         timestamp
member      varchar( 80 ) # this isn't a member id, because we only need the username - saves a db query
content      text
approved      tinyint( 1 )




And then the query code.

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT *
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

[Arantor]

Well, the structure looks about right. Note that all your numeric columns should be unsigned, and you should never be selecting * when you don't actually need all the columns. I won't even get into the issues of reusing 'id' when combining tables.

The point of username vs user id is interesting. Your method is probably slower than an INNER JOIN to the members table (which enforces there being a match on the other side)

Best compromise I can suggest: have both. You add 3 bytes per row to include the id_member (it's a mediumint( unsigned), LEFT JOIN the members table, and IFNULL check:

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT p.id AS id_page, otherfield, otherfield, field, IFNULL(mem.real_name, r.member) AS member_name
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.id_member = mem.id_member)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

[sangwe11]

Well, the structure looks about right. Note that all your numeric columns should be unsigned, and you should never be selecting * when you don't actually need all the columns. I won't even get into the issues of reusing 'id' when combining tables.

The point of username vs user id is interesting. Your method is probably slower than an INNER JOIN to the members table (which enforces there being a match on the other side)

Best compromise I can suggest: have both. You add 3 bytes per row to include the id_member (it's a mediumint( unsigned), LEFT JOIN the members table, and IFNULL check:

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT p.id AS id_page, otherfield, otherfield, field, IFNULL(mem.real_name, r.member) AS member_name
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.id_member = mem.id_member)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

What happens when a guest edits it though ?

As when this happens, I have it entering the IP address of the guest.

[Arantor]

That's why you have the LEFT JOIN with IFNULL, and why I specifically said both. Note this is EXACTLY what SMF itself does, so if you don't have an id_member join, the LEFT JOIN returns NULL for that column from the join itself.

[sangwe11]

That's why you have the LEFT JOIN with IFNULL, and why I specifically said both. Note this is EXACTLY what SMF itself does, so if you don't have an id_member join, the LEFT JOIN returns NULL for that column from the join itself.

Ahh, so if I store all users as a id, and all guests as a ip, and then do the left join with if null, it would work as wanted ?

If so, what would the IF NULL part look like in the query.

[Arantor]

Like the query I already gave you.

[sangwe11]

Like the query I already gave you.

I saw no if null in there though, or is that included in left join ?

I really should research mysql joins a bit more. I know the basics, but not alot else. (first time i've needed them)

[Arantor]

OK, let's repeat the query:

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT p.id AS id_page, otherfield, otherfield, field, IFNULL(mem.real_name, r.member) AS member_name
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.id_member = mem.id_member)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

The IFNULL is in the select since you're selecting it. And you're selecting the field, or if that's null an alternative field.

[sangwe11]

OK, let's repeat the query:

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT p.id AS id_page, otherfield, otherfield, field, IFNULL(mem.real_name, r.member) AS member_name
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.id_member = mem.id_member)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

The IFNULL is in the select since you're selecting it. And you're selecting the field, or if that's null an alternative field.

* sangwe11 face palm

Sorry, didn't notice you'd changed the select part.

[Arantor]

Ah, it's easy to miss...

* Arantor hands out his favourite smiley on his dev site (frequently using this myself)

[sangwe11]

Ah, it's easy to miss...

* Arantor hands out his favourite smiley on his dev site (frequently using this myself)

Added

Woop, working great Arantor!

Just a check, but do we need to add an index to the member column (on the revisions table) ?

[Arantor]

I'm not sure. I'd wait until you have some big data sets, then run the select with EXPLAIN in front of it, that way you can see how MySQL evaluates the query, and using that I can help you figure out how to add smarter indexes.

Book recommendation: High Performance MySQL (2nd ed) by Baron Schwartz (and others)

[sangwe11]

I'm not sure. I'd wait until you have some big data sets, then run the select with EXPLAIN in front of it, that way you can see how MySQL evaluates the query, and using that I can help you figure out how to add smarter indexes.

Book recommendation: High Performance MySQL (2nd ed) by Baron Schwartz (and others)

For big data sets, are we talking 1000's or 10,000's ?

[Arantor]

Hundreds of rows should be enough, though when I say about 'big' I tend to refer to 100,000s or more (when supporting Sphinx, I used to deal with sets into the millions, on multiple occasions, the billions)

[sangwe11]

Hundreds of rows should be enough, though when I say about 'big' I tend to refer to 100,000s or more (when supporting Sphinx, I used to deal with sets into the millions, on multiple occasions, the billions)

* sangwe11 is speechless!

Anywho, I've noticed a problem with doing the db tables the way decided.

When inserting a new page, I have to enter the name, and the current revision id.

I don't know this, until I enter the revision into the revisions table.

Ok you say, do the revisions table part first.

So, I go to insert into the revisions table, but oh no! I need the page id, and I can't get this until I insert the page into the pages table.

Its a vicious loop, and I'm trying to see how (if it does it anything like this) MediaWiki does it, but I figured I ask incase someone new a way also.

[Arantor]

Insert one, insert the other than update the first one with the newly insert id.

[sangwe11]

Insert one, insert the other than update the first one with the newly insert id.

Looks like its gunna take a few queries..

Insert into pages table -> Select page id from pages table -> Insert into revisions table (using newly got page id) ->  Get revision id -> Update pages table ..

[Arantor]

Or...
Insert into pages table with $smcFunc['db_insert']
Get the id with $smcFunc['db_insert_id']
Insert into revisions table using the above-collected id
Update pages table with revisions id

3 queries, 4 if you're using PostgreSQL but you never see it.

[sangwe11]

Or...
Insert into pages table with $smcFunc['db_insert']
Get the id with $smcFunc['db_insert_id']
Insert into revisions table using the above-collected id
Update pages table with revisions id

3 queries, 4 if you're using PostgreSQL but you never see it.

Hmm, never new there was a insert_id function!

Thanks

[Arantor]

It's on the list on http://www.simplemachines.org/community/index.php?topic=224166.0

Ugly how it has to be done for PostgreSQL for earlier versions (below 8.3 if I remember rightly) but still...

[sangwe11]

I did a little optimization on my category tag parsing.

Before, I was selecting the category from the categories table, for each tag and seeing if it existed, and if so, hid the tag, and if it didn't, provide a link to create it.

I noticed this would be VERY bad on pages that have multiple categories (10+), so I moved into using a class, with the preg_replace_callback.

I changed my page selection code, to include the categories that are in the categories table (only the ones the page is in)

The code is as follows.

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT p.id , p.name, p.is_locked, p.topic_id, IFNULL(mem.real_name, r.member) AS member_name, r.content, r.date, GROUP_CONCAT(pc.category) AS categories, GROUP_CONCAT(c.name) AS created_categories
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.member = mem.id_member)
LEFT JOIN {db_prefix}wiki_page_categories AS pc ON (pc.page = p.name)
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE p.name = {text:name}
LIMIT 1',
array(
'name' => $pagename,
)
);

I then explode that column into an array (to make it easier later on)

Code Select Expand

// Explode the two category columns (as we grouped them into a list)
$page['categories'] = explode(',', $page['categories']);
$page['created_categories'] = explode(',', $page['created_categories']);

Now I have that included, to save me queries later, I needed to find a way to give the callback function the array of created cats.

Using global variables wouldn't work, as for every page, 2 pages are parsed (menu + page)

So I took a wild guess, and used a class.

Code Select Expand

// This looks a little complex, but its only like this to save us alot of queries (one per page category!)
$make_cat = new parse_categories();
$make_cat->categories = $page['created_categories'];
$content = preg_replace_callback("~\[category\](.+)\[/category\]~iU", array($make_cat, 'make_category'), $content);

And then the class

Code Select Expand

class parse_categories{

    public $categories;

    public function make_category($matches){
   
    global $scripturl;
    return (in_array($matches[1], $this->categories) ? '' : '<a href="'.$scripturl.'?action=wiki;sa=createcat;p='.$matches[1].'" style="color: #FF0000;">Category: '.$matches[1].'</a>');
   
    }
   
}

I was wondering if people thought it was a better solution, and more efficient, seeing as I don't have a way of testing it (haven't got a large dataset to test it with)

[Arantor]

Interesting solution. Nothing is shouting from the rooftops as being a performance concern.

[sangwe11]

Interesting solution. Nothing is shouting from the rooftops as being a performance concern.

Good good.

It was, as far as I could tell, the most practical (and shortest) solution. I might stumble upon, or someone might suggest a better way, but it works great, and as you said, nothing is standing out as a concern.

Just one last question. I know storing more than one bit of data in a column, isn't really a good idea. In my page select query, I put all the categories, and created categories, into one column (one for categories, one for created_categories), before splitting them into an array.

I see no problem with this, as its not actually being stored with more than one per column, and saves me 2 other queries.

However, I thought it best to ask

[Arantor]

Breaks on non MySQL (meaning we won't approve it, since PostgreSQL doesn't have GROUP_CONCAT built in) and has a performance cost but other than that all good.

[sangwe11]

Breaks on non MySQL (meaning we won't approve it, since PostgreSQL doesn't have GROUP_CONCAT built in) and has a performance cost but other than that all good.

Hmm, well I need some sleep now, so I'll reconsider how to do it tommorrow.

Surely there has to be a way though, as doing another 2 queries seems pointless.

[Arantor]

JOINing to a separate table (since page -> category is by nature a many-many join rather than a one-many) would be cleanest.

[sangwe11]

JOINing to a separate table (since page -> category is by nature a many-many join rather than a one-many) would be cleanest.

Ahh yes, of course!

[sangwe11]

Hmm actually, now I have had a little think, how would this work ?

I'm trying to join the category table into the pages table, getting ONE row from the pages table and joining MANY rows from the category table to it. These then need to be returned in an array, or a list which we can split into an array later.

I've probably overlooked something, but I'm not sure.

[Arantor]

One to many joins is the natural heart of relational databases.

Think of boards->topics->messages: one board holds many topics/one topic lives in one board; one topic holds many messages/one message lives in one topic. That's a classic 1:m relationship. Pushing it into an array is simply a case of while($row = $smcFunc['db_fetch_assoc']($query)) { $rowset[] = $row; }

Depends what you want/need to do with it.

As for many-many, that's where you get into the realms of join tables. I can't give you a good example in SMF directly since SMF's code actually folds the relationship away (in what is IMO one of the only design flaws in the schema)

In your case, actually, let's think about how that'd work.

We have pages p, categories c. One category can hold many pages, one page can belong to many categories.

So what we do is an intermediate table, page_category. All it contains is a series of rows in two columns: page and category ids.

Then when we want the list of categories for a page, SELECT c.cat_id, c.cat_name FROM pages AS p INNER JOIN page_category AS pc ON (p.page_id = pc.page_id) INNER JOIN category AS c ON (pc.cat_id = c.cat_id) WHERE p.page_id = xyz.

Presto, you have a list of category ids and category names that page id xyz belongs to. Getting a list of pages in a category is just the reverse: SELECT p.page_id, p.page_name FROM category AS c INNER JOIN page_category AS pc ON (c.cat_id = pc.cat_id) INNER JOIN pages AS p ON (pc.page_id = p.page_id)


As you can imagine this can quickly get immensely complex; even the most complex queries in SMF never join more than about 7 tables (in some cases joining the same table to the query twice, under different joins, e.g. topic->message, joining against both first and last message ids)

[sangwe11]

One to many joins is the natural heart of relational databases.

Think of boards->topics->messages: one board holds many topics/one topic lives in one board; one topic holds many messages/one message lives in one topic. That's a classic 1:m relationship. Pushing it into an array is simply a case of while($row = $smcFunc['db_fetch_assoc']($query)) { $rowset[] = $row; }

Depends what you want/need to do with it.

As for many-many, that's where you get into the realms of join tables. I can't give you a good example in SMF directly since SMF's code actually folds the relationship away (in what is IMO one of the only design flaws in the schema)

In your case, actually, let's think about how that'd work.

We have pages p, categories c. One category can hold many pages, one page can belong to many categories.

So what we do is an intermediate table, page_category. All it contains is a series of rows in two columns: page and category ids.

Then when we want the list of categories for a page, SELECT c.cat_id, c.cat_name FROM pages AS p INNER JOIN page_category AS pc ON (p.page_id = pc.page_id) INNER JOIN category AS c ON (pc.cat_id = c.cat_id) WHERE p.page_id = xyz.

Presto, you have a list of category ids and category names that page id xyz belongs to. Getting a list of pages in a category is just the reverse: SELECT p.page_id, p.page_name FROM category AS c INNER JOIN page_category AS pc ON (c.cat_id = pc.cat_id) INNER JOIN pages AS p ON (pc.page_id = p.page_id)


As you can imagine this can quickly get immensely complex; even the most complex queries in SMF never join more than about 7 tables (in some cases joining the same table to the query twice, under different joins, e.g. topic->message, joining against both first and last message ids)

Isn't this what I was doing before, except I was grouping it ?

Code Select Expand

SELECT p.id , p.name, p.is_locked, p.topic_id, IFNULL(mem.real_name, r.member) AS member_name, r.content, r.date, GROUP_CONCAT(c.name) AS created_categories, GROUP_CONCAT(pc.category) AS categories
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.member = mem.id_member)
LEFT JOIN {db_prefix}wiki_page_categories AS pc ON (pc.page = p.name)
LEFT JOIN {db_prefix}wiki_categories AS c ON (c.name = pc.category)
WHERE p.name = {text:name}
LIMIT 1

[Arantor]

No, you were pulling it into a single column, which apart from being inefficient (since you only then have to split it again after), is not cross DB compliant, because GROUP_CONCAT does not exist except in a UDF setup on PostgreSQL, which means we can't approve it anyway.

I also wouldn't bundle it into the same query, I'd actually be more explicit and have a query for categories separately. Daft as it might sound, in virtually every case it'll perform better.

[sangwe11]

No, you were pulling it into a single column, which apart from being inefficient (since you only then have to split it again after), is not cross DB compliant, because GROUP_CONCAT does not exist except in a UDF setup on PostgreSQL, which means we can't approve it anyway.

I also wouldn't bundle it into the same query, I'd actually be more explicit and have a query for categories separately. Daft as it might sound, in virtually every case it'll perform better.

Well I think if I do split it into multiple queries, I can keep it down to 2.

[Arantor]

You don't need to go beyond that - one to get the page, one to get the list of categories that page is in.

[sangwe11]

You don't need to go beyond that - one to get the page, one to get the list of categories that page is in.

Well, basically, I need to select the page, which I have got already, the pages categories, and the categories out of them which are created.

I have the page query here.

Code Select Expand

SELECT p.id , p.name, p.is_locked, p.topic_id, IFNULL(mem.real_name, r.member) AS member_name, r.content, r.date
FROM {db_prefix}wiki_pages AS p
INNER JOIN {db_prefix}wiki_revisions AS r
ON (p.current_revision = r.id)
LEFT JOIN {db_prefix}members AS mem ON (r.member = mem.id_member)
WHERE p.name = {text:name}
LIMIT 1

I now need a query to select the pages categories from wiki_page_categories, that then also checks the wiki_categories to see if that category is created, and if so, it gives it a 1 (true), if not a false.

So, the output should be ..

Page categories => ("name" => "Main", "created" => "1 or 0"),

I was thinking something along the lines of this.

Code Select Expand

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

This doesn't do what I quite do, I need to get it to do a sort of if empty(c.name) return 0 else 1 sort of thing.

[Arantor]

So you populate page_categories on page save?

Then it's easy, LEFT JOIN instead of INNER JOIN, and turn c.name into IFNULL(c.name, 0) AS name

Returns 0 if not created or the name if it is. Or even, thinking about it, just c.name as-is and compare whether it's null when you get it back.

[sangwe11]

So you populate page_categories on page save?

Then it's easy, LEFT JOIN instead of INNER JOIN, and turn c.name into IFNULL(c.name, 0) AS name

Returns 0 if not created or the name if it is. Or even, thinking about it, just c.name as-is and compare whether it's null when you get it back.

So far I have this.

Code Select Expand

SELECT pc.category, IFNULL(c.name, "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}

Suddenly thought of the IFNULL before I read your post, but thanks for the help anyway.

Now just to get it to show as 1 if it is not null.

Is there a !IFNULL or anything ?

[Arantor]

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

[sangwe11]

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]

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

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.

Code Select Expand

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!

Code Select Expand

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 ?

You're missing my point.

Code Select Expand

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]

No, 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]

No, 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]

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

Code Select Expand

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