JOIN table with dates and item_id

ianhaney50 · June 29, 2015

Hi

next little issue

I have successfully joined two tables together and got the data pulled in from the two tables, now I need to pull data from a third table that contains 3 different dates for different item_id numbers so one is car mot date, second is car insurance date and the third is car tax date

The code I put in profile.php is below

$sql = "SELECT * FROM users INNER JOIN visitors on visitors.visitor_id = visitors.visitor_id INNER JOIN renewal on visitors.visitor_id = visitors.visitor_id WHERE users.id = $id LIMIT 1";

then bit further below, I have the echo code

echo "<p>Car MOT Expiry Date: {$user['renewal_date']}</p>";
			echo "<p>Car Insurance Expiry Date: {$user['renewal_date']}</p>";
			echo "<p>Car TAX Expiry Date: {$user['renewal_date']}</p>";

obviously that just outputs the same date on each row

my database structure for the renewal table is below

-- phpMyAdmin SQL Dump

-- version 4.0.7

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jun 29, 2015 at 09:46 AM

-- Server version: 5.5.42

-- PHP Version: 5.3.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `databasename`

--

-- --------------------------------------------------------

--

-- Table structure for table `renewal`

--

CREATE TABLE IF NOT EXISTS `renewal` (

`renewal_id` int(11) NOT NULL AUTO_INCREMENT,

`visitor_id` int(11) NOT NULL,

`item_id` int(11) NOT NULL,

`renewal_date` date NOT NULL,

`date_notified` datetime DEFAULT NULL,

PRIMARY KEY (`renewal_id`),

KEY `renewal_id` (`renewal_id`),

KEY `renewal_id_2` (`renewal_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=181 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Barand · June 29, 2015

Not so long ago I spent hours answering these same questions for you in this topic

http://forums.phpfreaks.com/topic/296918-automatic-php-email/

That topic shows you how to join to the renewal table.

Now you want us go through it all again? Where were you during that marathon topic. If you cannot be bothered to learn you are wasting our time.

ianhaney50 · June 29, 2015

Sorry I am bothered and eager to learn

I have been looking at the auto-email.php and see how that is done and done the profile.php the same way but the profile.php is outputting the data 3 times, wonder if my query is wrong

<?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>";
		}
		
}

		
// showing the login & register or logout link
if (logged_in() == true) {
	echo '<a href="logout.php">Log Out</a>';
} else {
	echo '<a href="login.php">Login</a> | <a href="register.php">Register</a>';
}
?>

the data displayed is below

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

Barand · June 29, 2015

That's the way joins work. They match data in one table with the corresponding data in the other, matching on the key values. If you have 1 record in tableA matching 3 records in tableB then you get 3 records output, each with the data from B and the matching data from A.

ianhaney50 · June 29, 2015

Ahh ok got it, I was hoping for more of 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

Insurance expiry date: 30 July 2015

MOT expiry date: 30 June 2015

TAX expiry date: 30 August 2015

Is that possible to have it like that?

also the data does not output the MOT date which am taking a guess is down to the query renewal date being WHERE renewal_date >NOW()";

ianhaney50 · June 29, 2015

sorry also just looked more closely at the WHERE renewal_date >NOW()"; code and looks like it is basically saying where the renewal date is greater than today's date

so am guessing why the MOT date is not showing?

ianhaney50 · June 29, 2015

Been doing some googling and was wondering if GROUP_CONCAT would be more suited to what I was looking for

I am tempted to give it a go just for learning as well as seeing if that does what I need, looking around on Google it does look like it could be what I need

Barand · June 29, 2015

Yes, GROUP_CONCAT is one way.

The other way is the same as in your other thread for the emails.

ianhaney50 · June 30, 2015

Hi

I have the following coding, most of it I got from the auto email thread

	$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  
mysqli_select_db($db,"") or die(mysqli_error($db));

if (mysqli_connect_error()) {
    die ('Failed to connect to MySQL');
} else {
	echo 'success';
}

	## query database
		# fetch data from mysql database		
		
		$sql = "SELECT 
        v.visitor_id
        , visitor_name
        , visitor_email
        , 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()";

		$query = mysqli_query($db, $sql) or die (mysqli_error($db)); 
		
		//fetch tha data from the database 
	while ($row = mysqli_fetch_array($query)) {
		
		echo "<br><br>";
echo $row['visitor_id'];
echo "<br><br>";
echo $row ['visitor_name'];
  echo "<br />";
	}
	
	// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);
}
?>

For now I am just outputting the visitor id and name just to get it working

on the profile.php page it is still repeating the data

75

Ian Haney

75

Ian Haney

I know(well think it is) it is down to the sql command query but not sure what to change in it so it only outputs the data once so will be like the following

Name

Email

Address

Town

County

Postcode

Tel number

Mobile number

Car Tax expiry date: 30 June 2015

Car Insurance expiry date: 30 July 2015

Car MOT expiry date: 30 August 2015

ianhaney50 · June 30, 2015

Hi

Sorry just a update, I have altered the coding to below, it is displaying the description for each item and it's date but is still repeating the name and email address where as I only want the name and email address to be displayed once

$sql = "SELECT 
        v.visitor_id
        , visitor_name
        , visitor_email
        , 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)";

$query = mysqli_query($db, $sql) or die (mysqli_error($db));

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

echo $row['visitor_id'];
echo "<br>";
echo $row ['visitor_name'];
echo "<br>";
echo $row ['visitor_email'];
echo "<br>";
echo $row ['description'] . " expiry date: " . $row['datedue'] . "\n";
  echo "<br /><br>";
}
}

I am aiming for the following

Name

Email

MOT date: 30 August 2015

Insurance date: 30 July 2015

Tax date: 30 June 2015

Instead I am getting the following

75

Ian Haney

ianhaney@irhwebsites.co.uk

M.O.T expiry date: 30 August 2015

75

Ian Haney

ianhaney@irhwebsites.co.uk

Insurance expiry date: 30 July 2015

75

Ian Haney

ianhaney@irhwebsites.co.uk

Tax expiry date: 30 June 2015

Ch0cu3r · June 30, 2015

You need to add logic to your while to prevent the duplicate data from being outputted, example

$prev_id = 0;

//fetch tha data from the database 
while ($row = mysqli_fetch_array($query))
{
    // if the vistor id does not equal the vistor id from previous row, 
    // output vistor id, name and email only once
    if($row['visitor_id'] != $prev_id)
    {
        echo $row['visitor_id'];
        echo "<br>";
        echo $row ['visitor_name'];
        echo "<br>";
        echo $row ['visitor_email'];
    }
    
    // output the neweal dates
    echo "<br>";
    echo $row ['description'] . " expiry date: " . $row['datedue'] . "\n";

    // set current rows vistor id to previous id
    $prev_id = $row['visitor_id'];
}

ianhaney50 · June 30, 2015

Hi Ch0cu3r

Thank you so much, was driving me loopy

Now I can get my head around it and work the logic out, can I just confirm the code you provided so I understand it

where you got

$prev_id = 0;

//fetch tha data from the database 
while ($row = mysqli_fetch_array($query))
{
    // if the vistor id does not equal the vistor id from previous row, 
    // output vistor id, name and email only once
    if($row['visitor_id'] != $prev_id)
    {
        echo $row['visitor_id'];
        echo "<br>";
        echo $row ['visitor_name'];
        echo "<br>";
        echo $row ['visitor_email'];
    }
    
    // output the neweal dates
    echo "<br>";
    echo $row ['description'] . " expiry date: " . $row['datedue'] . "\n";

    // set current rows vistor id to previous id
    $prev_id = $row['visitor_id'];
}

Is that saying that if the visitor_id does not equal 0 then display the name and email only once

Is that right?

sorry just trying to get my head around it

Ch0cu3r · June 30, 2015

No, it only outputs the name, email, id etc when the visitor id does change

The if statement is comparing the visitor id from the previous row. I set $prev_id to 0 as the initial stating value. At the end of the while loop $prev_id is being set to the current rows visitor id ($row['visitor_id']).

After the first iteration of the while loop, it will be checking the current rows visitors id ($row['visitor_id']) with the previous rows id ($prev_id). When they do not match then it will output the name, email and id again.

ianhaney50 · June 30, 2015

ahh ok got it

so if another user logged in and has a different visitor_id number, it will output the name email etc.

Sign In

JOIN table with dates and item_id

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information