Trouble with sql querys

[t0h]

Im trying to modify a display table which is presenting information from columns from my db tables. However one of the columns i need to select contains multiple different field_value which are bound to a feild_id. I only want to display field_value for corresponding feild_id=4 I currently have something like this :-

SELECT field_id, field_value FROM #__eb_field_values WHERE field_id=4 

I have not worked with sql before and i am unsure how to incorporate this query in with the current query without causing errors. 

My current code is in 2 pages. view and query.

// no direct access
defined( '_JEXEC' ) or die ;


jimport( 'joomla.application.component.view');

class EventBookingViewRegistrantList extends JView
{
function display($tpl = null)
{ 
if (!EventBookingHelper::canViewRegistrantList()) {
return ;
}
$config = EventBookingHelper::getConfig() ;
   $this->setLayout('default') ;
$db = & JFactory::getDBO();
$eventId = JRequest::getInt('event_id');
$config = EventBookingHelper::getConfig();
if ($eventId) {
if (isset($config->include_group_billing_in_registrants) && !$config->include_group_billing_in_registrants) 
$sql = 'SELECT * FROM #__eb_registrants WHERE event_id='.$eventId.' AND is_group_billing=0 AND (published=1 OR (payment_method LIKE "os_offline%" AND published != 2)) ORDER BY register_date DESC';
else
$sql = 'SELECT * FROM #__eb_registrants WHERE event_id='.$eventId.' AND (published=1 OR (payment_method LIKE "os_offline%" AND published != 2)) ORDER BY register_date DESC'; 
$db->setQuery($sql) ;
$rows = $db->loadObjectList();
} else { 
$rows = array() ;
}
$sql = 'SELECT * FROM #__eb_events WHERE id='.$eventId;
$db->setQuery($sql);
$event = $db->loadObject() ;
if (strlen(trim($event->custom_field_ids))) {
$fields = explode(',', $event->custom_field_ids);
$fieldTitles = array();
$fieldValues = array();
$sql = 'SELECT id, title FROM #__eb_fields WHERE id IN ('.$event->custom_field_ids.')';
$db->setQuery($sql) ;
$rowFields = $db->loadObjectList();
foreach($rowFields as $rowField) {
$fieldTitles[$rowField->id] = $rowField->title ;
}


$registrantIds = array();
foreach ($rows as $row) {
$registrantIds[] = $row->id ;
}
$sql = 'SELECT registrant_id, field_id, field_value FROM #__eb_field_values WHERE registrant_id IN ('.implode(',', $registrantIds).')';
$db->setQuery($sql);
$rowFields = $db->loadObjectList();
foreach ($rowFields as $rowField) {
$fieldValues[$rowField->registrant_id][$rowField->field_id] = $rowField->field_value ;
}
$this->assignRef('fieldTitles', $fieldTitles) ;
$this->assignRef('fieldValues', $fieldValues) ;
$this->assignRef('fields', $fields) ;
$displayCustomField = true ;
} else {
$displayCustomField = false ;
}


$this->assignRef('items', $rows) ;
$this->assignRef('config', $config) ;
$this->assignRef('displayCustomField', $displayCustomField) ;


parent::display($tpl); 
}

second
 

<?php

// no direct access
defined( '_JEXEC' ) or die ;


if (version_compare(JVERSION, '1.6.0', 'ge')) {
    $param = null ;
?>
<h1 class="eb_title"><?php echo JText::_('EB_REGISTRANT_LIST'); ?></h1>
<?php    
} else {
?>
<div class="componentheading"><?php echo JText::_('EB_REGISTRANT_LIST'); ?></div>   
<?php    
    $param = 0 ;
}
if (count($this->items)) {
?> 
<table width="100%">
<thead>
<tr>
<td width="5" class="sectiontableheader">
<?php echo JText::_( 'ID' ); ?>
</td> 
<td class="sectiontableheader">
<?php echo JText::_('EB_FIRST_NAME'); ?>
</td>
<td class="sectiontableheader"> 
<?php echo JText::_('EB_LAST_NAME'); ?>
</td> 
<td class="sectiontableheader">
<?php echo JText::_('EB_EMAIL'); ?>
</td> 
<td class="sectiontableheader">
<?php echo JText::_('Phone'); ?>
</td>
<?php
if ($this->displayCustomField) {
foreach($this->fields as $fieldId) {
?>
<td class="sectiontableheader">
<?php echo $this->fieldTitles[$fieldId] ; ?>
</td>
<?php 
} 
}
?>
</tr>
</thead> 
<tbody>
<?php
$k = 0;
$tabs = array('sectiontableentry1', 'sectiontableentry2') ;
for ($i=0, $n=count( $this->items ); $i < $n; $i++)
{
$row = &$this->items[$i]; 
$tab = $tabs[$k] ; 
?>
<tr class="<?php echo $tab; ?>">
<td>
<?php echo $row->id ; ?>
</td> 
<td> 
<?php echo $row->first_name ; ?> 
</td> 
<td>
<?php echo $row->field_value ; ?>
</td>
<td>
<a href="mailto:<?php echo $person['Person']['primEmail']; ?>"><?php echo $row->email ; ?></a>
</td> 
<td>
<?php echo $row->phone ;?>
</td> 
<?php
if ($this->displayCustomField) {
foreach($this->fields as $fieldId) {
?>
<td class="sectiontableheader">
<?php echo $this->fieldValues[$row->id][$fieldId] ; ?>
</td>
<?php 
} 
}
?> 
</tr>
<?php
$k = 1 - $k;
}
?>
</tbody>
</table> 
<?php 
} else {
?>
<div align="center" class="info"><?php echo JText::_('EB_NO_REGISTRATION_RECORDS');?></div>
<?php 
}
?>

Any help at all would be useful as i am yet to find an example i can dissect. 

[Ch0cu3r]

Need more info.

 

When/Where do you want the sql query to run? What is your script doing at them moment, what do you want it to do? What steps have you taken to implement this yourself

[t0h]

it is currently working to display a table of registrants in a particular group.  The Registrants details are held in eb_registrants and therefor the majority of information is in raw columns. however the plugin that this is part of has an option to store custom fields. It does by storing a feild_id against a field_value and corresponding user id. i need to select the field_values table but only display the information where the field_id=4. but i need to incorporate this into the current code. would a screenshot of the php table and structure help?

[Ch0cu3r]

The only query I see using the #__eb_field_values table is this query

$sql = 'SELECT registrant_id, field_id, field_value FROM #__eb_field_values WHERE registrant_id IN ('.implode(',', $registrantIds).')';

Not sure but add " field_id=4 AND " (without the quotes) before registrant_id IN

[t0h]

I have tried this to no avail. Here is a more detailed view of the tables I'm working with. 

This is the EB_fields table where an ID relates to a field. the one i want is organisation or id=4

This is the eb field_values table where the id in the eb_field table corresponds here with the field_id. I need to display the field_value but only when field_id=4 in order to show the value for the organisation.

This is the main registrants table. the id here corresponds to the registrant_id in the above table. meaning that i have to link the id and registrant_id in order to show the relavent field_value for the relavent id.

Please if anyone can help me. Ive spent days on this and i can not get my head round it.

Thanks

[Ch0cu3r]

It appears it loads the custom fields/values from eb_fields and eb_field_values tables dynamically. How it retrieves the custom field/values is whatever is set in the custom_field_ids column within the eb_events table that corresponds to the current event_id (this is retrieved from JRequest::getInt('event_id')).

 

So maybe add your custom field id to the custom_field_ids column in the eb_events table that corresponds to the current event_id.

Edited October 1, 2013 by Ch0cu3r