[OpenBSD]

I have this MySql TABLE with name settings:

i need to select featuredarticle row from title columns (settings table) for JOIN and FIND_IN_SET method. i try this code but i dont know how to work for select featuredarticle 'row':

SELECT  a.*
    FROM    articles a
            INNER JOIN settings b
            ON FIND_IN_SET(a.ID, b.title) <> 0

how to select this row? If my way is the wrong, what's way is true?

[e_i_pi]

Firstly I will say, storing data like that entirely goes against the concept of normalisation. Because your data is stored in that fashion, it does not even meet 1NF (first normal form). Databases should adhere to at least 3NF in most cases. Because your DB does not adhere to even 1NF you have to use costly functions like FIND_IN_SET, and your overall DB performance I imagine will be quite poor.

Nonetheless, in regards to your problem, have you tried casting the ID to CHAR datatype? The FIND_IN_SET function may be mismatching the types. Also, you might want to make the check on > 0, I'm not sure how MySQL handles <> 0 compared to NULL, which the function will return is a.ID or b.title is NULL:

SELECT  a.*
FROM articles a
INNER JOIN settings b
ON FIND_IN_SET(CAST(a.ID AS CHAR), b.title) > 0

Also, I'm moving this to the DB forums.

This post has been edited by e_i_pi: 17 January 2013 - 03:48 AM

[OpenBSD]

e_i_pi, on 17 January 2013 - 03:45 AM, said:

Firstly I will say, storing data like that entirely goes against the concept of normalisation. Because your data is stored in that fashion, it does not even meet 1NF (first normal form). Databases should adhere to at least 3NF in most cases. Because your DB does not adhere to even 1NF you have to use costly functions like FIND_IN_SET, and your overall DB performance I imagine will be quite poor.

Nonetheless, in regards to your problem, have you tried casting the ID to CHAR datatype? The FIND_IN_SET function may be mismatching the types. Also, you might want to make the check on > 0, I'm not sure how MySQL handles <> 0 compared to NULL, which the function will return is a.ID or b.title is NULL:

SELECT  a.*
FROM articles a
INNER JOIN settings b
ON FIND_IN_SET(CAST(a.ID AS CHAR), b.title) > 0

Also, I'm moving this to the DB forums.

You right, But My Problem is how to select featuredarticle content from settings table?! in code, i and u select title column from settings database, this not work and false.

This post has been edited by OpenBSD: 17 January 2013 - 04:05 AM

[e_i_pi]

Have you tried SELECT b.featuredarticle instead of SELECT a.*?

[OpenBSD]

e_i_pi, on 17 January 2013 - 04:45 AM, said:

Have you tried SELECT b.featuredarticle instead of SELECT a.*?

i have two table for article and setting. in article table i put id,title,date ..etc and in settings table, i have row with name featuredarticle and content put id of article with separate comma for featured articles. now i need to list/print my featured article only. how to ?

ID | TITLE | DATE | PUBLISH
1
2
3
4
5

TABLE settings: row title = featuredarticle and content = 1,7,16:

1,7,16

please see screen picture in first post

This post has been edited by OpenBSD: 17 January 2013 - 06:24 AM

[e_i_pi]

Can you tell us what the column names are in both the articles and settings tables, and also what the numbers in the featuredarticle column relate to?

[OpenBSD]

e_i_pi, on 17 January 2013 - 01:12 PM, said:

Can you tell us what the column names are in both the articles and settings tables, and also what the numbers in the featuredarticle column relate to?

in settings TABLE i have two column. 1- title 2-content(value) for featuredarticle title = featuredarticle and content = 1,7,16. this content is id of articles TABLE separate with comma. Now i need to list article name when article id = content(featuredarticle). my mean list article with id 1 and 7 and 16 from article table.

This post has been edited by OpenBSD: 18 January 2013 - 01:03 AM

[e_i_pi]

Then it sounds like you need to find results when article.id is in settings.featuredarticle, so maybe try this:

SELECT  a.*
FROM articles a
INNER JOIN settings b
ON FIND_IN_SET(CAST(a.ID AS CHAR), b.featuredarticle) > 0