hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : ahhhhhh why won't this sql query work
Reply

Forum Jump

ahhhhhh why won't this sql query work

Reply Post New Thread In Programming Discussion Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 04-22-2013, 02:22 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727

ahhhhhh why won't this sql query work


$sql = "SELECT * FROM members, affiliates WHERE (members.commission>=$minbal AND members.payment_method='PayPal') OR (affiliates.commission>=$minbal AND affiliates.payment_method='PayPal') ORDER BY COALESCE(members.affiliateinfo1,affiliates.affiliateinfo1) limit $limit";

mysql
commission is varchar and has one entry in the member table of 99

__________________
Quality web hosting PullHost.com



Sponsored Links
  #2  
Old 04-22-2013, 02:37 AM
TwineDev TwineDev is offline
Web Hosting Guru
 
Join Date: May 2011
Location: Columbus, Ohio
Posts: 260
What error do you get?

__________________
This signature intentionally left blank.


  #3  
Old 04-22-2013, 02:43 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
None, that's the problem.
$minbal=0.01
There is definitely one row of members with commission of 99 and payment method PayPal

__________________
Quality web hosting PullHost.com


Last edited by Research Names; 04-22-2013 at 02:50 AM.
Sponsored Links
  #4  
Old 04-22-2013, 03:20 AM
foobic foobic is offline
Community Liaison 2.0
 
Join Date: Feb 2005
Location: Australia
Posts: 5,546
Don't know if it's your problem but:
Quote:
Originally Posted by Research Names View Post
commission is varchar
Really?! Are you perhaps doing something like a string comparison of "$99" vs "0.01"?

__________________
Chris

"An economist is an expert who will know tomorrow why the things he predicted yesterday didn't happen today." - Laurence J. Peter

  #5  
Old 04-22-2013, 03:24 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
Quote:
Originally Posted by foobic View Post
Don't know if it's your problem but:

Really?! Are you perhaps doing something like a string comparison of "$99" vs "0.01"?
No it's a straight 99

__________________
Quality web hosting PullHost.com

  #6  
Old 04-22-2013, 03:39 AM
TwineDev TwineDev is offline
Web Hosting Guru
 
Join Date: May 2011
Location: Columbus, Ohio
Posts: 260
First step, check actual errors. You already confirmed you are not getting any errors.

Second step, echo out the actual SQL statement, sometimes when using variables in the middle, you miss something that becomes clear when you see the full statement with all the data.

Third step, break it up to separate selects to make sure you are getting at least one record on one or the other tables.
Code:
SELECT * FROM members WHERE commission>=$minbal AND payment_method='PayPal'

SELECT * FROM affiliates WHERE commission>=$minbal AND payment_method='PayPal'
Make sure at least one of those return a value

If so, then it is a matter of how you are doing the join. (as the other issue of that possibly $limit isn't at least one, you should see when you echo out the actual SQL statement.

__________________
This signature intentionally left blank.



Last edited by TwineDev; 04-22-2013 at 03:41 AM. Reason: typo editing
  #7  
Old 04-22-2013, 03:52 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
echoed query returns SELECT * FROM members, affiliates WHERE (members.commission>=0.01 AND members.payment_method='PayPal') OR (affiliates.commission>=0.01 AND affiliates.payment_method='PayPal') ORDER BY COALESCE(members.affiliateinfo1,affiliates.affiliateinfo1) limit 1000
Does work with just members
So join rewrite suggestions?
I appreciate the help

__________________
Quality web hosting PullHost.com

  #8  
Old 04-22-2013, 04:03 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
oh with the new join, I also need this code to work

if (array_key_exists('download_status', $myrow)) {
$file = 'members.php';
$payment_account = $myrow["affiliateinfo1"];
$type = '2';
} else {
$file = 'affiliates.php';
$payment_account = $myrow["affiliateinfo1"];
$type = '1';
}

__________________
Quality web hosting PullHost.com

  #9  
Old 04-22-2013, 04:06 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
would temporary tables be better?

__________________
Quality web hosting PullHost.com

  #10  
Old 04-22-2013, 06:53 AM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
SELECT members.*, affiliates.* FROM
Doesn't work either
cross join definitely doesn't work
Is the answer outer join? I don't know how to do that without putting it ON something.

__________________
Quality web hosting PullHost.com

  #11  
Old 04-22-2013, 02:07 PM
scott_m scott_m is offline
Junior Guru Wannabe
 
Join Date: Jan 2013
Posts: 50
Hello Research_Names,

The issue is due to the fact that the affiliates table is empty (if I read your initial question correctly).

The original code will find data for members but none for affiliates, and return 0 rows. If you had it reversed where the members table had 0 rows and the affiliates table has at least one row, you still get 0 rows returned.

As long as both tables have a row of data, you will get results. Note, though that there are no joins so you will get repeating data for both tables. (2 rows in affiliates and 2 rows in members will result in 4 rows of data)

It looks like the query is not quite finished as far as producing legitimate results, but at least you know why it is giving no data so far.

__________________
Scott M
InMotion Hosting Customer Community Team


  #12  
Old 04-22-2013, 05:57 PM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
Interesting, yet very annoying. maybe I will just put a note saying you need to have both members and affiliates for the "both" feature to work. No workaround I take it?
Thanks for the information!

__________________
Quality web hosting PullHost.com

  #13  
Old 04-22-2013, 06:33 PM
souhil souhil is offline
WHT Addict
 
Join Date: Dec 2011
Posts: 135
you need a left outer join to list all records

__________________
OISSite.com
VMware Cloud Hosting, VSphere 5.1, High Availability, Clustering, Disaster Recovery Solutions, VMware Dedicated and Managed Servers, Linux and Windows VPS, Windows 2012, SQL 2014, cPanel.

  #14  
Old 04-22-2013, 09:51 PM
Research Names Research Names is offline
Web Hosting Master
 
Join Date: Nov 2002
Posts: 727
tried a left outer join on 1=1, no luck.

__________________
Quality web hosting PullHost.com

  #15  
Old 04-22-2013, 10:44 PM
souhil souhil is offline
WHT Addict
 
Join Date: Dec 2011
Posts: 135
can you send me the full query with the left join ?

__________________
OISSite.com
VMware Cloud Hosting, VSphere 5.1, High Availability, Clustering, Disaster Recovery Solutions, VMware Dedicated and Managed Servers, Linux and Windows VPS, Windows 2012, SQL 2014, cPanel.

Reply

Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query help strikeboy Programming Discussion 7 10-03-2008 09:55 PM
sql query conanqtran Hosting Security and Technology 4 01-25-2006 07:26 PM
How to do this SQL query? innova Programming Discussion 7 01-03-2006 06:14 PM
Help with sql query innova Programming Discussion 5 07-14-2005 02:24 PM
can you do this sql query ? DeX Programming Discussion 2 06-15-2004 04:56 PM

Related posts from TheWhir.com
Title Type Date Posted
Three Tips to Reduce Meetings, Work Hours, and Be as Productive as Ever Blog 2013-11-01 15:18:39
Pace Work Technologies Launches Upgraded Web Hosting Services Web Hosting News 2013-07-03 15:41:09
April Fools Day 2013 Web Hosting Round-Up Blog 2013-04-01 12:34:54
Alert Logic Releases New Version of Security and Compliance Solution Web Hosting News 2013-02-19 15:55:16
Web Host PEER 1 Named Top Workplace in UK Web Hosting News 2012-05-31 16:10:42


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

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 Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?