Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
  #1  
Old 01-21-2007, 02:38 AM
Itworx4me's Avatar
Itworx4me Itworx4me is offline
 
Join Date: Feb 2002
Posts: 210
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default 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
Reply With Quote
  #2  
Old 01-21-2007, 03:30 AM
JMH11788 JMH11788 is offline
 
Join Date: Jan 2005
Location: Ohio
Posts: 83
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by Itworx4me View Post
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.
Reply With Quote
  #3  
Old 01-21-2007, 03:44 AM
Adrian Schneider's Avatar
Adrian Schneider Adrian Schneider is offline
 
Join Date: Jul 2004
Posts: 2,528
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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
Reply With Quote
  #4  
Old 01-21-2007, 04:06 AM
Itworx4me's Avatar
Itworx4me Itworx4me is offline
 
Join Date: Feb 2002
Posts: 210
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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
Reply With Quote
  #5  
Old 01-21-2007, 04:54 AM
Adrian Schneider's Avatar
Adrian Schneider Adrian Schneider is offline
 
Join Date: Jul 2004
Posts: 2,528
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Hm... MySQL version? Try "substring" instead of "substr".
Reply With Quote
  #6  
Old 01-21-2007, 06:02 AM
Itworx4me's Avatar
Itworx4me Itworx4me is offline
 
Join Date: Feb 2002
Posts: 210
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Thanks for the code SirAdrain. It worked.

Thanks,
Itworx4me
Reply With Quote
  #7  
Old 01-22-2007, 01:04 AM
JMH11788 JMH11788 is offline
 
Join Date: Jan 2005
Location: Ohio
Posts: 83
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 01:49 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03986 seconds
  • Memory Usage 2,224KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_code
  • (3)bbcode_php
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (7)post_thanks_box
  • (7)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (7)post_thanks_postbit_info
  • (7)postbit
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete