[*solved*]binding two separate values using DB API

113 Posts

Fatknuckle Reply #1, 18 years, 7 months ago

I have two sections: One for events and one for a directy of event locations.
I have a tv (venue) which uses the @select binding to generate a list of possible venues to select from. The list is directly pulled from the directory.
This is then fed into a template and the (venue) is appropriately displayed.

eg: Event Name, (content of the venue TV) and the category its listed in.

That works just fine, but now I would like that (content of the venue TV) do be a link to that page in the directory....

Like Event name, <a href="link to the venue tv">Venue TV name</a>

Since I have the venues in subcategories (eg restaurants --> venue TV name) I am trying to figure out the way to get the association between the two established.

I can get it to the directory (a href="/places/{+venue+} which is one step closer, but I need to get it to both the venue name and its parent.

Which I think maybe a snippet of some sort which would evaluate the venue name to its parent and then somehow return that value back to the template.

But I am wicked confused, see beta.akronicity.com/this-week for a little clearer representation of what I am trying to accomplish. Each venue in blue should link to the venue in the "places to go" section.

So I guess I am trying to do something like this...

Take the venue name (friendly Version) and associate it with its document alias & its parent document alias.
Return that to the href string.

documentAlias = (value of the venue TV)
document parent alias = (venueTV's parent)

complete string returned = documentParentAlias + /document alias

this would be the template code:

<a href="/places/(complete string returned">[+venueTV+]</a>

sorry just typing out loud here...thanks again

113 Posts

Fatknuckle Reply #2, 18 years, 7 months ago

SO I have a TV which outputs a pagetitle (eg "Weathervane Playhouse")

Now I need to search the DB for the entry whose pagetitle is equal to that value.
eg. venueName == "Weathervane Playhouse" then return that entries alias field.
(eg weathervane-playhouse)

I guess I know what I need to do, but I dont know how to write the damn thing. So I will more than happily offer a bounty for someone with far more command of PHP than I have??.

Thx, maybe I should move the post over to the bounty section.??

113 Posts

Fatknuckle Reply #3, 18 years, 7 months ago

Okay. I think I am getting closer.

I am using a chunk to get the id....

[[testingAPI? &id=`[+id+]`]]

That passes the id variable to the snippet which is this...

<?php
global $modx;
$venue = $modx->getTemplateVarOutput($idname=array("venueName"), $docid="$id"); // gets output of tv venueName from specified id
$getAlias= $modx->db->select("alias", "modx_site_content","pagetitle=($venue[venueName])"); // query db and return alias field from entry whose pagetitle matches the tv venueName
return "$getAlias";
?>

Unfortunately that returns this sql error:

« 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 'Playhouse)' at line 1 »
      SQL: SELECT alias FROM modx_site_content WHERE pagetitle=(Weathervane Playhouse)

WHich has something to do with the quotes I figure. But when I add them I get nothing...

SO I have defined the variable $venue which returns eg (Weathervane Playhouse), This return the alias for the entry whose pagetitle corresponds correct? Or am I completely off base? I’m trying to get up to speed with the whole API, PHP, SQL interaction.

I’m begging now and it ain’t pretty embarrassed

P

MODX Staff
10,725 Posts

opengeek Reply #4, 18 years, 7 months ago

Try this:

$getAlias= $modx->db->select("alias", "modx_site_content","pagetitle='" . $venue[venueName] . "'"); // query db and return alias field from entry whose pagetitle matches the tv venueName

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

113 Posts

Fatknuckle Reply #5, 18 years, 7 months ago

OG -
Thanks much. Since my last post I was giving myself a crash course on some php/sql basics and for a tutorials sake I was doing sql searches on the db directly. This query is the crux of my problem, well that and the fact I obviously don’t know what I am doing rolleyes

So the php code from the db query is this

$sql = 'SELECT `alias` FROM `modx_site_content` WHERE pagetitle = "Weathervane Playhouse"';

one thing that did come up as well, there are pagetitle which use an apostrophe such as "Luigi’s." This led me to add a addslashes to the $venue. And using that concantened code that you just gave me turns the snippet into this:

<?php
$venue = $modx->getTemplateVarOutput($idname=array("venueName"), $docid="$id");
$venueAlias= $modx->db->select("alias", "modx_site_content", "pagetitle='" . addslashes($venue) . "'"); 
return $venueAlias;
?>

But that returns "Resource id #51" and so on. http://beta.akronicity.com/events/this-weekSo I guess the problem I am having now is how to turn that back into the actual data eg "weathervane-playhouse". Am I making any sense?

MODX Staff
10,725 Posts

opengeek Reply #6, 18 years, 7 months ago

$modx->db->select() returns a MySQL result set. You need to then turn that into an array, via $modx->db->getRow($venueAlias). The result of that call will be an associative array that you can use, containing the columns you returned in your query. Since you are only returning one value, you might want to look at $modx->db->getValue() instead; it’s a shortcut to getting a single column from a single selected row.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

113 Posts

Fatknuckle Reply #7, 18 years, 7 months ago

Thanks for pointing me in the right direction. I’ll be giving that a whack in the morning...

113 Posts

Fatknuckle Reply #8, 18 years, 7 months ago

Nice. That worked. Although I had to hunt and peck with the quotes...Heres the final which only took me something like 36 hours to come up with rolleyes

...

<?php
// Set variable $venue to the value of tv venueName
$venue = $modx->getTemplateVarOutput($idname=array("venueName"), $docid="$id");

// Take value of $venue array, escape the result, then get record from db where the pagetitle = venueName tv
$venueAlias = $modx->db->getValue( $modx->db->select( 'alias', 'modx_site_content', 'pagetitle="' . addslashes($venue[venueName]) . '"' ) );

// Return the result
return $venueAlias;
?>

The time wasn’t a waste though since now I am way more used to the DBAPI, PHP & SQL and how it all fits together.
I can’t say thanks enough.

-P

113 Posts

Fatknuckle Reply #9, 18 years, 7 months ago

Actually I took it one step further to get the parent’s alias as well, which uses getRow vs. getValue for the result set, this is actually for a heirarchical link using a tv for the starting point, in case anyone is interested (probably reinventing the wheel here but its a good learning experience on working with the DB API)

<?php
// Set variable $venue to the value of tv venueName
$venue = $modx->getTemplateVarOutput($idname=array("venueName"), $docid="$id");

// Take value of $venue, escape the string, get the id,parent, alias from the row select alias WHERE pagetitle = $venue
$venueAlias = $modx->db->getRow( $modx->db->select( 'id,parent,alias', 'modx_site_content', 'pagetitle="' . addslashes($venue[venueName]) . '"' ) );

// This returns the parents alias
$venueParentAlias = $modx->getParent($venueAlias["id"],1,'alias');

// This returns the grandparents alias
$venueCategory= $modx->getParent($venueAlias["parent"],1,'alias');

// Return the result string to get a workable url
return $venueCategory["alias"]."/".$venueParentAlias["alias"]."/".$venueAlias["alias"];
?>