I'm trying to build an extension that would create pages for automatic redirections of short URLs, and to make it short, I need to get the path of a page given its entry_id.
Say, I have a page with the path: http://server.tld/index.php/path/to/my/page But, in the code, I only know the entry_id of this page.
If I look on the exp_channel_titles table, I can get the url_title field. But it will only contain "page". And I'd like to get "/path/to/my/page". And there doesn't seem to be any API for this.
Do you know how I could proceed?
Thanks a lot.
I did not find anything better than the following code:
//
//
// Don't look the following code just yet . . .
//
//
// You'll be pulling your hair out of your head. Just read me first.
// Following is a huge SQL query, that assume that pages are not nested
// than 9 times. This is actually a hard limitation of EE, and I'm using
// that to get the information I need in only one query instead of nine.
//
// The following code is just to get the path of the current entry we are
// editing, so the redirect page will know where to redirect. And I did
// not find any function or API to get this information. Too bad.
//
// If you find any solution to that, please answer
// http://stackoverflow.com/questions/8245405/expressionengine-how-to-get-the-path-of-a-page-given-its-entry-id-with-the-str
// it might save other people all the trouble.
// S
//
// P
//
// O
//
// I
//
// L
//
// E
//
// R
// First, we get all the entry_id of all the elements that are parent to
// the current element in the structure table (created by the Structure
// plugin).
$q = $this->EE->db->query(
"SELECT
S1.entry_id AS entry9,
S2.entry_id AS entry8,
S3.entry_id AS entry7,
S4.entry_id AS entry6,
S5.entry_id AS entry5,
S3.entry_id AS entry4,
S7.entry_id AS entry3,
S8.entry_id AS entry2,
S9.entry_id AS entry1
FROM
exp_structure AS S1,
exp_structure AS S2,
exp_structure AS S3,
exp_structure AS S4,
exp_structure AS S5,
exp_structure AS S6,
exp_structure AS S7,
exp_structure AS S8,
exp_structure AS S9
WHERE
S1.entry_id = $entry_id AND
S1.parent_id = S2.entry_id AND
S2.parent_id = S3.entry_id AND
S3.parent_id = S4.entry_id AND
S4.parent_id = S5.entry_id AND
S5.parent_id = S6.entry_id AND
S6.parent_id = S7.entry_id AND
S7.parent_id = S8.entry_id AND
S8.parent_id = S9.entry_id");
// Then, we construct a similar query to get all the url_title attributes
// for these pages.
$path = array();
$sql = array("SELECT" => "SELECT", "FROM" => " FROM", "WHERE" => " WHERE");
$j = 1;
for($i = 1; $i <= 9; ++$i){
$id = $q->row("entry$i");
if($id > 0){
$sql['SELECT'] .= " CT$j.url_title AS title$j,";
$sql['FROM'] .= " exp_channel_titles as CT$j,";
$sql['WHERE'] .= " CT$j.entry_id = $id AND";
$j++;
}
}
$sql['SELECT'] = rtrim($sql['SELECT'], ",");
$sql['FROM'] = rtrim($sql['FROM'], ",");
$sql['WHERE'] = preg_replace("/ AND$/", "", $sql['WHERE']);
$sql = $sql['SELECT'] . $sql['FROM'] . $sql['WHERE'];
$q = $this->EE->db->query($sql);
// Finally, we can construct the path for the current page
$path = "/";
for($i = 1; $i < $j; ++$i){
$path .= $q->row("title$i") . '/';
}
//
// Blood and bloody ashes.
//
May I suggest asking the Structure devs via their support forum?
I can't remember exactly where in the documentation it is, but I think your issue is coming from the fact that the page uris are not retrieved directly from the database.
They are instead located in the Expressionengine global configuration variables. I've been able to do a url lookup using the entry_id using the following code:
Note: This assumes you are using structure, pages module, etc.
<?php
$this->EE =& get_instance(); // Get global configuration variable
$site_id = $this->EE->config->item('site_id'); // Get site id (MSM safety)
$site_pages = $this->EE->config->item('site_pages'); // Get pages array
/** The array is indexed as follows:
* $site_pages[{site_id}]['url'] = '{site_url}
* $site_pages[{site_id}]['uris']['entry_id'] = {page_uri}
**/
$page_url = $site_pages[$site_id]['uris'][$entry_id];
?>
EDIT: I initially stated that the uris are not in the database which is not strictly speaking true... Pages are actually stored as a hashed string in exp_sites.site_pages indexed by site id.