$modx->db->select returns a resource id

56 Posts

jflarity Reply #1, 17 years, 3 months ago

this is a small snippet i made.

<?php
//Get document info
global $modx;
$data = $modx->documentObject['createdby'];
$author = substr($data,1);
$table = $modx->getFullTableName("web_user_attributes");

//Get Creator email address
$db_query = $modx->db->select("email", $table, "id = $author");
return $db_query;
?>

my thoughts were that it would return the email address of the webuser that created the document. it returns "Resource id #17". what does that mean? i read the wiki before i posted, but i don’t really understand it.

for all things independent, check out http://www.indieophile.com

MODX Staff
1,535 Posts

Garry Nutting Reply #2, 17 years, 3 months ago

Yep, the select() will return a resource ID (a reference to the returned recordset) - you have to do something with the recordset that is returned from the select(), for example:

if ($modx->db->getRecordCount($db_query) > 0) {
    while ($row = $modx->db->getRow($db_query)) {
         //do something more meaningful with the row here
         echo $row['email'];
    }
}

Or, if you are only trying to get a single value from the database, you can use the alternative getValue() method:

$email = $modx->db->getValue('SELECT email FROM '.$table.' WHERE internalKey='.$author);
return $email;

FYI, it’s not ID that you want for the web_user_attributes table - the correct key column is: internalKey wink

Garry Nutting
Senior Developer
MODX, LLC

Email: [email protected]
Twitter: @garryn
Web: modx.com

56 Posts

jflarity Reply #3, 17 years, 3 months ago

ahhhh. thanks a lot!

for all things independent, check out http://www.indieophile.com

56 Posts

jflarity Reply #4, 17 years, 3 months ago

what i am trying to do is put this in a plugin so when i publish a document, an email is automatically sent to the web user who created it telling them that it is published. i understand that i have to right click in the document tree and publish it from there in order for it to fire, but i keep getting a parse error.

here is the error

MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’’ at line 1 »
SQL: SELECT email FROM `indieoph_modx`.`modx_web_user_attributes` WHERE internalKey =

and here is my code. any ideas?

// Handle event
$e = &$modx->Event;
switch ($e->name) {
case "OnDocPublished":
global $modx;
$data = $modx->documentObject['createdby'];
$author = substr($data,1);
$table = $modx->getFullTableName("web_user_attributes");

//Get Creator details
$db_query = $modx->db->select("email", $table, "internalKey = $author");
$db_results = $modx->db->getRow($db_query);

$Name = "Indieophile"; //senders name
$email = "[email protected]"; //senders e-mail adress
$recipient = $db_results['email']; //recipient
$mail_body = "your post has been published. See it at http://www.indieophile.com/"; //mail body
$subject = "your indieophile post has been published"; //subject
$header = "From: ". $Name . " <" . $email . ">\r\n"; //optional headerfields

mail($recipient, $subject, $mail_body, $header); //mail command 
  break;

case "OnWebLogout":
  // code here
  break;

//case "":
  // code here
//  break;

default: "";

  return;
}

for all things independent, check out http://www.indieophile.com

☆ A M B ☆
24,524 Posts

sottwell Reply #5, 17 years, 3 months ago

I’m not so sure the documentObject is available during that event if you’re publishing from the context menu. I think all it does is pass the document ID to the publish_content.processor.php file; that file only checks permissions, updates the document’s published status, and runs the cache sync utility.

The documentObject array is only available when a document is actually being parsed.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

56 Posts

jflarity Reply #6, 17 years, 3 months ago

yeah, i think you’re right. hm. any ideas on how i could get that information when a document gets published?

for all things independent, check out http://www.indieophile.com

☆ A M B ☆
24,524 Posts

sottwell Reply #7, 17 years, 3 months ago

Yes. Your plugin will need to do a query using the document ID (which you do have) to get that document’s creator. If you’re clever with convoluted SQL queries, you can get it all done with one joined query.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

56 Posts

jflarity Reply #8, 17 years, 3 months ago

sweet. thanks. i’ll post it when i have it to share with others.

for all things independent, check out http://www.indieophile.com

5,888 Posts

Bruno17 Reply #9, 17 years, 3 months ago

you can try:

$doc = $modx->getDocument($_GET['id'], '*', 1);
$author =$doc['createdby'];

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

56 Posts

jflarity Reply #10, 17 years, 3 months ago

well, here is the plugin. it works quite well. thank ya’ll for your guidance. i’m sure it could be a little cleaner. i may work on doing that and then submitting it as an actual configurable plugin.

// Handle event
$e = &$modx->Event;
switch ($e->name) {
case "OnDocPublished":
//Get document info
global $modx;
$page_id = $modx->getPageInfo($docid);
$content_table = $modx->getFullTableName("site_content");
$web_user_table = $modx->getFullTableName("web_user_attributes");

$data = $modx->db->getValue('SELECT createdby FROM '.$content_table.' WHERE id='.$page_id['id']);
$author = substr($data,1);

$address = $modx->db->getValue('SELECT email FROM '.$web_user_table.' WHERE internalKey='.$author);

$full_name = $modx->db->getValue('SELECT fullname FROM '.$web_user_table.' WHERE internalKey='.$author);

$Name = "Indieophile"; //senders name
$email = "[email protected]"; //senders e-mail adress
$recipient = $address; //recipient
$mail_body = $full_name.", your post, ".$page_id['pagetitle']. ", has been published. See it at http://www.indieophile.com/".$page_id['alias']; //mail body
$subject = "your post has been published"; //subject
$header = "From: ". $Name . " <" . $email . ">\r\n"; //optional headerfields

mail($recipient, $subject, $mail_body, $header); //mail command
  break;

case "OnWebLogout":
  // code here
  break;

//case "":
  // code here
//  break;

default: "";

  return;
}

for all things independent, check out http://www.indieophile.com