Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 01-15-2009, 09:53 AM
noj75 noj75 is offline
 
Join Date: Nov 2004
Posts: 72
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Any ideas on this mysql query?

Hi to all on this great site! I am desperate for some help:

PHP Code:
$samesurn "SELECT * FROM $stb WHERE s_sur IN (SELECT s_sur FROM $stb WHERE s_name='$proname') AND s_name!='$proname' "
I am trying to do the above query:

$proname has a list of values in s_sur. I want to query the database to see who else has the same values and then display the results excluding $proname's own results.

This does work but is TERRIBLY slow. Is there another way to do this?

Any help is greatly appreciated. Thank you.
Reply With Quote
  #2  
Old 01-15-2009, 10:11 AM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Maybe i misread it, but on first sight this doesn'tmake sense at all.

In the sub-query you select all surnames (assumption) that have a first name $proname. With the resulting surnames, you select again from the $stb table, and want to list all with the selected surnames, but who don't have a first name of $proname. That should result in an empty resultset.
Reply With Quote
  #3  
Old 01-15-2009, 10:56 AM
noj75 noj75 is offline
 
Join Date: Nov 2004
Posts: 72
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Hi Marco,

$proname (s_name) is the username of the person who entered the s_sur (surname) records.

I want to query the db to see who else has entered the same (surnames) s_sur as $proname and return the results. But I want all surnames from $proname exluded.

Does that make more sense?
Reply With Quote
  #4  
Old 01-15-2009, 11:25 AM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Sorry but no.

You want to list all surnames that have $proname, but exclude those that have $proname.
Reply With Quote
  #5  
Old 01-15-2009, 11:49 AM
noj75 noj75 is offline
 
Join Date: Nov 2004
Posts: 72
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

LOL,

Basically, I (I am $proname, s_name) have entered numerous surnames into the database (s_sur). I want to see if any other users have entered the same surnames as I have and have the database show the rusults to me but I dont want to see my own entries, only other members entries.

So, I have to do a query to get ALL the surnames I have entered and then put that into another query to see who else has the same surnames.

How about that?

The query DOES work, it's just slow.
Reply With Quote
  #6  
Old 01-15-2009, 12:16 PM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Are there any indexes on these columns?
Reply With Quote
  #7  
Old 01-15-2009, 12:32 PM
noj75 noj75 is offline
 
Join Date: Nov 2004
Posts: 72
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Yes s_sur and s_name

This has speeded it up alot. Could it be faster?
Reply With Quote
  #8  
Old 01-15-2009, 01:28 PM
Guest190829
Guest
 
Posts: n/a
Default

The subquery is probably causing the slow load times and is probably not even necessary if your table is normalized enough.

If you have an id associated with the user who submitted the data, you can just do a simple select using the s_name and s_sur of the user with a conditional to exclude records submitted by that user id.
Reply With Quote
Reply


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 02:41 PM.


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.04240 seconds
  • Memory Usage 2,225KB
  • 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
  • (1)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (8)post_thanks_box
  • (8)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (8)post_thanks_postbit_info
  • (8)postbit
  • (7)postbit_onlinestatus
  • (8)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