Display a Value from a Specific DB Field

Question

I'm trying to figure out what I've got going on here thats not working - I'm using RsTicketsPro and I'm trying to pull a specific value from their custom fields section in the database. I reached out to their support and this is what they recommended:

$db = JFactory::getDBO();
$db->setQuery("SELECT `value` FROM #__rsticketspro_custom_fields_values WHERE `ticket_id`='".$item->id."' AND `custom_field_id`=7");
echo $db->loadResult();

Ok so I adjusted their code to pop this into place in a table to display this specific value:

<td align="center" class="rst_cell_assigned center">
<?php
$db = JFactory::getDBO();
$db->setQuery("SELECT 'value' FROM #__rsticketspro_custom_fields_values WHERE 'ticket_id'='".$item->id."' AND 'custom_field_id'=4");
echo $db->loadResult();
?>
</td>

I updated the custom field id value to match with the corresponding field I want to pull from my database, but when I view it on the front end, it just displays blank - no error message or anything.

Anybody spot anything I'm doing wrong?

EDIT: CURRENT CODE IM WORKING WITH

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(`value`);
$query->from($db->quoteName('#__rsticketspro_custom_fields_values'));
$query->where($db->quoteName(`ticket_id`)." = ".$db->quote('".$item->id."' AND `custom_field_id`=4"));
$db->setQuery($query);
$result = $db->loadResult();
echo $query;
echo $db->getErrorMsg();
?>

note so this one now does not even load the page any longer, haha. #fail

Answer 1

If this is a direct copy paste, you have a mysql error:

SELECT 'value' FROM #__rsticketspro_custom_fields_values WHERE 'ticket_id'='".$item->id."' AND 'custom_field_id'=4

'value' and 'ticket_id' should have back ticks not quotes around it.

I suspect if you ran echo $db->getErrorMsg(); right after echo $db->loadResult();, it would tell you there is a MySql error.

On the 2nd part where you use the query wrapper, can you post the output of echo $query;?

Answer 2

I suspect the issue is due to your select statement missing quoteName and a malformed where statement

you should also use chaining (and qn alias for quoteName q for quote)

This should be the right way to do queries using the Joomla query builder syntax

$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select($db->qn('value'))
      ->from($db->qn('#__rsticketspro_custom_fields_values'))
      ->where($db->qn('ticket_id') . " = " . $db->q($item->id))
      ->where($db->qn('custom_field_id') . " = " . $db->q(4));
$db->setQuery($query);

$result = $db->loadResult();

• See Selecting data using JDatabase
• See Inserting, Updating and Removing data using JDatabase
• See Joomla coding standards (which includes a small part on best practices for JDatabase queries in the PHP section)

Answer 3

Ahhh! I got it!!! I went back to the original code and was able to get it to work properly!

<?php
$db = JFactory::getDBO();
$db->setQuery("SELECT `value` FROM #__rsticketspro_custom_fields_values WHERE `ticket_id`='".$item->id."' AND `custom_field_id`=4");
$result = $db->loadResult();
echo $db->loadResult();
?>

Answer 4

Joomla's PHP Coding Standards say:

Table names and table column names should always be enclosed in the quoteName() method to escape the table name and table columns. Field values checked in a query should always be enclosed in the quote() method to escape the value before passing it to the database. Integer field values checked in a query should also be type cast to (int).

Toeing this line too often results in ugly and bloated code, while unnecessarily calling quoting methods.

Because none of the table or column names actually need quoting, and you should cast integer variables as integer type before writting them into the query, this is what I would recommend...

*note value is a MySQL keyword, but not a Reserved Keyword, so it doesn't require backticking. https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-V

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select("value")
    ->from("#__rsticketspro_custom_fields_values")
    ->where([
        "ticket_id = " . (int)$item->id,
        "custom_field_id = 4"
    ]);
$db->setQuery($query);
$result = $db->loadResult();

• the WHERE clause is happy to receive an array of conditions
• method chaining can start from the getQuery() method to avoid writing $query at the start of each line
• there is no point in quoting or casting the static value 4

The above snippet is as secure as it can be written while using Joomla's query building methods without needless quoting calls.