1

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

Table being used

  • Have you echoed $item->id to make sure that it exists as a value? Have you looked in the database manually to make sure that there is a result? Whilst it should work to just whack the full query into $db->setQuery, the lack of escaping variables would make me nervous - look at docs.joomla.org/Selecting_data_using_JDatabase for tider ways to create a query string. – Richard B Apr 29 '16 at 18:25
  • Just edited my original post to include my new code that I worked out through that page you recommended, however the page wont even load now using this - anything look out of place to you or anyone else? – Brandon Apr 29 '16 at 22:55
  • When you say a "lack of escaping variables" what do you mean exactly? Closing the database string? Here is my final code - does this look ok? or would I be at risk? *See above for the code - couldnt figure out how to format it correctly on a comment. (new to the site here) – Brandon Apr 29 '16 at 23:27
  • I meant escaping strings - docs.joomla.org/Secure_coding_guidelines#Secure_strings - ie wrapping the $db->quote() around the variable. It usually isn't going to matter, but it's good practise to always do as it prevents possible sql injections. – Richard B Apr 30 '16 at 0:18
2

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

  • Updated the code again correcting the back ticks and added the error message echo but nothing is loading still. (you can see it above again) – Brandon Apr 29 '16 at 23:18
  • How close was I? – David Hayes Apr 29 '16 at 23:56
  • You definitely got me going back to the original code that they sent over to me to have another look at it. For some reason my brain never went to the back ticks - is that only when we're referencing a column specifically? – Brandon Apr 30 '16 at 0:03
  • 1
    Backtick are for referencing a column name. Quote are for columm values. – David Hayes Apr 30 '16 at 0:11
  • 1
    David, you really shouldn't do "raw" sql through jdatabase, you should use the query builder and quoting to keep things secure and abstracted. See Selecting data using JDatabase docs.joomla.org/Selecting_data_using_JDatabase – Walt Sorensen May 1 '16 at 4:06
4

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();
1

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();
?>
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.