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>\n";
}
} else { // 0 = invalid user id
echo "<p><b>Error:</b> Invalid user ID.</p>";
}
}
?>
