By InterceptPoint on

I have a CCK content type called stuff with fields name, signupdate ,hometown.
I have 100 records (nodes);

I want to use db_query or db_query_range and db_rewrite to select the first 50 of these nodes and order by signupdate;

Question: What is the proper $sql = "SELECT * FROM {node} n ..." SYNTAX to use?

My basic problem is that I don' know how to reference the CCK fields in the query. Nothing I've tried works.

⋅ Categories: Drupal 6.x

Comments

InterceptPoint’s picture

InterceptPoint commented

Seems like a simple thing but the answer must be buried deep in the Drupal or CCK documentation. I can't find it.

Codeblind’s picture

Codeblind commented

If you don't want to use Views on the production server, you can install it on the development server and use it to build a view, then press Preview and it will show you the query it's running. Here is an example query from Views pulling from a CCK imagefield named field_image_gallery_image. It should shed some light on the table structure.

SELECT node.nid AS nid,
   node_data_field_image_gallery_image.field_image_gallery_image_fid AS node_data_field_image_gallery_image_field_image_gallery_image_fid,
   node_data_field_image_gallery_image.field_image_gallery_image_list AS node_data_field_image_gallery_image_field_image_gallery_image_list,
   node_data_field_image_gallery_image.field_image_gallery_image_data AS node_data_field_image_gallery_image_field_image_gallery_image_data,
   node_data_field_image_gallery_image.delta AS node_data_field_image_gallery_image_delta,
   node.type AS node_type,
   node.vid AS node_vid
 FROM drpl_node node 
 LEFT JOIN drpl_content_field_image_gallery_image node_data_field_image_gallery_image ON node.vid = node_data_field_image_gallery_image.vid
 WHERE (node.status <> 0) AND (node.type in ('image_gallery'))

I'd crack open Views, CCK and a couple of CCK modules and have a look around them too. There's probably some sort of query mechanism in CCK that exposes itself to other modules to make life easier.

ehikr’s picture

ehikr commented

Just a thought, but if you have to do it using an SQL query, then you'll have to go to your database and find out what those fields are named. Try getting the field names from phpMyAdmin or similar program.

InterceptPoint’s picture

InterceptPoint commented

Thanks for the inputs Codeblind and ehikr.

I know the names of the node fields. I can acess the Drupal database via my VPS-Vmin. Those field names do not work in a Drupal SQL query. But 'title works' and 'created' works and I'm sure that any native Drupal field will work. But that is not the syntax you use for a CCK field. Why not? Who knows?

And Codeblind: I've used the reference names that Views generates in my SQL Query. It's a no go. That was a surprise. I thought that would work for sure.

What's amazing to me is that I guess people just don't write much code with SQL queries. We're addicted to Views (me too) and it's made us lazy. I'll keep digging but someone who has written an SQL query sorted by just one CCK field could solve my problem. Just show us the code and we can figure it out.

calebtr’s picture

calebtr commented

Not sure if this is still timely, but you can always JOIN in the CCK tables.

By default, CCK creates a table called content_type_type for each content type, and each field is is given a field in the database field_fieldname_value. Sometimes instead of the '_value' suffix a CCK field will use another suffix. CCK E-mail fields are field_fieldname_email. Node reference fields are field_fieldname_nid. So check your database.

Given a CCK type 'foo' with a field named 'bar': 

SELECT * FROM {node} AS n JOIN {content_type_foo} AS c ON n.nid=c.nid ORDER BY c.field_bar_value;

The problem is that if you create another CCK type with a 'bar' field, CCK breaks off the 'bar' data from both content types into its own table. It uses the format content_field_fieldname. So your query would become:

SELECT * FROM {node} AS n JOIN {content_field_bar} AS c ON n.nid=c.nid ORDER BY c.field_bar_value;

So the problem I'm trying to solve is how to get your script to directly look up data in CCK tables without knowing if the fields live in content_type_ or content_field tables.

I found what I was looking for at http://drupal.org/node/131452