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.