Jump to content

Trouble with sql querys


t0h

Recommended Posts

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. 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

3.jpg

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.
1.jpg

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.

2.jpg

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

Thanks

Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.