1
// CODE1 (Mysql (MariaDB 10.8.3) query):

SELECT s.`p_p`, COUNT(REGEXP_SUBSTR(r.`xml`, '[0-9]+'))
 FROM `#__s` AS s, `#__p` AS x, `#__v` AS r
 WHERE REGEXP_SUBSTR(r.`xml`, '[0-9]+') = [user_id] AND s.`user_id` = r.`user` AND x.`id` = s.`p_p`
 GROUP BY REGEXP_SUBSTR(r.`xml`, '[0-9]+'), s.`p_p`
 ORDER BY FIELD(x.`id`, 44, 14, 11, 10, 18, 4)

// Note1: works fine
// CODE2 (phpMyAdmin (5.2.0) query convertion to PHP (7.4.30)):
$query = "SELECT s.`p_p`, COUNT(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))
 FROM `#__s` AS s, `#__p` AS x, `#__v` AS r
 WHERE REGEXP_SUBSTR(r.`xml`, \'[0-9]+\') = [user_id] AND s.`user_id` = r.`user` AND x.`id` = s.`p_p`
 GROUP BY REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'), s.`p_p`
 ORDER BY FIELD(x.`id`, 44, 14, 11, 10, 18, 4);"; 
 
// Note2: Having phpMyAdmin to convert CODE1 to php it gives CODE2. Only \ was added and made it not to work. Taking \ out, works just fine as it is exactly the same as CODE1 which also works.
// CODE3 (PHP (7.4.30) query in Joomla (3.10.11) syntax):
$query = $db
    ->getQuery(true)
    ->select($db->quoteName('s.p_p'), $db->quoteName('COUNT(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))'))
    ->from($db->quoteName('#__s', 's'), $db->quoteName('#__p', 'x'), $db->quoteName('#__v', 'r'))
    ->where($db->quoteName('(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))') .' = [user_id]' AND $db->quoteName('s.user_id') .' = '. $db->quoteName('r.user') AND $db->quoteName('x.id') .' = '. $db->quoteName('s.p_p'))
    ->group($db->quoteName('(REGEXP_SUBSTR(r.`xml`, \'[0-9]+\'))'), $db->quoteName('s.p_p'));
    
// Note3: In an effort to convert CODE1 to Joomla syntax, I can't make it work (even without the \). Using try/catch throws an error in line ->group. I also need to add the ORDER BY FIELD(x.`id`, 44, 14, 11, 10, 18, 4) but I don't know how.

1 Answer 1

1

quoteName() is something that you call when you are quote-wrapping identifiers. (REGEXP_SUBSTR(r.xml, \'[0-9]+\')) is not a table name or column name so it makes no sense to try to wrap it in quotes as an identifier.

In fact, I don't see any reason to use ANY of those quoteName() method calls in your script. It only bloats the syntax, and makes your code harder to "eyeball". There isn't any security/stability improvement for calling quoteName() either.

JFactory::getApplication()->enqueueMessage($query->dump(), 'notice'); can prove very helpful as a sanity check when queries are failing.

Ultimately, I'm saying, remove all of those quoteName() calls and those manually written backticks, then try your query again.

P.S. Since where()'s default glue is AND, I'd personally prefer to feed it an array of rules:

->where([
    "REGEXP_SUBSTR(r.xml, '[0-9]+') = [user_id]",
    "s.user_id = r.user",
    "x.id = s.p_p"
])

Err, wait. I don't like old-skool comma joins -- they also trick the developer eye. I recommend using explicit INNER JOINs with ON clauses for clarity. This means you will only have one rule in your WHERE clause (so no array is needed).

3
  • Concerning quoteName() I can now understand, thanks. JFactory::getApplication()->enqueueMessage($query->dump(), 'notice'); doesn't produce any error since it doesn't seem to be a syntax error? I had also checked with link but no warnings appeared. Concerning "...manually written backticks...", they were added by phpMyAdmin together with \ in regex when it converted the query to PHP. I will remove them following what you said. Finally, haven't figured out yet how to use "Order by field" in Joomla syntax.
    – GalanopD
    Aug 24, 2022 at 9:18
  • I don't mean the backslashes " \ ", I mean the backticks " ` ". When you want backticks, use quoteName() instead. $query->dump() isn't meant to show errors, just show you what the rendered SQL looks like. Then you can more easily review the query and/or paste it in your phpMyAdmin. Are the values in the FIELD() function static/known or are they dynamic? Aug 24, 2022 at 9:19
  • CODE2 works fine although not in Joomla syntax. Further on I use loadObjectList and then if(isset to get what I need from the array. Changing everything not the best option for me. I defintely do more than one things wrong when converting it to Joomla syntax. Answering your question, each of the value represents a specific subscription plan, so the plan values do not change. A user though [user_id] might have some none or or all of them and that is why I use if(isset
    – GalanopD
    Aug 24, 2022 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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