SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,388
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Rows of two tables repeating and combining

    Why would this script repeat the table data? I have four rows in each table. It appears that the information is being repeated to all possible iterations among the rows. I removed the WHERE clause and still get duplicates. (The WHERE clause would normally link to a linking table to link the rows in table 1 with the corresponding rows in table 2.)

    That is, row 1 of table 1 accompanies not just row 1 of table 2 (which is correct), but also rows 2-4 of table 2. Row 2 of table 1 is also paired with all four rows of table 2 successively down the page.

    Table 1=racer_spotlight. Rows=rsModelName, rsDescr
    Table 2=racerspot_photos. Rows=rspOwnerName, rspFilename

    I stripped down the code snippet to the bare essentials (no WHERE statement) where the duplicate still occurs:

    PHP Code:
    <table class="chart600">

    <?php


    $rsq 
    = @mysql_query(
        
    'SELECT rsModelName, rsDescr, rspOwnerName, rspFilename
        FROM racer_spotlight, racerspot_photos'
    );
    if (!
    $rsq) {
      exit(
    'Unable to load the info from the database.');
    }
    if (
    mysql_num_rows($rsq) < 1) {
      exit(
    'Sorry, that information is not available. Try another search.');
    }



    while (
    $rs mysql_fetch_array($rsq)) {
        
    $rsModelName =     $rs['rsModelName'];
        
    $rsDescr =     $rs['rsDescr'];
        
    $rsBodyType =     $rs['rsBodyType'];
        
    $rspFilename =     $rs['rspFilename'];
        
    $rspOwnerName $rs['rspOwnerName'];

    // Paragraphs and line breaks
    $rsDescr ereg_replace("\r\n""\n"$rsDescr);
    $rsDescr ereg_replace("\r""\n"$rsDescr);
    $rsDescr ereg_replace("\n\n"'</p><p>'$rsDescr);
    $rsDescr ereg_replace("\n"'<br />'$rsDescr);

    echo 
    "<tr><td><p><strong>$rspOwnerName  </strong>$rsModelName<br />$rsDescr</p><img src=$rspFilename></td></tr>";
    }
    ?>

    </tr></table>
    Can you tell me why the duplicates appear? I don't get this in any other script.

    Thanks,
    Steve

  2. #2
    Non-Member DelvarWorld's Avatar
    Join Date
    Jul 2004
    Location
    Baloney
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First off this belongs in the database forum, secondly you're getting duplicates because you're using the dreaded "cross join." Without specifying a WHERE or ON clause in a regular old join MySQL will "multiply" the rows together, that is you get row 1 from table 1 paired with all rows from table 2, then row 2 from table 1 paired with all rows from table 2, and so on.

    If you included a WHERE / ON clause properly lining up table A and table B and you still had duplicates then there's a logic error in your MySQL statement.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you didn't specify the relationship between the two tables in your SQL statement.

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,388
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I get the cross-join even with constraints added :

    PHP Code:
    $rsq = @mysql_query(
        
    'SELECT rsModelName, <== from table a
    rspOwnerName, rspFilename <== from table b
        FROM racer_spotlight, <== table a
    racerspot_photos, <==table b
    racer_photolink <== linking table
        WHERE rsID=rsIDlink <== ID of table a = ID of linking table
    AND rsBodyType="buggy"' 
    <== from table a
    ); 
    The linking table is created with a primary key:

    CREATE TABLE racer_photolink (
    rsIDlink INT NOT NULL,
    rspIDlink INT NOT NULL,
    PRIMARY KEY(rsIDlink, rspIDlink)
    );

    Does this info help?

    Thanks!
    Steve

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,388
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    OK, I'm using the wrong technique. I need to use two tables, not three!


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
  •