0

I'm using d3js on a visual display of user-generated content. Data that is being displayed is being pulled from the database with the standard three tables: entry, entry tags, entry-tag look-up table. My d3js code needs JSON output for this to work. I have the PHP-to-JSON output coded correctly, but am having a hard time figuring out what query I need to write to pull the correct IDs.

Entry table (create_thought)

id | email | thought_title

Tags table (thoughts_tags)

tag_id | tag_name

Look-up table (thoughts_tagged)

tagged_id | tag_id | thought_id

PHP-to-JSON code (functioning)

$titlequery = "
SELECT  `thought_title` FROM  `create_thought`
";
//THE BELOW QUERY IS INCORRECT
$tagquery = "
SELECT  `tag_id` AS `source`, `thought_id` AS `target` FROM  `thoughts_tagged`
";
$query1 = mysql_query($titlequery);

if ( ! $query1 ) {
echo mysql_error();
die;
}
$query2 = mysql_query($tagquery);

if ( ! $query2 ) {
echo mysql_error();
die;
}

$datatitle = array();

for ($x = 0; $x < mysql_num_rows($query1); $x++) {
$datatitle[] = mysql_fetch_assoc($query1);
}

$datatag = array();

for ($y = 0; $y < mysql_num_rows($query2); $y++) {
$datatag[] = mysql_fetch_assoc($query2);
}

echo '{
"nodes":' . json_encode($datatitle) . ',"links":' . json_encode($datatag) . '}';

This outputs to something like:

{ "nodes":[{"thought_title":"Title1"},{"thought_title":"Title2"}],"links":[{"source":"1","target":"8"},{"source":"2","target":"8"},{"source":"2","target":"17"}]}

My problem is the links (source/target) are all incorrect. I need the source to be the thought_id and the target to be all thought_ids with matching tag_ids. I've tried fiddling with some of the solutions from How to filter SQL results in a has-many-through relation, but they aren't what I need.

The below is the closest that I've gotten, but I'm not sure how to format it with the source and target; it's just pulling the thought_id with the one matching tag (55).

SELECT id
FROM   create_thought
WHERE  EXISTS (SELECT * FROM thoughts_tagged
WHERE  thought_id = id AND tag_id = 55)

Edit: Came up with this, which seems to be closer, but it is not formatted correctly. I need the thought_ids listed based on matching tags, but nothing else.

SELECT DISTINCT t1.*
FROM thoughts_tagged t1
INNER JOIN thoughts_tagged t2
ON t1.tag_id = t2.tag_id AND t1.thought_id <> t2.thought_id

Any help is appreciated!

Community
  • 1
  • 1
C B
  • 3
  • 3
  • I dont clearly understand your question but i think its about sql. So try this `SELECT E.*,T.*.L.* FROM entry AS E INNER JOIN look_up AS L ON E.id = L.though_id INNER JOIN tags AS T ON T.tag_id = L.tag_id WHERE L.tag_id = 55` – Nouphal.M Mar 02 '14 at 04:53
  • Thank you very much. This does work, but it works the same as my previous SQL, only pulls all data from the entries that have 55 as a tag (instead of the entry ID only). If either query works, I'm not sure how to now format this into PHP so that one is the source and the other is the target in my PHP file. Thank you! – C B Mar 02 '14 at 05:07
  • This query pulls all of the thought_ids that have similar tag_ids, but I'm not sure how to integrate it into my code above. Any ideas? `SELECT DISTINCT t1.* FROM thoughts_tagged t1 INNER JOIN thoughts_tagged t2 ON t1.tag_id = t2.tag_id AND t1.thought_id <> t2.thought_id` – C B Mar 02 '14 at 07:00

1 Answers1

0

Here was the answer a developer I know was able to come up with. It works perfect. :)

SELECT DISTINCT
t1.thought_id as source,
t2.thought_id as target
FROM thoughts_tagged t1
INNER JOIN
thoughts_tagged t2
ON t1.tag_id = t2.tag_id AND t1.thought_id <> t2.thought_id
C B
  • 3
  • 3