SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2000
    Location
    Norcross, GA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Strange SELECT anamoly

    $resultescq = mysql_query("SELECT * FROM esculations WHERE esculations.userid = '$userid'",$dbx);
    $checkrow=mysql_numrows($resultescq);

    > Returns 8 records. (accurate)


    $resultescq = mysql_query("SELECT * FROM transfers WHERE transfers.userid = '$userid'",$dbx);
    $checkrow=mysql_numrows($resultescq);

    > Returns 4 records. (accurate)


    $resultescq = mysql_query("SELECT * FROM pending WHERE pending.userid = '$userid'",$dbx);
    $checkrow=mysql_numrows($resultescq);

    > Returns 4 records. (accurate)


    $resultescq = mysql_query("SELECT * FROM pending, transfers WHERE pending.userid = '$userid' && transfers.userid = '$userid'",$dbx);
    $checkrow=mysql_numrows($resultescq);

    > Returns 16 records. (inaccurate)


    $resultescq = mysql_query("SELECT * FROM esculations, pending, transfers WHERE esculations.userid ='$userid' && pending.userid = '$userid' && transfers.userid = '$userid'",$dbx);
    $checkrow=mysql_numrows($resultescq);

    > Returns 128 records. (inaccurate)


    On the last two queries it appears that MySQL is multiplying the number of records from each table times each other. ie, 4x4=16. 4x4x8=128.

    Why is that?

  2. #2
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe it's called a Cartesian Join. I always have that problem because I suck at queries.

    http://www.1keydata.com/sql/sql9.html
    and some more
    http://www.google.com/search?q=carte...&start=10&sa=N
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I am assuming your tables all have one to many relationships. What you will get is one row for row in EACH table that matches a value.

    Try using a SELECT DISTINCT. That should get you the results you need
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    he's not specifying HOW to join the tables. for example, this

    $resultescq = mysql_query("SELECT * FROM pending, transfers WHERE pending.userid = '$userid' && transfers.userid = '$userid'",$dbx);

    should be this

    $resultescq = mysql_query("SELECT * FROM pending, transfers WHERE pending.someidthatwillmatchintransfers=transfers.someidthatwillmatchpending pending.userid = '$userid' && transfers.userid = '$userid'",$dbx);

    BTW, are the userid columns numeric? then don't put quotes around $userid in the query!


    2nd BTW, it's mysql_num_rows(), not mysql_numrows(). does mysql_numrows() work for you?
    Last edited by DR_LaRRY_PEpPeR; Feb 22, 2002 at 16:20.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR


Bookmarks

Posting Permissions

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