4

I built a Joomla component for a school using component-creator.com and now I am trying to modify the source files.

My current view lists off all parents and students in my database, visible to all registered users. I am trying to modify the SQL query in models/students.php to only show the students associated with the currently logged in user. I tried to do this by modifying the where clause, but I keep getting a syntax error.

Here is the original code of models/students.php, which displays all students and parents to anybody with permission to view the form.

public function getItems()
    {
        $items = parent::getItems();

        foreach ($items as $item)
        {
            if (isset($item->parent) && $item->parent != '')
            {
                if (is_object($item->parent))
                {
                    $item->parent = \Joomla\Utilities\ArrayHelper::fromObject($item->parent);
                }

                $values = (is_array($item->parent)) ? $item->parent : explode(',', $item->parent);
                $textValue = array();

                foreach ($values as $value)
                {
                    $db = JFactory::getDbo();
                    $query = $db->getQuery(true);
                    $query
                            ->select('`#__users_2679886`.`name`')
                            ->from($db->quoteName('#__users', '#__users_2679886'))
                        ->where($db->quoteName('id') . ' = ' . $db->quote($db->escape($value)));
                    $db->setQuery($query);
                    $results = $db->loadObject();

                    if ($results)
                    {
                        $textValue[] = $results->name;
                    }
                }

                $item->parent = !empty($textValue) ? implode(', ', $textValue) : $item->parent;
            }

        }

        return $items;
    }

And here is the section of the code which I modified, producing a syntax error:

            $db = JFactory::getDbo();
            $user = JFactory::getUser();
            $query = $db->getQuery(true);
            $query
                    ->select('`#__users_2679886`.`name`')
                    ->from($db->quoteName('#__users', '#__users_2679886'))
                ->where($db->quoteName('id') . ' = ' . $db->quote($db->escape($value)) AND parent.id = $user->id);
            $db->setQuery($query);
            $results = $db->loadObject();

I simply tried to modify the WHERE cause by adding AND parent.id = $user->id, but the syntax error returned is: syntax error, unexpected '=', expecting ',' or ')'

I am looking for help to modify my specific code as well as an explanation of the WHERE clause, since Joomla's chained SQL syntax is confusing for me and I will need to perform multiple WHERE operations throughout my site design. I have consulted here and here, but I can't seem to find a clear guide showing how to build and chain the WHERE statement.

Any insight is appreciated. Thank you.

5

Try changing your query to the following:

$query->select('#__users_2679886', 'name')
      ->from($db->qN('#__users', '#__users_2679886'))
      ->where($db->qN('id') . ' = ' . $db->q($value)
      ->where($db->qN('parent.id') . ' = ' . $user->id);

The problem was coming from $db->q(parent.id = $user->id).

Other that that, I've made a few tweaks to the query for you.

Just note that you don't need to do $db->quote($db->escape('XXX')), just $db->quote('XXX') is fine.

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.