[SOLVED] Need help with query (3 different tables).

[play_]

blog

+-------------+-------------+------+-----+---------------------+----------------+
| Field       | Type        | Null | Key | Default             | Extra          |
+-------------+-------------+------+-----+---------------------+----------------+
| blogID      | int(11)     | NO   | PRI | NULL                | auto_increment | 
| title       | varchar(30) | NO   |     | NULL                |                | 
| content     | text        | NO   |     | NULL                |                | 
+-------------+-------------+------+-----+---------------------+----------------+

 

tags

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| tagID | int(11)     | NO   | PRI | NULL    | auto_increment | 
| tag   | varchar(20) | NO   |     | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+

 

ref_blog_tag

+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| refID  | int(11) | NO   | PRI | NULL    | auto_increment | 
| blogID | int(11) | NO   |     | NULL    |                | 
| tagID  | int(11) | NO   |     | NULL    |                | 
+--------+---------+------+-----+---------+----------------+

 

Those are the 3 tables i'm having trouble with.

 

What I am trying to do is output the blog entry, and its tag(s).

 

I've tried join queries but can't get it to work.

 

Can it all be done in one query? (i think this is unlikely because if so, all the tags to that blog entry would have to be returned as an array)

 

And if not, how could i make this work?

 

If it helps, here's my PHP code:

<?php

$statement = $sql->prepare("select * from blog order by blog.blogID desc");
$statement->execute();
while( $row = $statement->fetch(PDO::FETCH_ASSOC) ) {
	// get tags for this entry
	$query = "
	select
		tags.tagID,
		tags.tag
	from
		tags			
	join
		ref_blog_tag
	on
		 ref_blog_tag.blogID = ?";

	$statement2 = $sql->prepare($query);
	$statement2->bindParam(1, $row['blogID']);
	$statement2->execute();
	echo '
	<div class="blog-entry">
		<div class="blog-date"><span class="blog-month">Oct</span>17</div>
		<div></div>
		<div style="text-align: justify; overflow: auto;">' . nl2br(stripslashes($row['content'])) . '</div>
	</div>';
}

 

Thanks and secret be told, i'm horrible with mysql.

[play_]

Alright i figured it out.

 

The second query from the post above should be

 

$query = "
	select
		tags.tagID,
		tags.tag
	from
		tags
	inner join ref_blog_tag ON ref_blog_tag.tagID = tags.tagID
	where
		ref_blog_tag.blogID = ?";

 

However, i am leaving this open for a bit just in case there is a way to do this all in just 1 query, and someone happens to show me how, or just tell me it can't be done.

[fenway]

Like with yet another JOIN?

[play_]

maybe?

[kickstart]

Hi

 

Something like:-

 

$query = "
	select a.blogID, a.title, a.content, c.tag
	from blog a 
	INNER JOIN ref_blog_tag b ON a.blogID = b.blogID
	INNER JOIN tags c ON b.tagID = c.tagID
	WHERE ref_blog_tag.blogID = ?";

 

All the best

 

Keith

[play_]

kickstart,

 

what does the a. and c. prepended to the column names mean?

[kickstart]

Hi

 

Just using an alias for the table names. Saves having to type the full table names. So you can you can just use b.blogID instead of ref_blog_tag.blogID.

 

All the best

 

Keith

[play_]

Thanks.

 

I'm still trying to get this in one query. this time, not with blogs, but a site im creating for storing regular expressions.

 

The concept is just like the blog scheme above. I have a table that stores regular expressions, one that stores tags/categories it may be in (ie, 'phone', 'numbers'), and a reference table.

 

mysql> describe expressions;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| regexpID       | int(11)      | NO   | PRI | NULL    | auto_increment | 
| reg_exp        | varchar(255) | NO   |     | NULL    |                | 
| description    | varchar(255) | NO   |     | NULL    |                | 
| author         | varchar(20)  | NO   |     | NULL    |                | 
| date_submitted | datetime     | NO   |     | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+

 

 

mysql> describe tags;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| tagID        | tinyint(2)   | NO   | PRI | NULL    | auto_increment | 
| name         | varchar(50)  | NO   |     | NULL    |                | 
| display_name | varchar(100) | NO   |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+

 

 

mysql> describe ref_regexp_tag;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| refID    | int(11) | NO   | PRI | NULL    | auto_increment | 
| regexpID | int(11) | NO   |     | NULL    |                | 
| tagID    | int(11) | NO   |     | NULL    |                | 
+----------+---------+------+-----+---------+----------------+

 

 

This would be easy if a regular expression could have only 1 category/tag. But since it can have more than one, i had to create this 3 table schema.

 

What i am trying to select is, the regular expression (reg_exp) and the tags associated with it.

 

 

 

ps: im not demanding a a solution, just throwing it out here in case someone's easily got this down. don't want anyone spending too much time and energy on it i can always use the same solution i used in the first post.

[play_]

I don't think it's possible.

 

given the following query

select distinct
	expressions.regexpID,
	expressions.reg_exp
from
	expressions
inner join ref_regexp_tag on ref_regexp_tag.regexpID = expressions.regexpID
[/code[

when i print_r the results, i get

[code]
Array
(
    [0] => Array
        (
            [regexpID] => 1
            [reg_exp] => [:digit:]
        )

)

 

If i were to select all the tags associated with that reg_exp, i don't even see how it would show up in this array.

[kickstart]

Hi

 

This would give you one row per regular expression / name:-

 

SELECT a.regexpID, a.reg_exp, c.tagID, c.name, c.display_name
FROM expressions a
LEFT OUTER JOIN ref_regexp_tag b ON a.regexpID = b.regexpID
LEFT OUTER JOIN tags c ON b.tagID = c.tagID

 

All the best

 

Keith

[play_]

Thanks Keith.