ianhaney50

done it with the following coding in the pastebin link http://pastebin.com/td96Gey0 I just got to test it now to make sure it updates

Hmm weird I looked at the page source and did not see any weird HTML or anything?

Hi I have now made the project site I am working on much simpler than before and now got all the fields in one form and added to one dbtable I have got the profile.php page working so it displays all the data from the users table and renewal table for the dates to display to display Now I am trying to make a edit-profile.php page which I have made and put all the coding and put the markup at the bottom and the php above it but for some reason the current data is not displaying within the form fields I have done a var_dump on the query and outputs the following string(346) "SELECT u.id , name , email , address1 , address2 , town , county , postcode , telnumber , mobnumber , model , numplate , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM users u INNER JOIN renewal USING (id) INNER JOIN item USING (item_id) WHERE id='23'" so then did a var_dump on just on the one variable var_dump($name); and that outputted the following string(9) "Ian Haney" so seems to connecting to the db and retrieving the data so not sure why is not displaying the current data within the form, all the fields are the same as the db etc. the link to the pastebin coding is below http://pastebin.com/UUSp0wgZ

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

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

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

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

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

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?

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()";

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

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 */;

Sorry just a update I have managed to do it with the following code on profile.php $sql = "SELECT * FROM users INNER JOIN visitors on visitors.visitor_id = visitors.visitor_id WHERE users.id = $id LIMIT 1";

Hi just a update I put the coding in below for now on profile.php $sql = "SELECT u.username, v.password FROM users AS u INNER JOIN visitors AS v ON u.visitor_id = v.id WHERE visitor_id = {$visitor_id} LIMIT 1"; just to confirm I do need to add visitor_id in the users table as well don't I? at the mo I am getting the following error on the profile.php page MySQL error no 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1 at the mo, my whole coding looks like the following <?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(); } ## query database # fetch data from mysql database $sql = "SELECT u.username, v.password FROM users AS u INNER JOIN visitors AS v ON u.visitor_id = v.id WHERE visitor_id = {$visitor_id} LIMIT 1"; if ($result = $mysqli->query($sql)) { $user = $result->fetch_array(); } else { echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>"; exit(); } if ($result->num_rows == 1) { # calculating online status if (time() - $user['status'] <= (30)) { // 300 seconds = 5 minutes timeout $status = "Online"; } else { $status = "Offline"; } # echo the user profile data echo "<p>User ID: {$user['id']}</p>"; echo "<p>Username: {$user['username']}</p>"; echo "<p>Name: {$user['name']}</p>"; } 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>'; } ?> I can provide the db table structure for users and visitors if need be

Hi Yeah that was the one but found a better one now where the id stays the same when a user logs in etc. so should be easier now to join two tables together, am going to call it a day for tonight but will carry on tomorrow with it and hopefully get somewhere with the joining of the two tables

yeah is strange why it is being changed, normally is fixed id I did remember seeing some coding about random code so think it does generate a random string of characters, I'll see if I can find it again don't make sense why it does generate a new string of characters when logging in

I have done that line so it now looks like the following $userid= mysqli_real_escape_string($conn, $_POST['userid']); - LINE 133 if(isset($_POST['userid'])){ $userid = $_POST['userid']; } but am getting the following error Notice: Undefined index: userid in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/information-form.php on line 133 The data is added but the userid column is empty in visitors table I have noticed the userid in the users table changes on login, one min it is mix of numbers and letters and then I logout and login again and it is different set of numbers and letters? that won't affect it will it as thought it would put in the userid into the visitors table and update it when a user logs in, then logs out and then logs in again

ahh cool done it

Ahh ok what should it be? would it be $userid= mysqli_real_escape_string($conn, $_POST['userid']); cause if I remember putting something in between the single quotes will just insert whatever is between the single quotes for example $userid = 'userid';

How do I mark it as answered I can't see anywhere on this page to mark it as answered or is it on the main page with the list of posts?

I have updated the coding to the following but it is not adding nothing to the userid column in the visitors table? <?php if(isset($_POST["submit"])){ $servername = ""; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $userid = ''; if(isset($_POST['userid'])){ $userid = $_POST['userid']; } // escape variables for security $visitor_name = mysqli_real_escape_string($conn, $_POST['visitor_name']); $visitor_email = mysqli_real_escape_string($conn, $_POST['visitor_email']); $visitor_firstline = mysqli_real_escape_string($conn, $_POST['visitor_firstline']); $visitor_secondline = mysqli_real_escape_string($conn, $_POST['visitor_secondline']); $visitor_town = mysqli_real_escape_string($conn, $_POST['visitor_town']); $visitor_county = mysqli_real_escape_string($conn, $_POST['visitor_county']); $visitor_postcode = mysqli_real_escape_string($conn, $_POST['visitor_postcode']); $visitor_tel = mysqli_real_escape_string($conn, $_POST['visitor_tel']); $visitor_mobile = mysqli_real_escape_string($conn, $_POST['visitor_mobile']); $visitor_model = mysqli_real_escape_string($conn, $_POST['visitor_model']); $visitor_plate = mysqli_real_escape_string($conn, $_POST['visitor_plate']); $sql = "INSERT INTO visitors (visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, userid) VALUES ('$visitor_name', '$visitor_email', '$visitor_firstline', '$visitor_secondline', '$visitor_town', '$visitor_county', '$visitor_postcode', '$visitor_tel', '$visitor_mobile', '$visitor_model', '$visitor_plate', '$userid')"; $result = $conn->query($sql); $visitor_id = $conn->insert_id; $insurance_date = date('Y-m-d', strtotime($_POST['visitor_insurance'])); $mot_date = date('Y-m-d', strtotime($_POST['visitor_mot'])); $tax_date = date('Y-m-d', strtotime($_POST['visitor_tax'])); $sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES ($visitor_id, 1, '$insurance_date'), ($visitor_id, 2, '$mot_date'), ($visitor_id, 3, '$tax_date')"; /*echo "<pre>$sql</pre>";*/ /*$conn->query($sql);*/ $result = $conn->query($sql); if ($result !== false) { echo "<script type= 'text/javascript'>alert('Your Information has been added successfully to our database');</script>"; } else { echo "<script type= 'text/javascript'>alert('Error: " . $sql . "<br>" . $conn->error."');</script>"; } $conn->close(); } ?>

also do I need to change of the following code <?php $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 { /*SUCCESS MSG*/ echo ''; } $visitor_name = ''; if(isset($_POST['visitor_name'])){ $visitor_name = $_POST['visitor_name']; } $sqlCommand3 = "SELECT visitor_id, visitor_name FROM visitors INNER JOIN users USING (visitor_id)"; $query = mysqli_query($db, $sqlCommand3) or die (mysqli_error($db)); $visitor_name=0; //fetch the data from the database while ($row = mysqli_fetch_array($query)) { if ($row['visitor_id'] != $visitor_name) { if ($visitor_name != 0) { $visitor_name = $row['visitor_name']; } } } ?> <?php $title = "My Account - The Tax Elephants"; $pgDesc=""; $pgKeywords=""; include ( 'includes/header.php' ); ?> <!--CONTENT--> <div id="column-whole"> <?php /* Requested Username error checking */ $req_user = trim($_GET['user']); if(!$req_user || strlen($req_user) == 0 || !preg_match("/^[a-z0-9]([0-9a-z_-\s])+$/i", $req_user) || !$database->usernameTaken($req_user)){ die("Username not registered"); } /* Logged in user viewing own account */ if(strcmp($session->username,$req_user) == 0){ echo "<h1>My Account</h1>"; } /* Visitor not viewing own account */ else{ echo "<h1>User Info</h1>"; } /* Display requested user information - add/delete as applicable */ $req_user_info = $database->getUserInfo($req_user); /* Username */ echo "<b>Username: ".$req_user_info['username']."</b><br>"; /* Email */ echo "<b>Email:</b> ".$req_user_info['email']."<br>"; echo "<strong>Name:</strong> ".$visitor_name['visitor_name']."<br>"; /** * Note: when you add your own fields to the users table * to hold more information, like homepage, location, etc. * they can be easily accessed by the user info array. * * $session->user_info['location']; (for logged in users) * * $req_user_info['location']; (for any user) */ /* If logged in user viewing own account, give link to edit */ if(strcmp($session->username,$req_user) == 0){ echo '<br><a href="useredit.php">Edit Account Information</a><br>'; } /* Link back to main */ echo "<br><a href='process.php'>Logout</a><br>"; ?> </div> <?php // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?>

I have added user id into the visitors table and changed the INSERT query to the following // escape variables for security $visitor_name = mysqli_real_escape_string($conn, $_POST['visitor_name']); $visitor_email = mysqli_real_escape_string($conn, $_POST['visitor_email']); $visitor_firstline = mysqli_real_escape_string($conn, $_POST['visitor_firstline']); $visitor_secondline = mysqli_real_escape_string($conn, $_POST['visitor_secondline']); $visitor_town = mysqli_real_escape_string($conn, $_POST['visitor_town']); $visitor_county = mysqli_real_escape_string($conn, $_POST['visitor_county']); $visitor_postcode = mysqli_real_escape_string($conn, $_POST['visitor_postcode']); $visitor_tel = mysqli_real_escape_string($conn, $_POST['visitor_tel']); $visitor_mobile = mysqli_real_escape_string($conn, $_POST['visitor_mobile']); $visitor_model = mysqli_real_escape_string($conn, $_POST['visitor_model']); $visitor_plate = mysqli_real_escape_string($conn, $_POST['visitor_plate']); $userid = mysqli_real_escape_string($conn, $_POST['userid']); $sql = "INSERT INTO visitors (visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, userid = 0) VALUES ('$visitor_name', '$visitor_email', '$visitor_firstline', '$visitor_secondline', '$visitor_town', '$visitor_county', '$visitor_postcode', '$visitor_tel', '$visitor_mobile', '$visitor_model', '$visitor_plate', '$userid')"; Does it look ok or have I missed anything? Is there anything else I need to do anywhere?

ahh ok will give that a go now and post a update if ok

Ahh ok so I need to define $visitor_id in the addNewUser() function? how would I do that? The purpose is the users one will just store their username, email address and password and the visitors table stores their personal info such as name, address, car model and car number plate and phone numbers