Results 1 to 22 of 22
  1. #1

    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

  2. #2
    Join Date
    May 2011
    Location
    Columbus, Ohio
    Posts
    270
    What error do you get?
    This signature intentionally left blank.

  3. #3
    None, that's the problem.
    $minbal=0.01
    There is definitely one row of members with commission of 99 and payment method PayPal
    Last edited by Research Names; 04-22-2013 at 02:50 AM.
    Quality web hosting PullHost.com

  4. #4
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    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

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  5. #5
    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. #6
    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'
    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.
    Last edited by TwineDev; 04-22-2013 at 03:41 AM. Reason: typo editing
    This signature intentionally left blank.

  7. #7
    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. #8
    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. #9
    would temporary tables be better?
    Quality web hosting PullHost.com

  10. #10
    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. #11
    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

  12. #12
    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. #13
    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.

  14. #14
    tried a left outer join on 1=1, no luck.
    Quality web hosting PullHost.com

  15. #15
    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.

  16. #16
    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 AHHHHHH
    Quality web hosting PullHost.com

  17. #17
    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

  18. #18
    I am just going to scrap the multiple query idea and query them separately. Thanks for your replies.
    Quality web hosting PullHost.com

  19. #19
    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.

  20. #20
    $minbal surrounded with quotes in the sql query gets the same result, thanks anyway though
    Quality web hosting PullHost.com

  21. #21
    Join Date
    Mar 2010
    Location
    Dallas
    Posts
    331
    How about a union query in this situation?

  22. #22
    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

  1. sql query help
    By strikeboy in forum Programming Discussion
    Replies: 7
    Last Post: 10-03-2008, 09:55 PM
  2. sql query
    By aussie_dude in forum Hosting Security and Technology
    Replies: 4
    Last Post: 01-25-2006, 07:26 PM
  3. How to do this SQL query?
    By innova in forum Programming Discussion
    Replies: 7
    Last Post: 01-03-2006, 06:14 PM
  4. Help with sql query
    By innova in forum Programming Discussion
    Replies: 5
    Last Post: 07-14-2005, 02:24 PM
  5. can you do this sql query ?
    By DeX in forum Programming Discussion
    Replies: 2
    Last Post: 06-15-2004, 04:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •