Here's a code snippet to show how to execute a query and fetch all the results into an associative array:
<?php
// create the SQL in a string
$sql = "
select *
from {node} n
where n.status = '1'";
// execute the query
$resource = db_query($sql);
// loop through the resource and fetch the rows
$results = array();
while ($row = db_fetch_array($resource)) $results[] = $row;
// show the results
echo "<pre>" . print_r($results, TRUE) . "</pre>";
?>This morning I was writing a query to get a distinct list of updated/added times for each node type per user. The result was going to be using in a table to show when the user last added or updated each type of node in the system. Here is an example of how to query the node table with subqueries:
<?php
$sql = "
select n.nid, n.vid, n.created, n.changed, n.type, n.title
from {node} n
join (
select max(changed) as maxChanged, type
from {node}
group by type
) as n2 on n2.type = n.type and n.changed = n2.maxChanged
join (
select max(nid) as maxNid, type
from {node}
group by type
) as n3 on n3.type = n.type and n.nid = n3.maxNid
where 1=1
and n.uid=%d
and n.status=1
order by n.changed desc
";
$resource = db_query($sql, db_escape_string($GLOBALS['user']->uid));
$results = array();
while($row = db_fetch_array($resource)) $results[] = $row;
?>