PHP SQL Sorting Topic is solved
Moderator: General Support Moderators
Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting.
Forum Post Assistant - If you are serious about wanting help, you should use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting.
Forum Post Assistant - If you are serious about wanting help, you should use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
PHP SQL Sorting
Hi everyone
I'm struggling with SQL and sorting. I'm extracting the name and e-mail address from the users table but want to sort by the second word in the name field. I know this is not the best way but simple is good.
Here is what I have:
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select ('*')
->from($db->quoteName('#__users'))
->where($db->quoteName('name') . "!=". $db->quote('Administrator'))
->where($db->quoteName('name') . "!=". $db->quote('Testuser AB'))
->order ('name');
$db->setQuery($query);
$rows = $db->loadObjectList();
echo '<table>';
echo '<tr>';
echo ' <th>Name </th>';
echo ' <th>E-Mail </th>';
echo '</tr>';
foreach($rows as $row) {
echo '<tr><td style="padding-right: 60px">' . $row->name . '</td><td style="padding-right: 60px">' . $row->email . '</td><td>' . $row->phone . '</td></tr>';
}
echo '</table>';
?>
I have been trying to use order by however, no matter what I try this will not work.
Example being
->order by substring_index(name, ' ', -1)
This errors with "by"
Any ideas what I'm doing wrong?
I'm struggling with SQL and sorting. I'm extracting the name and e-mail address from the users table but want to sort by the second word in the name field. I know this is not the best way but simple is good.
Here is what I have:
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select ('*')
->from($db->quoteName('#__users'))
->where($db->quoteName('name') . "!=". $db->quote('Administrator'))
->where($db->quoteName('name') . "!=". $db->quote('Testuser AB'))
->order ('name');
$db->setQuery($query);
$rows = $db->loadObjectList();
echo '<table>';
echo '<tr>';
echo ' <th>Name </th>';
echo ' <th>E-Mail </th>';
echo '</tr>';
foreach($rows as $row) {
echo '<tr><td style="padding-right: 60px">' . $row->name . '</td><td style="padding-right: 60px">' . $row->email . '</td><td>' . $row->phone . '</td></tr>';
}
echo '</table>';
?>
I have been trying to use order by however, no matter what I try this will not work.
Example being
->order by substring_index(name, ' ', -1)
This errors with "by"
Any ideas what I'm doing wrong?
- AMurray
- Joomla! Exemplar
- Posts: 9636
- Joined: Sat Feb 13, 2010 7:35 am
- Location: Australia
Re: PHP SQL Sorting
Perhaps "order by" requires an underscore, i.e. "order_by" ?
I don't know if you're writing the query for a component you're making or just simply trying to extract data but can I suggest something like https://extensions.joomla.org/extension ... l-manager/ that might help with building your queries?
Doing a bit of searching for SQL stuff, I have noticed that the examples I've come across have all the SELECT, FROM, SORT BY etc in capitals. I don't know if that makes any difference - that might be just for readability.
I don't know if you're writing the query for a component you're making or just simply trying to extract data but can I suggest something like https://extensions.joomla.org/extension ... l-manager/ that might help with building your queries?
Doing a bit of searching for SQL stuff, I have noticed that the examples I've come across have all the SELECT, FROM, SORT BY etc in capitals. I don't know if that makes any difference - that might be just for readability.
Regards - A Murray
General Support Moderator
General Support Moderator
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia
Re: PHP SQL Sorting
This SQL statement shows an example of sorting by substring:
The statement can then be written in PHP:
Code: Select all
SELECT substring_index(name, ' ', -1) AS surname, name, username, email
FROM la1fb_users
ORDER BY surname
Code: Select all
$query->select('substring_index(name, \' \', -1) AS surname, name, username, email')
->from($db->quoteName('#__users'))
->where($db->quoteName('name') . ' != ' . $db->quote('Administrator'))
->where($db->quoteName('name') . ' != ' . $db->quote('Testuser AB'))
->order ('surname');
Toivo Talikka, Global Moderator
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
Re: PHP SQL Sorting
Thanks for your time Toivo, I'm getting an error with the php version of your code:
syntax error, unexpected '', -1) AS surname, name, usern' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ')'
This is the same error I have been struggling with other experiments, it seams to have issues with ' characters and I can see you have used \ but this has never worked for me.
Thanks again.
syntax error, unexpected '', -1) AS surname, name, usern' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ')'
This is the same error I have been struggling with other experiments, it seams to have issues with ' characters and I can see you have used \ but this has never worked for me.
Thanks again.
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia
Re: PHP SQL Sorting
The PHP code is valid and it works without problems as it is but try replacing \' with a double quotation mark ".
The PHP syntax error could be caused by a syntax issue before the line that starts with $query->select.
The PHP syntax error could be caused by a syntax issue before the line that starts with $query->select.
Toivo Talikka, Global Moderator
- lefteriskavadas
- Joomla! Enthusiast
- Posts: 101
- Joined: Fri Feb 05, 2016 6:10 pm
- Location: Athens, Greece
- Contact:
Re: PHP SQL Sorting
The code provided should work. However, keep in mind, that if you have a lot of users, this query might make your site go down. The substring_index function will get executed for each one single record in the table.
Regards
Regards
Joomla Extensions https://www.firecoders.com/
-
- Joomla! Guru
- Posts: 734
- Joined: Wed Aug 15, 2018 8:23 pm
Re: PHP SQL Sorting
If you are sifting specific user groups it's best to filter and find the user by ID and not a name since that is subject to change. The following method will scan for users in groups registered, author, editor, publisher. Remove or add as needed.
HTML display
Code: Select all
$db->setQuery('
SELECT SUBSTRING_INDEX(name, " ", 1) AS firstname, SUBSTRING_INDEX(name, " ", -1) AS lastname, email, title
FROM #__users
JOIN #__user_usergroup_map ON #__users.id = #__user_usergroup_map.user_id
JOIN #__usergroups ON #__user_usergroup_map.group_id = #__usergroups.id
WHERE #__user_usergroup_map.group_id IN(3,4,5) AND #__usergroups.id IN(3,4,5)
ORDER BY lastname
');
Code: Select all
echo '<table class="table table-striped">';
echo '<tr><th>Name</th><th>Email</th><th>Group</th></tr>';
foreach($db->loadObjectList() as $row) {
echo '<tr><td>'.$row->lastname.', '.$row->firstname.'</td><td>'.$row->email.'</td><td>'.$row->title.'</td></tr>';
}
echo '</table>';
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
Re: PHP SQL Sorting
Thanks everyone, I will have a play.
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
Re: PHP SQL Sorting
Hi everyone
Try as I might I cannot get round this error
syntax error, unexpected '', -1) AS surname, name, usern' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ')'
Try as I might I cannot get round this error
syntax error, unexpected '', -1) AS surname, name, usern' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ')'
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia
Re: PHP SQL Sorting
Please copy and paste the PHP code, exactly as it is now, and use the CODE tags around it in the post.
Toivo Talikka, Global Moderator
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
Re: PHP SQL Sorting
Code: Select all
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('substring_index(name, \' \', -1) AS surname, name, username, email')
->from($db->quoteName('#__users'))
->where($db->quoteName('name') . ' != ' . $db->quote('Administrator'))
->where($db->quoteName('name') . ' != ' . $db->quote('Testuser AB'))
->order ('surname');
$db->setQuery($query);
$rows = $db->loadObjectList();
echo '<table>';
echo '<tr>';
echo ' <th>Name </th>';
echo ' <th>E-Mail </th>';
echo '</tr>';
foreach($rows as $row) {
echo '<tr><td style="padding-right: 60px">' . $row->name . '</td><td style="padding-right: 60px">' . $row->email . '</td><td>' . $row->phone . '</td></tr>';
}
echo '</table>';
?>
I'm wondering if I have an incompatibility as I'm using Jumi to run this in Joomla.
Thanks for your time.
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia
Re: PHP SQL Sorting
The code you posted works all right when run from a Jumi module.
It also works inside an article and a custom module when run by the Jumi plugin. The obsolete constant DS (directory separator) used in the file plugins/system/jumi/jumi.php has to be replaced with the string '/'.
Test environment: Joomla 3.8.12, PHP 7.2.4, Jumi 3.0.5
Which versions do you have?
It also works inside an article and a custom module when run by the Jumi plugin. The obsolete constant DS (directory separator) used in the file plugins/system/jumi/jumi.php has to be replaced with the string '/'.
Test environment: Joomla 3.8.12, PHP 7.2.4, Jumi 3.0.5
Which versions do you have?
Toivo Talikka, Global Moderator
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
Re: PHP SQL Sorting
Hi again, thanks for your time.
Joomla 3.8.12, PHP 7.2.9, Jumi 3.0.5 (component)
Joomla 3.8.12, PHP 7.2.9, Jumi 3.0.5 (component)
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia
Re: PHP SQL Sorting
When Jumi saves the script from the Custom Script field of the Jumi component to the database table, the backslashes get removed, which causes the syntax error.
Use double quotation marks or the MySQL character function CHAR(32) instead:
or
Use double quotation marks or the MySQL character function CHAR(32) instead:
Code: Select all
$query->select('substring_index(name, " ", -1) AS surname, name, username, email')
Code: Select all
$query->select('substring_index(name, CHAR(32), -1) AS surname, name, username, email')
Toivo Talikka, Global Moderator
-
- Joomla! Apprentice
- Posts: 22
- Joined: Sat Jul 29, 2017 3:55 pm
Re: PHP SQL Sorting
Amazing, working now. Thanks for your help, got me out of a big problem.
- toivo
- Joomla! Master
- Posts: 17350
- Joined: Thu Feb 15, 2007 5:48 am
- Location: Sydney, Australia