Calc Element query question

[nbradshaw]

Hello

I have a multi-selection dropdown {peg_alert___ospf_area} where the user selects a value or multiple values.

I use the following code to grab the value - this works great. However, I am wanting to concat the output if the users selects more than one value.

DB Structure:
id, peg_site_id, ospg_area

ospf_area values may be 'OSPF Area 01, OSPF Area 02, etc.

Can someone please provide some ideas on how to do this? Do I need to do a %Like% or CONTAINS statement?

$id = '{peg_alert___ospf_area}';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('peg_site_id'))
    ->from('site_ospf')
    ->where('ospf_area = ' . $db->quote($id));
$db->setQuery($query);
$rows = $db->loadObjectList();

$list = array();
foreach ($rows as $row)
{
    $list[] = "<li>" . $row->peg_site_id .  "</li>";
}
return "<ol>" . implode($list) . "</ol>";

 

[nbradshaw]

Any ideas?

 

[nbradshaw]

Friendly bump.

Sent from my A0001 using Tapatalk

 

[troester]

I don't think bumping a thread twice a day will help...

See http://fabrikar.com/forums/index.php?wiki/calculation-element/
for how to get your multiple values.

The query will be something like
WHERE ospf_area IN ('OSPF Area 01', 'OSPF Area 02')

 

[nbradshaw]

Thanks troester.

That is what I was thinking, as well - but I just don't know how to construct this piece for the IN ('OSPF Area 01', 'OSPF Area 02').

->where('ospf_area = ' . $db->quote($id));

 

[troester]

You have to construct the string
('OSPF Area 01', 'OSPF Area 02')
from your dbjoin selections
$myids = implode(',', $foo); //whatever... (I don't know by heart what you will get from a multiselect, try the WIKI examples)

Then e.g.
->where('ospf_area IN (' . $db->quote($myids) . ')');

 

[nbradshaw]

Is there a clever way to view the sql query within fabrik for the ajax calc query? fabrikdebug won't work because the page has already been loaded.

 

[nbradshaw]

I have this...but I just can't figure out how to get it working...any thoughts on how to get it to the final mile would be greatly appreciated

$id = '{peg_alert___ospf_area}';
$myids = implode(',', $id);
$first_names = implode(',', "{peg_alert___ospf_area}");
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('peg_site_id'))
    ->from('site_ospf')
    ->where('ospf_area IN (' . $db->quote($first_names) . ')');
$db->setQuery($query);
$rows = $db->loadObjectList();
//var_dump($id);exit;

$list = array();
foreach ($rows as $row)
{
    $list[] = $row->peg_site_id;
}
return implode(", ",$list);

!

 

[nbradshaw]

When I do a vardump on $id - I get...

string(31) "["OSPF Area 02","OSPF Area 01"]"

Does this help?

 

[cheesegrits]

That's a JSON string, so it can be converted into an array with ...

$ids = json_decode($id);

... then in your query, implode(',', $ids);

-- hugh

 

[nbradshaw]

Thanks cheesegrits!
Here is what I have...I wasn't too sure what you meant by the comment "... then in your query, implode(',', $ids);". I played around with that - but I am obviously still missing something here.

$id = '{peg_alert___ospf_area}';
$ids = json_decode($id);
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('peg_site_id'))
    ->from('site_ospf')
    ->where('ospf_area IN (' . implode(',', $ids) . ')');
$db->setQuery($query);
$rows = $db->loadObjectList();
//var_dump($id);exit;

$list = array();
foreach ($rows as $row)
{
    $list[] = $row->peg_site_id;
}
return implode(", ",$list);

 

[nbradshaw]

Just trying to keep this one on the radar - appreciate all the help so far...I think we are pretty close on this...I can't find any other posts on this same subject. Any help would be greatly appreciated!

 

[troester]

var_dump $id, $ids to see what you get.
You can just
echo $query; to see the query (there's also something like $db->debug - but I can't remember)

http://fabrikar.com/forums/index.php?wiki/calculation-element/#retrieving-data-from-complex-elements

 

[nbradshaw]

Ok - here is what I get:

for echo query, $id, $ids
SELECT peg_site_id FROM site_ospf WHERE ospf_area IN ('')string(51) "["OSPF Area 02","OSPF Area 01"]" NULL

here is the error when I use the:
$foo = FabrikWorker::JSONtoData($data['peg_alert___ospf_area'], true);

Unknown column 'Array' in 'where clause' SQL=SELECT peg_site_id FROM site_ospf WHERE ospf_area IN (Array)

$id = '{peg_alert___ospf_area}';
$ids = json_decode($id);
$foo = FabrikWorker::JSONtoData($data['peg_alert___ospf_area'], true);
//$myids = implode(',', $id);
//$first_names = implode(',', "{peg_alert___ospf_area}");
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('peg_site_id'))
    ->from('site_ospf')
->where('ospf_area IN (' . $db->quote($foo) . ')');
    //->values(implode(',', $db->quote($values)));
$db->setQuery($query);
$rows = $db->loadObjectList();
echo $query;
var_dump($id, $ids,$foo);exit;

$list = array();
foreach ($rows as $row)
{
    $list[] = $row->peg_site_id;
}
return implode(", ",$list);

 

[nbradshaw]

wait...I didn't have my IN statement in that one...

This is what I get with foo now using IN:
I think it is missing the single quotes in the IN statement.

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 'Area 02,OSPF Area 01)' at line 3 SQL=SELECT peg_site_id FROM site_ospf WHERE ospf_area IN (OSPF Area 02,OSPF Area 01)

$id = '{peg_alert___ospf_area}';
$ids = json_decode($id);
$foo = FabrikWorker::JSONtoData($data['peg_alert___ospf_area'], true);
//$myids = implode(',', $id);
//$first_names = implode(',', "{peg_alert___ospf_area}");
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('peg_site_id'))
    ->from('site_ospf')
->where('ospf_area IN (' . implode(',', $foo) . ')');
    //->values(implode(',', $db->quote($values)));
$db->setQuery($query);
$rows = $db->loadObjectList();
echo $query;
var_dump($id, $ids,$foo);exit;

$list = array();
foreach ($rows as $row)
{
    $list[] = $row->peg_site_id;
}
return implode(", ",$list);

 

[nbradshaw]

Okay - got it done!
Thanks troester, cheesegrits!!!

FINAL:

$id = '{peg_alert___ospf_area}';
$ids = json_decode($id);
$foo = FabrikWorker::JSONtoData($data['peg_alert___ospf_area'], true);
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('peg_site_id'))
    ->from('site_ospf')
->where('ospf_area IN ("' . implode('", "', $foo) . '")');

$db->setQuery($query);
$rows = $db->loadObjectList();


$list = array();
foreach ($rows as $row)
{
    $list[] = $row->peg_site_id;
}
return implode(", ",$list);