Database Query Question

[Itworx4me]

Hello,

I have people sign up using there Real Name on my forum. I am using this code to pull some info from the database.

$users = $db->query_read("SELECT userid, username FROM " . TABLE_PREFIX . "user WHERE usergroupid IN (6,9) ORDER BY username ASC ");

Is there a way to code it so that it sorts by there last name instead of the first name?

Thanks,
Itworx4me

[JMH11788]

Hello,

I have people sign up using there Real Name on my forum. I am using this code to pull some info from the database.

$users = $db->query_read("SELECT userid, username FROM " . TABLE_PREFIX . "user WHERE usergroupid IN (6,9) ORDER BY username ASC ");

Is there a way to code it so that it sorts by there last name instead of the first name?

Thanks,
Itworx4me

If they register their username as "John Doe" and it dumps it to the username column in the users table, there is no way that I know of to do what you are looking to do. I bet you could do it with a php loop, but I don't think any mysql query will do that.

As for a php loop, you could have it slit the username into two arrays at the space and return an order like that. Honestly I don't know the details on coding something like that myself, but I would think it can be done.

Probably not worth the work unless it's EXTREMELY important.

[Adrian Schneider]

Try this, SELECT
userid,
username,
substr(username FROM locate(' ', username) + 1) as lastname
FROM user
WHERE usergroupid IN (6,9)
ORDER BY lastname ASC

[Itworx4me]

ok I tried this code:

$users = $db->query_read("SELECT userid, username, substr(username FROM locate(' ', username) + 1) as lastname FROM user WHERE usergroupid IN (6,9) ORDER BY lastname ASC ");


Gave me this error:

Database error in vBulletin 3.6.4:

Invalid SQL:
SELECT userid, username, substr(username FROM locate(' ', username) + 1) as lastname FROM user WHERE usergroupid IN (6,9) ORDER BY lastname ASC;

MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(username FROM locate(' ', username) + 1) as lastname FROM user
Error Number : 1064


Thanks,
Itworx4me

[Adrian Schneider]

Hm... MySQL version? Try "substring" instead of "substr".

[Itworx4me]

Thanks for the code SirAdrain. It worked.

Thanks,
Itworx4me

[JMH11788]

very nice man, I did not know you could do that!