Combine multiple MySql queries into a single query

Question

I'm trying to solve the problem of the second day but with no success yet.

My goal is:

For certain taxonomy term ID (for example 1) I need to get at least one imagefield filepath of nodes, which belong to that term id (tid 1)

Below are three queries which give me what I need. All works fine but I know it looks really ugly and, I'm sure, there is a big performance issue.

$childterm = 10; // Taxonomy term ID
$result = db_fetch_array(db_query("SELECT node.vid FROM node JOIN 
                                   term_node ON  node.vid=term_node.vid WHERE  
       term_node.tid=$childterm AND 
                                   node.type= 'product' LIMIT 0,1"));           

$nvid = $result['vid']; // Extracting node VID by term ID that will be used futher 

$result = db_fetch_array(db_query("SELECT field_image_cache_fid FROM
                                   content_field_image_cache WHERE
       vid = '%d'", $nvid));

$fid = $result['field_image_cache_fid']; // Extracting file ID from array

$result = db_fetch_array(db_query("SELECT filepath FROM files WHERE
                                   files.fid = '%d'", $fid));           

$filepath = $result['filepath']; // Finally. Extracting file path from array

Please look at the picture.

alt text How can I improve the query? Can I get filepath value using only ONE sql query?

Thanks in advance.

googletorp · Accepted Answer · 2010-11-01 15:55:08Z

5

Something like this should work.

$sql = "SELECT f.filepath FROM {node} AS n
INNER JOIN {term_node} AS t ON t.nid = n.nid
INNER JOIN {content_field_image_cache} as c ON c.nid = n.nid
INNER JOIN {files} AS f on f.fid = c.fid
WHERE n.type = '%s'
AND t.tid = %d;"

$result = db_query($sql, $node_type, $tid);

Note that you should get the info on the content table name using

$db_info = content_database_info(content_fields($field_name));

The reason is because that the SQL will break if you switched from/to multiple values, unless you use this method to extra table and column names.

edited Nov 1, 2010 at 15:55

answered Nov 1, 2010 at 15:30

googletorp

33.3k15 gold badges69 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user359040 Over a year ago

I think these should probably be inner joins, rather than left outer joins.

googletorp Over a year ago

@Mark B: True, old habits die hard.

berkes Over a year ago

Also, be aware of the fact that CCK, the module providing tables like content_field_image_cache might (and therefore WILL) change the database layout at will. That table could just dissapear on config-changes. Queries like yours will then break. You might at least put in some documentation on this. Or, more solid, provide checks in hook_requirements().

ymakux · Accepted Answer · 2010-11-01 20:40:13Z

1

I accepted the answer from @googletorp. Although his solution was not 100% working for me, it pointed to the right direction.

Here is correct code:

$childterm = 10;// taxonomy term
$nodetype = 'product';// node type
$sql = "SELECT f.filepath FROM node n 
        INNER JOIN term_node tn ON tn.vid = n.vid  
        INNER JOIN content_field_image_cache cf ON cf.vid = n.vid 
        INNER JOIN files f ON f.fid = cf.field_image_cache_fid 
        WHERE n.type = $nodetype AND tn.tid = $childterm LIMIT 0, 1";
        $result = db_fetch_array(db_query($sql));
    $filepath = $result['filepath']; // image path

Thanks, you are all cool guys!

answered Nov 1, 2010 at 20:40

ymakux

3,4952 gold badges37 silver badges44 bronze badges

1 Comment

googletorp Over a year ago

In this case, it makes no difference if you join on vid or nid, since vid is only used for body content revisions of the node. Also in drupal, you should use placeholders, like I demonstrated, to help protect against SQL injection. Also in this case, since you are only getting one result, you should use db_result instead of db_fetch_array

Collectives™ on Stack Overflow

Combine multiple MySql queries into a single query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related