使用INNER JOIN问题重复数据

Question

I am building a project and want to retrieve data from three different tables so am using INNER JOIN but just worked out it is duplicating the data, below is what it is currently doing

Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Insurance expiry date: 30 July 2015
Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Tax expiry date: 30 June 2015

what I want is the following

Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Tax expiry date: 30 June 2015
Insurance expiry date: 30 July 2015
MOT expiry date: 30 August 2015

is that possible to do?

my coding is below

<?php

if (logged_in() == false) {
    redirect_to("login.php");
} else {
    if (isset($_GET['id']) && $_GET['id'] != "") {
        $id = $_GET['id'];
    } else {
        $id = $_SESSION['user_id'];
    }

    ## connect mysql server
        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        # check connection
        if ($mysqli->connect_errno) {
            echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
            exit();
        }

    ## connect mysql server
        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        # check connection
        if ($mysqli->connect_errno) {
            echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
            exit();
        }
    ## query database
        # fetch data from mysql database

        $sql = "SELECT v.visitor_id, visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, item.description, renewal_id, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue, renewal_date FROM visitors v 
        INNER JOIN renewal USING (visitor_id)
        INNER JOIN item USING (item_id)
        WHERE renewal_date >NOW()";


        if ($result = $mysqli->query($sql)) {
            $user = $result->fetch_array();
            } else {
            echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>";
            exit();
            }

    if(mysqli_num_rows($result)) {  

   //fetch the data from the database 
while ($row = mysqli_fetch_array($result)) {

# echo the user profile data
            /*echo "<p>User ID: {$user['id']}</p>";*/
            echo "<p>Name: {$user['visitor_name']}</p>";
            echo "<p>First Line of Address: {$user['visitor_firstline']}</p>";
            echo "<p>Second Line of Address: {$user['visitor_secondline']}</p>";
            echo "<p>Town: {$user['visitor_town']}</p>";
            echo "<p>County: {$user['visitor_county']}</p>";
            echo "<p>Postcode: {$user['visitor_postcode']}</p>";
            echo "<p>Telephone Number: {$user['visitor_tel']}</p>";
            echo "<p>Mobile Number: {$user['visitor_mobile']}</p>";
            echo "<p>Car Model: {$user['visitor_model']}</p>";
            echo "<p>Car Number Plate: {$user['visitor_plate']}</p>";       
            echo "<p>" . $row['description'] . " expiry date: " . $row['datedue'] . "</p>
";

}
        } else { // 0 = invalid user id
            echo "<p><b>Error:</b> Invalid user ID.</p>";
        }

}

?>

Answer 1

this need to work:

SELECT v.visitor_id, visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, item.description, renewal_id, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue, renewal_date FROM visitors v 
            INNER JOIN renewal on (v.vistor_id = renewal.visitor_id)
            INNER JOIN item on (v.vistor_id = item.visitor_id)
            WHERE renewal_date >NOW()

linking 3 tables will cause you bad performance you need to warp the 2 tables first and then connect 3rd one like this:

select a.* from (
SELECT v.visitor_id, visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, item.description, renewal_id, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue, renewal_date FROM visitors v 
        INNER JOIN renewal on (v.vistor_id = renewal.visitor_id) ) as a
        INNER JOIN item on (a.vistor_id = item.visitor_id)
        WHERE renewal_date >NOW()