Mysql – Selecting data from one table via categoryID field and displaying in different areas whilst excluding categoryID for one area

MySQLPHPselect

I have made a very simple CMS for the company I work for. We need to display articles on our index page as well as headlines above in a carousel.

All articles are setup with a table for articles and art_categories table to link articles to their respective categories. I have been able to create a query that selects all articles and that exclude the headlines like below.

SELECT SQL_CALC_FOUND_ROWS articles.*, art_categories.*
FROM articles, art_categories
WHERE articles.categoryID=art_categories.categoryID AND articles.categoryID NOT IN(8) AND art_categories.categoryID NOT IN (8)
ORDER BY publishdate DESC LIMIT {$start},5

{$start} is used for pagination. The above query functions 100% and excludes the headlines category from the general article display area.

Now the problem I have is not being able to display the headlines on the same page by selecting them from the same table via their respective categoryID

SELECT articles.*, art_categories.*
FROM articles, art_categories
WHERE articles.categoryID=art_categories.categoryID AND articles.categoryID=8 AND art_categories.categoryID=8
ORDER BY publishdate DESC LIMIT 5

Any advice would be greatly appreciated.

Thanks the issue has been resolved and question is closed

Best Answer

You can use LEFT JOIN for this:

SELECT 
     articles.*, 
     art_categories.*
FROM art_categories LEFT JOIN articles
ON articles.categoryID=art_categories.categoryID
WHERE art_categories.categoryID=8 
ORDER BY publishdate DESC LIMIT 5

as advice - avoid use *, better to list all columns by name, it also allow avoid duplicates and/or arrange aliases for them

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Mysql – Most efficient way to structure segmented data

One possibility, stated simply, is the events are all in a calendar table, along with any non-category-specific attributes... then tables for the separate types of events to store the category-specific attributes.

tbl_calendar
------------------
event_id (PK)
user_id (FK -> user.id)
start_time
end_time

tbl_cat1_event_info
------------------
event_id (FK -> tbl_calendar.event_id)
cat1_foo1
cat1_foo2

tbl_cat2_event_info
------------------
event_id (FK -> tbl_calendar.event_id)
cat2_foo3
cat2_foo4

This query would return essentially the same result as a union query of the two original tables.

SELECT *
  FROM tbl_calendar tc
  LEFT JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id
  LEFT JOIN tbl_cat1_event_info c2 ON c2.event_id = tc.event_id
 WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;
-- see the expanded version of this query below

That last line isn't technically necessary but it assures that no events that aren't either in c1 or c2 don't get returned.

Getting just the cat1 events:

SELECT *
  FROM tbl_calendar tc
  JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id;

Follow-up: Avoiding events inappropriately being assocated with more than one category

The business rules say that an event is always either a cat1 event or a cat2 event, never both... but the structure I suggested above does not contain any mechanism for avoiding the inconsistency of the same event_id being inserted in both the cat1 and cat2 event info tables, which would then indicate that one event was both types of events... which can't be true.

If we add an event_type_id column to tbl_calendar, we can also modify the joins so that only the appropriate *event_info table is joined.

-- assumes tbl_calendar has an event_type_id column
SELECT *
  FROM tbl_calendar tc
  LEFT JOIN tbl_cat1_event_info c1 ON tc.event_type_id = 1 AND c1.event_id = tc.event_id
  LEFT JOIN tbl_cat1_event_info c2 ON tc.event_type_id = 2 AND c2.event_id = tc.event_id
 WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;

This would not actually prevent the event from being inserted but it would result in it not being evaluated if it appears in the wrong table; only the correct table according to event_type_id would be joined for each event.

A more aggressive (but perhaps also more correct) approach in addition to the above would be before-insert and before-update triggers on the *event_info tables that checked the other *event_info table and used SIGNAL (introduced in MySQL 5.5) to throw an exception to prevent the incorrect data from being inserted or updated. One example:

DELIMITER //
CREATE TRIGGER tbl_cat1_event_info_bi BEFORE INSERT ON tbl_cat1_event_info FOR EACH ROW
BEGIN
    IF EXISTS (SELECT c2.id FROM cat2_event_info c2 WHERE c2.id = NEW.id) THEN
        SIGNAL SQLSTATE '45000'
               SET MESSAGE_TEXT = 'Conflicting value found in cat2_event_info';      
    END IF;
END;
//
DELIMITER ;

Best Answer

Related Solutions

Mysql – Help optimizing MySQL slow query

Mysql – Most efficient way to structure segmented data

Related Question