5 Replies - 468 Views - Last Post: 16 January 2013 - 08:41 AM Rate Topic: -----

#1 OpenBSD  Icon User is offline

  • D.I.C Head

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

join two table and print one result

Posted 16 January 2013 - 06:49 AM

Hi,
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 value put id of article with separate comma for featured articles. now i need to list/print my featured article only. how to ?

TABLE articles:
ID | TITLE | DATE | PUBLISH
1
2
3
4
5


TABLE settings / row featuredarticle:(in screen u see title / value)

1,7,16


Posted Image

NOTE: i now this bad design, but i custom the any modules of cms And I have no choice.

Thanks For Any Help

This post has been edited by OpenBSD: 16 January 2013 - 06:50 AM


Is This A Good Question/Topic? 0
  • +

Replies To: join two table and print one result

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3099
  • View blog
  • Posts: 10,887
  • Joined: 08-August 08

Re: join two table and print one result

Posted 16 January 2013 - 08:00 AM

Read up on joins and explode.
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3808
  • View blog
  • Posts: 13,515
  • Joined: 12-December 12

Re: join two table and print one result

Posted 16 January 2013 - 08:01 AM

You really need to do this after the data has been retrieved from the database.

$artids = explode(',', $featured); // creates an array

explode docs

If you are collecting several rows from your settings table then you probably need to combine these values, make-unique, and sort them. Perhaps similar to the following code which does the same for tags:

        // grab all the users' tags:
        $your_tags = array();
        $qtags = "SELECT tags FROM bookmarks WHERE user_id=$uid";
        $result_tags = mysqli_query($dbc, $qtags);
        if ($result_tags !== false) {
            while ($row_tags = mysqli_fetch_row($result_tags)) {
                if (strlen($row_tags[0])) {
                    array_splice($your_tags, count($your_tags), 0, 
                        explode(' ',$row_tags[0]));
                }
            }
            mysqli_free_result($result_tags);
        }

        $uniq_tags = array();
        if (!empty($your_tags)) {
            $uniq_tags = array_unique($your_tags);
            sort($uniq_tags);
        }

This post has been edited by andrewsw: 16 January 2013 - 08:03 AM

Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3099
  • View blog
  • Posts: 10,887
  • Joined: 08-August 08

Re: join two table and print one result

Posted 16 January 2013 - 08:13 AM

Andrewsw, you really should be using prepared statements to avoid having your site hacked.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3808
  • View blog
  • Posts: 13,515
  • Joined: 12-December 12

Re: join two table and print one result

Posted 16 January 2013 - 08:28 AM

View PostCTphpnwb, on 16 January 2013 - 08:13 AM, said:

Andrewsw, you really should be using prepared statements to avoid having your site hacked.


Thank you. Yes, we all should! The sample code is to demonstrate array_splice(), explode(), etc..
Was This Post Helpful? 0
  • +
  • -

#6 OpenBSD  Icon User is offline

  • D.I.C Head

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

Re: join two table and print one result

Posted 16 January 2013 - 08:41 AM

View PostCTphpnwb, on 16 January 2013 - 08:00 AM, said:

Read up on joins and explode.

I try with this code for JOIN and FIND_IN_SET method but not work for me and i see error:
SELECT  a.*
FROM    articles a
        INNER JOIN settings b
            ON FIND_IN_SET(a.ID, b.featuredarticle) <> 0


Error :
 Error 1054: Unknown column 'b.featuredarticle' in 'on clause'


can u tell me with any example code! Thanks

This post has been edited by OpenBSD: 16 January 2013 - 08:43 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1