| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
04-27-09, 22:29
|
|
Registered User
|
|
Join Date: Apr 2009
Posts: 6
|
|
|
Help with retrieving data...
|
|
[PROBLEM SOLVED]
Code:
TABLE BLOG - tBlog TABLE TAGS - tTags
--------------------------- ---------------------------
| blog_id | blog_tags | | tag_id | tag_name |
--------------------------- ---------------------------
| 1 | 2,3,5 | | 1 | html |
--------------------------- ---------------------------
| 2 | php |
---------------------------
| 3 | mysql |
---------------------------
| 4 | javascript |
---------------------------
| 5 | xml |
---------------------------
I'm trying to get all 3 tag names (php, mysql, xml) for blog_id = 1 and the following is what I've tried but it returns only the first tag (php)...
SELECT tBlog.blog_id, tTags.tag_name FROM tBlog JOIN tTags ON tTags.tag_id IN (tBlog.blog_tags) WHERE tBlog.blog_id = 1
So please guide me to the correct solution, thanks.
|
Last edited by JayQuery; 04-30-09 at 16:38.
|
04-28-09, 03:17
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,260
|
|
I think you need to rethink your table design
depending what is in tBlog you may needs to add a 3rd table to act as an intersection table where you associate a blog with a tag
the association table will contain the relevant data that is unique to the association (often that means just the primary keys of the 'parent' tables).
if there is no other data in the tBlog table then you could redesign that to allow for multiple entries per blogtag (keep the blogid and have a different row per blogtag. that does mean you couldn't use an autonumber for blogid.
my guess is this is a cut down table design, so a third (Intersection) table is the way to go.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton 
|
|
04-28-09, 04:42
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
|
Quote:
|
Originally Posted by healdem
I think you need to rethink your table design
|
Come on Healdem, you know the design needs to be changed 
|
|
04-29-09, 06:39
|
|
Registered User
|
|
Join Date: Aug 2005
Posts: 30
|
|
Use this query
SELECT tBlog.blog_id, GROUP_CONCAT(DISTINCT tTags.tag_name
ORDER BY tTags.tag_name DESC SEPARATOR ', ') FROM tBlog LEFT JOIN tTags ON tTags.tag_id IN (tBlog.blog_tags) WHERE tBlog.blog_id = 1 GROUP BY tBlog.blog_id
|
|
04-29-09, 07:26
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by ashish_mat1979
FROM tBlog LEFT JOIN tTags ON tTags.tag_id IN (tBlog.blog_tags)
|
that's not going to work the way you think it will 
|
|
04-29-09, 08:00
|
|
Registered User
|
|
Join Date: Aug 2005
Posts: 30
|
|
Thanks r937 to point out this one is corrected query:
SELECT tBlog.blog_id, GROUP_CONCAT( DISTINCT tTags.tag_name
SEPARATOR ', ' ) FROM tBlog, tTags WHERE FIND_IN_SET( tTags.tag_id, tBlog.blog_tags ) >0 AND tBlog.blog_id =1 GROUP BY tBlog.blog_id
|
|
04-29-09, 09:55
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,260
|
|
hint
***table redesign***
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
|
|
04-29-09, 13:15
|
|
Registered User
|
|
Join Date: Apr 2009
Posts: 6
|
|
Thanks ashish_mat1979, I tried your query but it doesn't work.
I have created another table to link tBlog and tTags as most of you suggested
Code:
BLOG - tBlog TAGS - tTags RELATIONSHIPS - tTagRel
------------------------ ------------------------ --------------------
| blog_id | blog_title | | tag_id | tag_name | | blog_id | tag_id |
------------------------ ------------------------ --------------------
| 1 | testing | | 1 | html | | 1 | 2 |
------------------------ ------------------------ --------------------
| 2 | php | | 1 | 3 |
------------------------ --------------------
| 3 | mysql | | 1 | 5 |
------------------------ --------------------
| 4 | javascript |
------------------------
| 5 | xml |
------------------------
The query I'm using...
SELECT tBlog.blog_title, tTags.tag_name FROM tBlog LEFT JOIN tTagRel ON (tTagRel.blog_id = tBlog.blog_id) LEFT JOIN tTags ON tTags.tag_id = tTagRel.tag_id WHERE tBlog.blog_id = 1
And fetching data...
Code:
$data = mysql_fetch_array($result);
echo $data['tag_name']; // outputs php ONLY
I'm pretty sure the query statement is incorrect because it only outputs the first tag.
So how can I get all 3 containing in an array like $data['tag_name'] = array(0=>'php', 1=>'mysql', 2=>'xml')
|
|
04-29-09, 15:55
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by JayQuery
I have created another table to link tBlog and tTags as most of you suggested
|
i would use the same concept (a "many-to-many" relationship table), but i would use the tag name itself instead of a silly integer surrogate
Code:
BLOG - tBlog RELATIONSHIPS - tTagRel TAGS - tTags
------------------------ ---------------------- ---------------
| blog_id | blog_title | | blog_id | tag_name | | tag_name |
------------------------ ---------------------- ---------------
| 1 | testing | | 1 | php | | html |
------------------------ ---------------------- ---------------
| 1 | mysql | | php |
---------------------- ---------------
| 1 | xml | | mysql |
---------------------- ---------------
| javascript |
---------------
| xml |
---------------
you have a choice, now, in whether to declare the tTags table at all
-- if you want to control the vocabulary, i.e. to ensure that only approved tag names can be used, then tag_name in tTagRel will be a Foreign Key to tag_name in tTags
-- if you want to allow any tag names to be used at all (folksonomy), then you would not declare the tTags table
in both cases, the query to retrieve blogs along with their tag names remains the same
sweet, eh?
|
|
04-29-09, 16:10
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Quote:
|
Originally Posted by r937
sweet, eh?
|
totally
|
|
04-29-09, 17:51
|
|
Registered User
|
|
Join Date: Apr 2009
Posts: 6
|
|
Thanks r937, I got your idea.
Yes, I want to control all the tags because:
- when writing a post, I wanna display a few popular tags (getting those from tTags) with checkboxes so I could save some time.
- when I want to edit a tag name, I edit the one in tTags table only, not updating every cell in tTagRel. I think using names instead of numbers is not a good idea in this case.
And if I used all 3 tags and used names in tTagRel, all the tag names are declared TWICE, isn't that gonna increase the size of mysql data? (say I have 5000 tags or something that contains more than 10 words other than tags)
I'm not just really trying to solve the tag problem, but I want to learn more about MySQL. So could you please take some time and answer the question in my last post? That is, using my table design and linking all 3 tables to get those tags. Thanks again.
|
|
04-29-09, 18:21
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by JayQuery
- when I want to edit a tag name, I edit the one in tTags table only, not updating every cell in tTagRel. I think using names instead of numbers is not a good idea in this case.
|
actually, it is 
can you give me an example of a tag that you need to "edit"? and an estimate of how often you will need to do this?
also, have you ever heard of ON UPDATE CASCADE?
|
|
04-30-09, 03:43
|
|
Registered User
|
|
Join Date: Apr 2009
Posts: 6
|
|
OK, r937, I'm not an expert, I just started to learn MySQL. Like I said, I'm not just trying to solve the tag promblem, but to learn something like joing 3 tables...Yes, a tag in this case could probably never been edited, but it may happen on other cases, so could you please ignore those conditions?
If you were willing to help, thank you very much and please write me the correct query statement with my table design? (even if you think it's a terribly bad design)
If you were just trying to pick on those insignificant conditions and showing me how much you know, please, get a cup of coffee and take some rest.
|
|
04-30-09, 06:43
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by JayQuery
If you were just trying to pick on those insignificant conditions and showing me how much you know, please, get a cup of coffee and take some rest.
|
i was trying to teach you, but that coffee sounds like a great idea, it will warm me up while i wait for you to actually test the query that you wrote and report back what happened
|
|
04-30-09, 07:27
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
I believe this is called "biting the hand that feeds".
Don't expect the answer on a plate - show a willingness to learn and you will gain a great deal from this community
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with retrieving data...
