ianhaney50 Posted June 29, 2015 Share Posted 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 */; Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/ Share on other sites More sharing options...
Barand Posted June 29, 2015 Share Posted 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. 1 Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515189 Share on other sites More sharing options...
ianhaney50 Posted June 29, 2015 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515224 Share on other sites More sharing options...
Barand Posted June 29, 2015 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515225 Share on other sites More sharing options...
ianhaney50 Posted June 29, 2015 Author Share Posted 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()"; Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515229 Share on other sites More sharing options...
ianhaney50 Posted June 29, 2015 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515230 Share on other sites More sharing options...
ianhaney50 Posted June 29, 2015 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515233 Share on other sites More sharing options...
Barand Posted June 29, 2015 Share Posted June 29, 2015 Yes, GROUP_CONCAT is one way. The other way is the same as in your other thread for the emails. Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515246 Share on other sites More sharing options...
ianhaney50 Posted June 30, 2015 Author Share Posted 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 75Ian Haney75Ian 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 Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515255 Share on other sites More sharing options...
ianhaney50 Posted June 30, 2015 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515267 Share on other sites More sharing options...
Solution Ch0cu3r Posted June 30, 2015 Solution Share Posted 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']; } Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515268 Share on other sites More sharing options...
ianhaney50 Posted June 30, 2015 Author Share Posted 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 1 Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515271 Share on other sites More sharing options...
Ch0cu3r Posted June 30, 2015 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515272 Share on other sites More sharing options...
ianhaney50 Posted June 30, 2015 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/297088-join-table-with-dates-and-item_id/#findComment-1515273 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.