Help me write a SQL query : )

  • I think I have figured out how to access a certain piece of info I need in the database. The only problem is I have never written MySQL code before – and the piece of info I want spans 3 tables.

    Here is the relation:
    -In the first table (term_taxonomy) I need to select a particular item in the “description” area and its related “term_taxonomy_ID” found along the same row.

    -Then I move to table 2 (“term_relationships”), here I use the “term_taxonomy_ID” I found – I need to look along the row and get the corresponding “object_ID”

    -On to table 3 (posts) I need to use the “object_ID” I got from the last table. Its the same as the “ID” of the post i’m after. Once I have found the post via ID I need everything from that row – i’ll need to use the content, title etc.

    So, from what I have seen other people doing I think this is possible if I use an “inner-join” to link the tables together?

    One more thing, this query will need to return multiple posts. they all start with the same description like step 1, but there will be a few posts to return.

    Please, educate me on SQL. I hope its do-able!

Viewing 5 replies - 1 through 5 (of 5 total)

  • You did not specify the taxonomy (category, post_tag, etc) that you want, or whether you want to match the entire description or just find a phrase in it. Assuming you want taxonomy category, and a phrase in the description, I think this is the query you want:

    $description = 'what i want to find';
$sql = "SELECT * FROM $wpdb->posts p
INNER JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
INNER JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND tt.taxonomy = 'category'
AND tt.description like '%$description%'
ORDER BY p.post_date DESC";
    Thread Starter g3legacy

    (@g3legacy)

    Hi vtxyzzy, I was hoping you would write something : )

    I’m not getting very far with it at the moment…could you explain how you know what code to use for the INNER JOIN commands? The first one looks like “join the table term_relationships (table row?) where ID and object_ID are the same? Or am I missing something?

    Dan

    Here is a translation:

    Get all posts where
   (JOIN 1) the term_relationships object_id is the same as the post ID
   (JOIN 2) and the term_taxonomy term_taxonomy_id is the same as
          the term_taxonomy_id in term_relationships
   and WHERE the post_type is post
   AND the post_status is publish
   AND the term_taxonomy taxonomy is 'category'
   AND the term_taxonomy description contains $description
order by descending post_date (newest first)
    Thread Starter g3legacy

    (@g3legacy)

    Thanks, i’ll try it again. I managed to write some SQL stuff, it turned out ok, now i’m having problems calling the posts even with the ID’s! Your is more specific though so it might be better….

    Is there any chance you would consider talking with me over skype? As far as I know its still free, and I could really do with a hand. This is for a paid job, I can’t take it on until the problems are resolved. I might also be able to offer some financial incentive if the jobs goes ahead ok.

    email me at mac =at= mcdspot =dot= com

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Help me write a SQL query : )’ is closed to new replies.