ianhaney50
-
Posts
261 -
Joined
-
Last visited
-
Days Won
1
Posts posted by ianhaney50
-
-
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 dbtableI 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 displayNow 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 fieldsI have done a var_dump on the query and outputs the followingstring(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 followingstring(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 -
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
- 1
-
HiSorry 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
NameEmailMOT date: 30 August 2015Insurance date: 30 July 2015Tax date: 30 June 2015Instead I am getting the following75Ian Haneyianhaney@irhwebsites.co.ukM.O.T expiry date: 30 August 201575Ian Haneyianhaney@irhwebsites.co.ukInsurance expiry date: 30 July 201575Ian Haneyianhaney@irhwebsites.co.ukTax 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 HaneyI 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---- Host: localhost-- Generation Time: Jun 29, 2015 at 09:46 AM-- Server version: 5.5.42-- PHP Version: 5.3.28SET 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 pageMySQL 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 1at 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
edit profile php
in PHP Coding Help
Posted
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