JOIN mysql php

[ianhaney50]

Sorry

 

on my userinfo.php page

 

it currently retrieves and displays the username and email address but now I want to retrieve and display the Full name of the visitor from the visitors table

 

I know it can be done using JOIN but not 100% sure how to code it in

 

I did try from another php file that uses JOIN but it only only displays the first letter of the full name

 

The code I have is below in the userinfo.php file

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php
/**
 * UserInfo.php
 *
 * This page is for users to view their account information
 * with a link added for them to edit the information.
 *
 * Updated by: The Angry Frog
 * Last Updated: October 26, 2011
 */
include("include/session.php");
global $database;
$config = $database->getConfigs();
if (!isset($_GET['user'])) { 
	header("Location: ".$config['WEB_ROOT'].$config['home_page']);
}
?>

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

$sqlCommand3 = "SELECT v.visitor_id, visitor_name
FROM visitors v
JOIN users";

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

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

$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);
?>

<!--CONTENT-->

<?php include( 'includes/footer.php' ); ?>

I have added visitor_id in the users table and made it INT(11) at the end of the users table and is currently saying 0 but the visitor_id of the user I am trying to match it with is 66

 

I looked in database.php as going by another php file using JOIN, it has INSERT INTO as well so it looks like it inserts the visitor_id into two two tables so I thought it already adds visitor_id into visitors table and thought it would also add visitir_id into the users table but has not worked that way

 

the database.php coding I have is below

function addNewUser($username, $password, $email, $token, $usersalt){
      $time = time();
      $config = $this->getConfigs();
      /* If admin sign up, give admin user level */
      if(strcasecmp($username, ADMIN_NAME) == 0){
         $ulevel = ADMIN_LEVEL;
      /* Which validation is on? */
      }else if ($config['ACCOUNT_ACTIVATION'] == 1) {
      	 $ulevel = REGUSER_LEVEL; /* No activation required */
      }else if ($config['ACCOUNT_ACTIVATION'] == 2) {
         $ulevel = ACT_EMAIL; /* Activation e-mail will be sent */
      }else if ($config['ACCOUNT_ACTIVATION'] == 3) {
         $ulevel = ADMIN_ACT; /* Admin will activate account */   
   	  }

	 $password = sha1($usersalt.$password);
	 $userip = $_SERVER['REMOTE_ADDR'];
      
     $query = "INSERT INTO ".TBL_USERS." SET username = :username, password = :password, usersalt = :usersalt, userid = 0, userlevel = $ulevel, email = :email, timestamp = $time, actkey = :token, ip = '$userip', regdate = $time, visitor_id = $visitor_id";
     $stmt = $this->connection->prepare($query);
     return $stmt->execute(array(':username' => $username, ':password' => $password, ':usersalt' => $usersalt, ':email' => $email, ':token' => $token));
   }

[Destramic]

is something like this you're after?

SELECT v.visitor_id, 
       v.visitor_name 
FROM visitors v
LEFT JOIN users u ON u.visitor_id = v.visitor_id

[ianhaney50]

Hi Destramic

 

Thank you for the reply, appreciate it

 

I got something similar - see below

<?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 got no errors but it is not displaying the full name from the visitors table?

[Ch0cu3r]

Could you show us your table structure for the users and visitors tables.

[ianhaney50]

Hi ch0cu3r

 

No probs, I have added below the structure for the two tables, is it ok?

 

-- phpMyAdmin SQL Dump

-- version 4.0.7

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jun 28, 2015 at 11:34 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: `database name`

--

 

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

 

--

-- Table structure for table `users`

--

 

CREATE TABLE IF NOT EXISTS `users` (

  `username` varchar(30) NOT NULL,

  `password` varchar(40) DEFAULT NULL,

  `usersalt` varchar( NOT NULL,

  `userid` varchar(32) DEFAULT NULL,

  `userlevel` tinyint(1) unsigned NOT NULL,

  `email` varchar(50) DEFAULT NULL,

  `timestamp` int(11) unsigned NOT NULL,

  `actkey` varchar(35) NOT NULL,

  `ip` varchar(15) NOT NULL,

  `regdate` int(11) unsigned NOT NULL,

  `visitor_id` int(11) NOT NULL,

  PRIMARY KEY (`username`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

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

 

--

-- Table structure for table `visitors`

--

 

CREATE TABLE IF NOT EXISTS `visitors` (

  `visitor_id` int(11) NOT NULL AUTO_INCREMENT,

  `visitor_name` varchar(255) NOT NULL,

  `visitor_email` varchar(255) NOT NULL,

  `visitor_firstline` varchar(255) NOT NULL,

  `visitor_secondline` varchar(255) NOT NULL,

  `visitor_town` varchar(255) NOT NULL,

  `visitor_county` varchar(255) NOT NULL,

  `visitor_postcode` varchar(255) NOT NULL,

  `visitor_tel` varchar(255) NOT NULL,

  `visitor_mobile` varchar(255) NOT NULL,

  `visitor_model` varchar(50) NOT NULL,

  `visitor_plate` varchar(255) NOT NULL,

  PRIMARY KEY (`visitor_id`)

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

 

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

Edited June 28, 2015 by ianhaney50

[Ch0cu3r]

The vistor id is never inserted in to your users table as the variable $vistor_id is not defined in the addNewUser() function, this will be inserting a blank value into the visitor_id column

     $query = "INSERT INTO ".TBL_USERS." SET username = :username, password = :password, usersalt = :usersalt, userid = 0, userlevel = $ulevel, email = :email, timestamp = $time, actkey = :token, ip = '$userip', regdate = $time, visitor_id = $visitor_id";

This will result in your your JOIN query returning no results.

 

What is the purpose of these two tables?

[ianhaney50]

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

[Ch0cu3r]

In that case you do not want to have the visitor id stored in the users table. Instead you should store the users id in the visitors table.

[ianhaney50]

ahh ok

 

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

Edited June 28, 2015 by ianhaney50

[ianhaney50]

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?

Edited June 28, 2015 by ianhaney50

[ianhaney50]

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);
?>

[ianhaney50]

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();
}
?>

[Barand]

The only value you ever store in $userid is an empty string

$userid = '';

 

[ianhaney50]

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';

Edited June 28, 2015 by ianhaney50

[Barand]

Ahh ok what should it be?

 

It should be the id of whichever user you want to link that particular visitor record. So, if that is what is in $_POST['userid'], then yes

$userid= mysqli_real_escape_string($conn, $_POST['userid']);

[ianhaney50]

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

[Ch0cu3r]

 

I have noticed the userid in the users table changes on login,

Umm.. why? Seeing as the userid field is VARCHAR(32) I'm guessing it is being md5 hashed for some reason.

 

 

that won't affect it will it

This will cause a problem yes

[ianhaney50]

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

[Ch0cu3r]

Been googling and it is this login script here you are using https://github.com/ivannovak/jpmaster77-s-Login-System-

That login script uses the session id as the user id, this is why the userid field is changing upon login

 

If the userid field in the users table keeps changing then this is going to destroy the relationship between the two tables. You have a couple of options set the userid field in the users table to have foreign key constraints to the userid field in the visitors table

 

or

 

insert a new field in your users table, call it id and set it to be auto increment. You would insert that fields value in to your visitors userid field when adding the users personal details.

[ianhaney50]

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

[ianhaney50]

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

[ianhaney50]

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