where exactly do I put the following code (from ProDrupal development)? Do I just create a new page and put the php code in between and just save the page?

it didn't work - i just get a plain page.. help if you can! :)

$sql = "SELECT * FROM {node} n WHERE type = 'page' ORDER BY
  n.created DESC"
$result = pager_query(db_rewrite_sql($sql), 0, 10);
while ($data = db_fetch_object($result)) {
  $node = node_load($data->nid);
  print node_view($node, TRUE);
}
// Add links to remaining pages of results.
print theme('pager', NULL, 10);

i've also tried (from drupal.org/node/142144

//This is numbers per page
$num_per_page = 10;

//actual query
$query = "SELECT nid,uid FROM {node} WHERE uid=%d AND type='jobs' ORDER BY created DESC";

//the count query should be similar to the query above
$count_query = "SELECT COUNT(*) AS row_count FROM {node} WHERE uid=%d AND type='jobs' ORDER BY created DESC";

//pager_query function
$result = pager_query($query, $num_per_page, 0, $count_query, $user_load->uid);

//dont forget
$output .= theme('pager');

Comments

NancyDru’s picture

omnyx’s picture

yeah, it was...but i still get blank pages. ideas??
thanks for your time...

NancyDru’s picture

omnyx’s picture

forgot to mention - i put semicolon and it's still not working.
Also, the bottom example has no semicolon problems but it isn't returning anything...

i'm soooo confused....

NancyDru’s picture

<?php
$sql = "SELECT * FROM {node} n WHERE n.type = 'story' ORDER BY n.created DESC";
$result = pager_query($sql, 10);
if (db_num_rows($result) > 0) {
  while ($data = db_fetch_object($result)) {
    $node = node_load($data->nid);
    print node_view($node, TRUE);
  }
  // Add links to remaining pages of results.
  print theme('pager', NULL, 10);
}
else { echo "<p>None found.</p>"; }
?>

BTW, since this code you are creating is itself a 'page', it will try to render itself. In my test, this caused the inability to save the code. That's why I changed it to "story".

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

thank you Nancy!
it worked...a quick question related to this database manipulation.
Let's say I want users, upon login, to see on their menu a link to the page with the list of the content they submitted.
Would i do it the following way:

1. create a menu (or a block?) to be displayed under navigation menu with the link to the page i'll create (see next line)
2. create a page with the php code where I invoke the global $user object and do a sql query similar to the one present here only with the condition node->uid = user->uid

would that be the way to do it?
also, can this be cached or something? or do i need to invoke the sql query every time?
most important question - what would the most efficient way (in terms of server resources) of doing this be?

thank you for helping me out. i much appreciate it :)

NancyDru’s picture

Create a page with this code (yes, similar), and add a menu item for it.

global $user;
$sql = "SELECT * FROM {node} n WHERE n.uid = '. $user->uid .' ORDER BY n.sticky desc, n.changed DESC";
$result = pager_query($sql, 100);
if (db_num_rows($result) > 0) {
  $rows = array();
  $header = array(t('Title'), t('Last Change'));
  while ($data = db_fetch_object($result)) {
    $rows[] = array(l($data->title, 'node/'. $data->nid), 
                          format_date($data->changed));
  }
  // Add links to remaining pages of results.
  print theme('table', $header, $rows);
  print theme('pager', NULL, 100);
}
else { echo "<p>None found.</p>"; }

Unless it is a very busy site, you may not need the pager stuff any more, since this just shows the titles.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

the code returned 'None Found'. but when I put
n.uid=' user->uid'
instead of
n.uid='. user->uid .'

it worked. Do you think the dots had something to do with it?
thank you

NancyDru’s picture

omnyx’s picture

how would i disable the menu for the anonymus user?
i.e. the front page still contains "my nodes" (that's how i named the menu). Of course, when anonymus user clicks there "none found" gets returned...
However, I'd like for that "my nodes" menu item (under navigation menu) to be available/visible only when user logs in. Is there a way to do that?

thank you!

NancyDru’s picture

You can always create a new menu block that is visible to only authenticated users. The other thing I can think of is the menu-per-role module.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

hm...but, for example, 'Create Content' is part of the Navigation Menu but isn't shown for anonymous users...But when I go to settings/menus and click on 'Create Content' I really see no difference between that menu item and the one i created except for the fact that 'Create Content' isn't visible to anonymous users.

is it an internal drupal thing?
thanks!

NancyDru’s picture

Most sites end up with "create content" being visible to anonymous users and have to turn it off. I suspect it could easily be turned back on, such as allowing a content type to be created by an anon. user. But why open yourself up to the spammers that will come?

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

no, i'm saying i want to have it off to anon. users.
To clarify a bit: So whichever menu item i add to the 'Navigation' menu they will be visible (not necessarily available meaning they can click but may not enter) to anon. users EXCEPT for 'create content' item, which is itself within Navigation menu.
Now what I don't understand is why I can't make other menu items available only to authenticated users like 'create content' is?

Also, there is one weird thing with 'create content' menu item. By default it's not visible to anon. users but if i just go to menus and click on 'configure' next to 'create content' it opens, logically, a configuration page for that menu item. Now the strange thing is if i don't change anything there and just click 'save changes' i somehow mess up the 'create content' item as it now becomes available to anon. users as well.
Also, if i click on 'reset' next to 'create content' on the menu settings page everything goes back to normal and 'create content' is no longer available to anon. users...
weird...

any ideas?

NancyDru’s picture

That's the way Create content works. I have no idea why.

As for limiting menu items, menu per role will do that.

Perhaps the major menu changes in D6 will address this problem.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

hi Nancy,
thanks for your help throughout this thread!
i learned some nice new stuff...

omnyx’s picture

sorry to be bugging you again. I have a related question:
this code

global $user;
$sql = "SELECT * FROM {node} n WHERE n.uid = '. $user->uid .' ORDER BY n.sticky desc, n.changed DESC";
$result = pager_query($sql, 100);
if (db_num_rows($result) > 0) {
  $rows = array();
  $header = array(t('Title'), t('Last Change'));
  while ($data = db_fetch_object($result)) {
    $rows[] = array(l($data->title, 'node/'. $data->nid),
                          format_date($data->changed));
  }
  // Add links to remaining pages of results.
  print theme('table', $header, $rows);
  print theme('pager', NULL, 100);
}
else { echo "<p>None found.</p>"; }

worked nicely. Now, I'd like to have two tables, one below the other, but on the same page. Can I simply do something like this?

global $user;
$sql = "SELECT * FROM {node} n WHERE n.uid = '. $user->uid .' ORDER BY n.sticky desc, n.changed DESC";
$result = pager_query($sql, 100);
if (db_num_rows($result) > 0) {
  $rows = array();
  $header = array(t('Title'), t('Last Change'));
  while ($data = db_fetch_object($result)) {
    $rows[] = array(l($data->title, 'node/'. $data->nid),
                          format_date($data->changed));
  }
  // Add links to remaining pages of results.
  print theme('table', $header, $rows);
  print theme('pager', NULL, 100);
}
else { echo "<p>None found.</p>"; }

$sql1=blabla...//any new query
$result = pager_query($sql1, 100);
if (db_num_rows($result) > 0) {
  $rows = array();
  $header = array(t('Title'), t('Last Change'));
  while ($data = db_fetch_object($result)) {
    $rows[] = array(l($data->title, 'node/'. $data->nid),
                          format_date($data->changed));
  }
  // Add links to remaining pages of results.
  print theme('table', $header, $rows);
  print theme('pager', NULL, 100);
}
else { echo "<p>None found.</p>"; }

this gave me two pages, one on top of the other. But, was this a valid thing to do from the pager/print theme point of view??
I'd like to keep them separated, so that, once I have a loot of posts, I could independently have buttons that lead to 'next' pages for both of these. That is, right below the top table I would have "1", "2", "last" (or whatever pager does) and the same below the bottom table.
thanks!

NancyDru’s picture

I would suspect that this isn't possible, but there is the capability within the pager routines to have multiple pagers - check the APIs on how to do this.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

hm, it seems to work :))
so, I'll just leave it like that...and keep my fingers crossed :))

one more quick question related to original pager_query.

So, when we use db_query we can use %d to indicate a 'variable', i.e. something like

db_query("SELECT * FROM {node} n where n.uid=%d', $user->uid);

but how would I do the n.uid=%d', $user->uid with pager_query. I tried using the same way, i.e. setting $sql be my $sql query and then writing

pager_query($sql, $user->uid)

but it doesn't seem to work that way.
Any ideas?
thanks so much for help!

NancyDru’s picture

I've never gotten substitution to work on either pager_query or db_rewrite_sql. In those cases, I just use regular php concatenation.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

omnyx’s picture

speaking of php concatenation it seems that several different ways all work. I.e.

SELECT * FROM {node} n where n.uid=$user->uid

seems to be giving me the same result as

SELECT * FROM {node} n where n.uid='$user->uid'

What is the right way? I just want to get my query-writing right and according to the rules :)

NancyDru’s picture

For a numeric value (like a user id), I don't think it matters. An alphanumeric value (like a user name), however, must be enclosed in quotes.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

dwees’s picture

If you look up pager_query at http://api.drupal.org, you'll find it has additional arguments to db_query, which is why what you wanted to do doesn't work.

Dave

My site: http://www.unitorganizer.com/myblog

omnyx’s picture

huh...double post...sorry

omnyx’s picture

huh...double post...sorry