Use Itemid from a menu URL as variable in WHERE SQL

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

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

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

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

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:

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

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;
?>

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

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

Craig

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>

Hi Craig,

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

Bob