Results 1 to 22 of 22
-
04-22-2013, 02:22 AM #1Web 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 99Quality web hosting PullHost.com
-
04-22-2013, 02:37 AM #2Web Hosting Guru
- Join Date
- May 2011
- Location
- Columbus, Ohio
- Posts
- 270
What error do you get?
This signature intentionally left blank.
-
04-22-2013, 02:43 AM #3Web 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 PayPalLast edited by Research Names; 04-22-2013 at 02:50 AM.
Quality web hosting PullHost.com
-
04-22-2013, 03:20 AM #4Retired Moderator
- Join Date
- Feb 2005
- Location
- Australia
- Posts
- 5,849
Chris
"Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter
-
04-22-2013, 03:24 AM #5Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
Quality web hosting PullHost.com
-
04-22-2013, 03:39 AM #6Web Hosting Guru
- Join Date
- May 2011
- Location
- Columbus, Ohio
- Posts
- 270
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'
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.Last edited by TwineDev; 04-22-2013 at 03:41 AM. Reason: typo editing
This signature intentionally left blank.
-
04-22-2013, 03:52 AM #7Web 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 helpQuality web hosting PullHost.com
-
04-22-2013, 04:03 AM #8Web 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
-
04-22-2013, 04:06 AM #9Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
would temporary tables be better?
Quality web hosting PullHost.com
-
04-22-2013, 06:53 AM #10Web 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
-
04-22-2013, 02:07 PM #11WHT Addict
- Join Date
- Jan 2013
- Posts
- 117
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.[B]Scott M
-
04-22-2013, 05:57 PM #12Web 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
-
04-22-2013, 06:33 PM #13WHT Addict
- Join Date
- Dec 2011
- Posts
- 137
you need a left outer join to list all records
OISSite.com
VMware Cloud Hosting, High Availability, Clustering, Backup and Disaster Recovery Solutions, VMware Dedicated and Managed Servers, Linux and Windows VPS, Windows, cPanel.
-
04-22-2013, 09:51 PM #14Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
tried a left outer join on 1=1, no luck.
Quality web hosting PullHost.com
-
04-22-2013, 10:44 PM #15WHT Addict
- Join Date
- Dec 2011
- Posts
- 137
can you send me the full query with the left join ?
OISSite.com
VMware Cloud Hosting, High Availability, Clustering, Backup and Disaster Recovery Solutions, VMware Dedicated and Managed Servers, Linux and Windows VPS, Windows, cPanel.
-
04-22-2013, 11:26 PM #16Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
SELECT * FROM members LEFT OUTER JOIN affiliates on 1=1 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
didn't work so switched back to affiliate with commission 0, now it's only returning the affiliate result despite not meeting the requirements. I repeat AHHHHHHQuality web hosting PullHost.com
-
04-22-2013, 11:44 PM #17Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
also doesn't outer join merge the rows instead of returning each separately? I need to do the later without any merging.
Quality web hosting PullHost.com
-
04-24-2013, 08:18 PM #18Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
I am just going to scrap the multiple query idea and query them separately. Thanks for your replies.
Quality web hosting PullHost.com
-
04-25-2013, 03:24 AM #19Junior Guru Wannabe
- Join Date
- Feb 2013
- Posts
- 46
Put an apostrophe on the $minbal, you will get an error if you will not treat it as a data type.
-
04-25-2013, 03:59 AM #20Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
$minbal surrounded with quotes in the sql query gets the same result, thanks anyway though
Quality web hosting PullHost.com
-
04-25-2013, 07:54 AM #21Web Hosting Guru
- Join Date
- Mar 2010
- Location
- Dallas
- Posts
- 331
How about a union query in this situation?
-
04-25-2013, 10:30 PM #22Web Hosting Master
- Join Date
- Nov 2002
- Posts
- 727
That won't work as the tables have different columns. It may work if i select only the columns absolutely needed, but then I couldn't tell what table they were from for the admin links, or could I with a special select command?
if not I'm fine with giving up and moving on.Quality web hosting PullHost.com
Similar Threads
-
sql query help
By strikeboy in forum Programming DiscussionReplies: 7Last Post: 10-03-2008, 09:55 PM -
sql query
By aussie_dude in forum Hosting Security and TechnologyReplies: 4Last Post: 01-25-2006, 07:26 PM -
How to do this SQL query?
By innova in forum Programming DiscussionReplies: 7Last Post: 01-03-2006, 06:14 PM -
Help with sql query
By innova in forum Programming DiscussionReplies: 5Last Post: 07-14-2005, 02:24 PM -
can you do this sql query ?
By DeX in forum Programming DiscussionReplies: 2Last Post: 06-15-2004, 04:56 PM