vb.org Archive - Database Query Question

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Database Query Question (https://vborg.vbsupport.ru/showthread.php?t=137099)

Itworx4me

01-21-2007 02:38 AM

Database Query Question

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.

PHP Code:


		
			
$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

01-21-2007 03:30 AM

Quote:

Originally Posted by Itworx4me (Post 1163999)

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.

PHP Code:


		
			
$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

01-21-2007 03:44 AM

Try this,

Code:

SELECT

    userid,

    username,

    substr(username FROM locate(' ', username) + 1) as lastname

FROM user

WHERE usergroupid IN (6,9)

ORDER BY lastname ASC

Itworx4me

01-21-2007 04:06 AM

ok I tried this code:

PHP 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:

Code:

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

01-21-2007 04:54 AM

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

Itworx4me

01-21-2007 06:02 AM

Thanks for the code SirAdrain. It worked.

Thanks,
Itworx4me

JMH11788

01-22-2007 01:04 AM

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

All times are GMT. The time now is 11:47 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02450 seconds Memory Usage 1,729KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (3)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: