PHP SQL Sorting Topic is solved

General questions relating to Joomla! 3.x.

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
Locked
G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

PHP SQL Sorting

Post by G0HXT » Sun Sep 16, 2018 9:00 pm

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?

User avatar
AMurray
Joomla! Exemplar
Joomla! Exemplar
Posts: 9636
Joined: Sat Feb 13, 2010 7:35 am
Location: Australia

Re: PHP SQL Sorting

Post by AMurray » Sun Sep 16, 2018 9:16 pm

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.
Regards - A Murray
General Support Moderator

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: PHP SQL Sorting

Post by toivo » Sun Sep 16, 2018 10:48 pm

This SQL statement shows an example of sorting by substring:

Code: Select all

SELECT substring_index(name, ' ', -1) AS surname, name, username, email
FROM la1fb_users
ORDER BY surname
The statement can then be written in PHP:

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

G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

Re: PHP SQL Sorting

Post by G0HXT » Mon Sep 17, 2018 8:13 am

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.

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: PHP SQL Sorting

Post by toivo » Mon Sep 17, 2018 8:38 am

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.
Toivo Talikka, Global Moderator

User avatar
lefteriskavadas
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 101
Joined: Fri Feb 05, 2016 6:10 pm
Location: Athens, Greece
Contact:

Re: PHP SQL Sorting

Post by lefteriskavadas » Mon Sep 17, 2018 9:17 am

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
Joomla Extensions https://www.firecoders.com/

annahersh
Joomla! Guru
Joomla! Guru
Posts: 734
Joined: Wed Aug 15, 2018 8:23 pm

Re: PHP SQL Sorting

Post by annahersh » Mon Sep 17, 2018 10:05 am

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.

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
');
HTML display

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

G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

Re: PHP SQL Sorting

Post by G0HXT » Mon Sep 17, 2018 10:32 am

Thanks everyone, I will have a play.

G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

Re: PHP SQL Sorting

Post by G0HXT » Mon Sep 17, 2018 8:09 pm

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 ')'

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: PHP SQL Sorting

Post by toivo » Mon Sep 17, 2018 8:15 pm

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

G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

Re: PHP SQL Sorting

Post by G0HXT » Mon Sep 17, 2018 8:34 pm

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 have tried replacing \' with "

I'm wondering if I have an incompatibility as I'm using Jumi to run this in Joomla.

Thanks for your time.

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: PHP SQL Sorting

Post by toivo » Tue Sep 18, 2018 10:35 am

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?
Toivo Talikka, Global Moderator

G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

Re: PHP SQL Sorting

Post by G0HXT » Tue Sep 18, 2018 10:47 am

Hi again, thanks for your time.

Joomla 3.8.12, PHP 7.2.9, Jumi 3.0.5 (component)

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: PHP SQL Sorting

Post by toivo » Tue Sep 18, 2018 11:50 am

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:

Code: Select all

$query->select('substring_index(name, " ", -1) AS surname, name, username, email')
or

Code: Select all

$query->select('substring_index(name, CHAR(32), -1) AS surname, name, username, email')
Toivo Talikka, Global Moderator

G0HXT
Joomla! Apprentice
Joomla! Apprentice
Posts: 22
Joined: Sat Jul 29, 2017 3:55 pm

Re: PHP SQL Sorting

Post by G0HXT » Tue Sep 18, 2018 1:08 pm

Amazing, working now. Thanks for your help, got me out of a big problem.

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17350
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: PHP SQL Sorting

Post by toivo » Tue Sep 18, 2018 1:42 pm

Good to hear. It was quite a puzzle indeed.
Toivo Talikka, Global Moderator


Locked

Return to “General Questions/New to Joomla! 3.x”