Forums

Use Itemid from a menu URL as variable in WHERE SQL

Joe Stokes 27 Oct, 2010
Hi to all

I'm new to chronoconnectivity with limited php experience.

I can display data from a complex multi-table query to my liking in a list. I call the connection from a number of menu items and the URL typically looks like this:
...index.php?option=com_chronoconnectivity&Itemid=<an integer based on the menu item >

I need to dynamically filter the data displayed in the list based on the menu item selected. The data displays fine when I "hardcode" the Itemid in the WHERE SQL box.

Would there be a way to get the `Itemid` from the URL as a parameter into my connection. I have tried the following based on other posts in this forum, but no success. ie.
$menu_itemid = JRequest::getInt('Itemid', 0, 'get');

Any suggestions will be greatly appreciated.

Joe Stokes
GreyHead 27 Oct, 2010
HI Joe,

I'd expect that code to work OK. Do you see anything if you echo out the value?
echo'<div>$menu_itemid: '.print_r($menu_itemid, true).'</div>';


You might also try looking at JURI and see if the info is held there. Test with
<?php
$uri =& JFactory::getURI();
echo'<div>$uri: '.print_r($uri, true).'</div>';


Do you have SEF URLs setup - sometimes they can hide the Itemid value?

Bob
Joe Stokes 27 Oct, 2010
Hi GreyHead

Very quick reply - thank you

I appears as if $menu_itemid is NULL or could not be extracted

I've tried your JFactory::getURI() suggestion and it produces the complete uri and looks like this:

$uri: JURI Object ( [_uri] => http://localhost/islagosb1a/index.php?option=com_chronoconnectivity&Itemid=82&limitstart=20 [_scheme] => http [_host] => localhost [_port] => [_user] => [_pass] => [_path] => /islagosb1a/index.php [_query] => option=com_chronoconnectivity&Itemid=82&limitstart=20 [_fragment] => [_vars] => Array ( [option] => com_chronoconnectivity [Itemid] => 82 [limitstart] => 20 ) [_errors] => Array ( ) )



So now I just have to find a way to extract the Itemid from this string - any suggestions will help a lot.

I am using cronoconnectivity to display a category list of items, but this list displays a lot more than just the category information, however it still reacts as if it is a normal category list.

Once I get this going, I will gladly share it with the forum.

Regards

Joe
GreyHead 27 Oct, 2010
Hi Joe,

Try this please
<?php
$uri =& JFactory::getURI();
$item_id = $uri->get('Itemid');
echo'<div>$item_id: '.print_r($item_id, true).'</div>';
?>
That should return the value of Itemid for you.

More info on JURI here

Bob
Joe Stokes 27 Oct, 2010
Hi GreyHead

The Itemid remains elusive despite all our efforts🙄

I have tried a number of options based on the URI documentation, but no success. I must be missing something in the process - after all I am following the derivative method of learning (first derive what does not work and then hope to get lucky)

Here is the code I have been using:

The WHERE SQL:
<?php
if ( !$mainframe->isSite() ) { return; }
$uri =& JFactory::getURI();
$menu_itemid = $uri->get('Itemid');
$category_id = "";
$sCategory_name = "";

if ( $menu_itemid ) {
	$db =& JFactory::getDBO();
	$query = "
		SELECT 	`fki_k2_cat_ID`
		FROM 	`#__k2_cat_cc_menu`
		WHERE 	`fki_menu_ID` = ".$db->quote($menu_itemid)." ;
	";
	$db->setQuery($query);
	$category_id = $db->loadResult();
	
	$query = "
		SELECT	`name`
		FROM	`#__k2_categories`
		WHERE	`id` = ".$db->quote($category_id)." ;
	";
	$db->setQuery($query);
	$sCategory_name = $db->loadResult();
}
echo "WHERE fki_category_ID = "$category_id;
?>


The HEADER Section:
<style type="text/css">
div.data_grid_head {
  height:16px;
  width:609px;
  font-size:11px;
  font-weight:700;
  background:#fffbfb;
  border-top:1px solid #ea8484;
  border-bottom:1px solid #ea8484;
  border-left:1px solid #ea8484;
  margin-top:15px;}

div.data_grid_row  {
  height:16px;
  width:609px;
  font-weight:400;
  border-left:1px solid #ea8484;
  border-bottom:1px solid #FFE6E6;}

div.data_grid_element {
  height:16px;
  font-size:11px;
  color:#800000;
  float:left;
  border-right:1px solid #ea8484;
  padding: 0 3px 0 3px;}

div.data_grid_foot {
  float:left;
  height:16px;
  text-align:center;
  border-top:1px solid #ea8484;
  border-bottom:1px solid #ea8484;
  border-left:1px solid #ea8484;
  background:#fffbfb;
  margin-bottom:15px;}

a:link {
  font-size:11px;
  font-weight:700 !important;}

a:visited {
  font-weight:700 !important;}  

div.limit {margin:5px 0;}
div.counter {margin:5px 0;}
a.pagenav {margin:5px 0;}
</style>

<div class="componentheading">Category: <?php echo $sCategory_name; ?></div>

<?php
$uri =& JFactory::getURI();
$item_id = $uri->get('Itemid');
echo'<br /><div><strong>$uri:</strong> '.print_r($uri, true).'</div><br />';
echo'<div><strong>$item_id:</strong? '.print_r($item_id, true).'</div><br />';
echo'<div><strong>$category_id:</strong? '.print_r($category_id, true).'</div>';
?>

<div class="data_grid_head">
  <div class="data_grid_element" style="width:260px">Organization</div>
  <div class="data_grid_element" style="width:140px">Category</div>
  <div class="data_grid_element" style="width:120px">City</div>
  <div class="data_grid_element" style="width:61px">Telephone</div>
</div>


The BODY Section:
<div class="data_grid_row">
  <div class="data_grid_element" style="width:260px;font-weight:700;"><a href="index.php?option=com_k2&view=item&id={fki_item_ID}:{sItem_title}&Itemid={menu_itemid}">{sItem_title}</a></div>
  <div class="data_grid_element" style="width:140px">{sCategory_name}</div>
  <div class="data_grid_element" style="width:120px">{sCity}</div>
  <div class="data_grid_element" style="width:61px">{sTelephone}</div>
</div>


The FOOTER Section:
<div class="data_grid_foot">
  <div class="data_grid_element" style="width:602px; background:#fffbfb">-- End of results --</div>
</div>
<br />
<div class="data_grid_foot" style="height:65px">
  <div class="data_grid_element" style="width:602px;height:65px;background:#fffbfb">{pagination}</div>
</div>


I can post a screen grab to show the results as soon as I can work out how to include an image.

Here is the Text that is rendered before the list:

$uri: JURI Object ( [_uri] => http://localhost/islagosb1a/index.php?option=com_chronoconnectivity&Itemid=82 [_scheme] => http [_host] => localhost [_port] => [_user] => [_pass] => [_path] => /islagosb1a/index.php [_query] => option=com_chronoconnectivity&Itemid=82 [_fragment] => [_vars] => Array ( [option] => com_chronoconnectivity [Itemid] => 82 ) [_errors] => Array ( ) )

$item_id:

$category_id:
Joe Stokes 28 Oct, 2010
Hi GreyHead,

I found a solution. It is broadly based on stripping out the Itemid as a substring from the URI.
I used this method:

$uri =& JFactory::getURI();
$query_set = $uri->getQuery();
$item = explode("=",$query_set);
$menu_itemid = $item[2];


It may not be very "clean", but for now it works

I will post the full solution here later once I had tested all aspects.

Regards

Joe
craigmartin 27 Nov, 2011
This hard coded "page_id = 5" displays the results I need but I need page_id to be equal to the php variable $pid = JRequest::getString('id');

I tried several ways like:
[list]page_id = $pid[/list]
[list]page_id = ($pid).[/list]
[list]page_id = '$pid'[/list]
[list]page_id = '.$pid.'[/list]
[list]page_id = ".$pid."[/list]
[list]page_id = ".$pid"[/list]
[list]page_id = '.$pid"[/list]

Anyone have any suggestions?
Thank you.

<?php
$pid = JRequest::getString('id');

$video_url = "SELECT youtube_url 
             FROM jml_chronoforms_data_addvideo 
             WHERE page_id = 5  
             ORDER BY cf_id DESC 
             LIMIT 1, 30" ;
$db =&JFactory::getDBO();
$db->setQuery ($video_url);
$url = $db->loadResult();
echo $url;
?>
GreyHead 28 Nov, 2011
Hi CraigMartin,

This is the correct one of these provided that $pid is an integer.
page_id = ".$pid."
I would write the code like this using {$pid} instead.
<?php
$pid = JRequest::getInt('id', '', 'get');

$query = "
  SELECT `youtube_url`
    FROM `#__chronoforms_data_addvideo`
    WHERE `page_id` = '{$pid}' 
    ORDER BY `cf_id` DESC
    LIMIT 1, 30;
";
$db =&JFactory::getDBO();
$db->setQuery($query);
$url = $db->loadResult();
echo $url;
?>

Bob
craigmartin 30 Nov, 2011
:D Thanks Bob. That did the trick.
I'm gonna but you a well deserved beer now.

Craig
craigmartin 02 Dec, 2011
Moving forward:

Cant seem to get this to work. The variable does echo the url.
What is the correct way to use the variable inside the iframe?

$url = $db->loadResult();

<iframe src="<?php echo $url; ?>"width="450" height="259" frameborder="0" allowfullscreen></iframe>
GreyHead 17 Dec, 2011
Hi Craig,

Where is the iFrame code being used? You may need to set global $url; to pass the value between boxes.

Bob
This topic is locked and no more replies can be posted.