7 Replies - 9715 Views - Last Post: 18 January 2013 - 03:21 AM

#1 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 17 January 2013 - 03:29 AM

I have this MySql TABLE with name settings:
Posted Image
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?

Is This A Good Question/Topic? 0
  • +

Replies To: PHP MySql JOIN row Of Column using FIND_IN_SET method

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,686
  • Joined: 30-January 09

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 17 January 2013 - 03:45 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 17 January 2013 - 04:04 AM

View Poste_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

Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,686
  • Joined: 30-January 09

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 17 January 2013 - 04:45 AM

Have you tried SELECT b.featuredarticle instead of SELECT a.*?
Was This Post Helpful? 0
  • +
  • -

#5 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 17 January 2013 - 06:22 AM

View Poste_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

Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,686
  • Joined: 30-January 09

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 17 January 2013 - 01:12 PM

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?
Was This Post Helpful? 0
  • +
  • -

#7 OpenBSD  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 15-August 10

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 18 January 2013 - 01:02 AM

View Poste_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

Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,686
  • Joined: 30-January 09

Re: PHP MySql JOIN row Of Column using FIND_IN_SET method

Posted 18 January 2013 - 03:21 AM

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


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1