auto increment value inserted to two db tables

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
ianhaney
New php-forum User
New php-forum User
Posts: 62
Joined: Tue May 01, 2012 12:30 pm

Sat May 19, 2018 10:32 am

I was just wondering if it is possible to have a auto increment id number added to two db tables instead of just one as this will be how I link the data together from two tables after logging in so the user can just see their data and no other users

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 761
Joined: Mon Feb 22, 2016 5:52 pm

Sat May 19, 2018 10:38 am

Have you tried it?

What happens when you try?

Stating the obvious: Don't try it on a live database.

If it does work, are you sure that it will solve your problem?

ianhaney
New php-forum User
New php-forum User
Posts: 62
Joined: Tue May 01, 2012 12:30 pm

Sat May 19, 2018 10:41 am

I have not tried it yet as not sure what to add in the coding, below is what I have

Code: Select all

if ($stmt = $mysqli->prepare("INSERT repairs (customer_ref, customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, exrdate, exrtime, exstdate, exstime, deltype, comments, job_cost, part_cost, profit, username, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
				{
					$stmt->bind_param("sssssssssssssssssss", $customer_ref, $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $job_cost, $part_cost, $profit, $username, $hashed_password);
					$stmt->execute();
					
					if ($stmt = $mysqli->prepare("INSERT members (customer_ref, username, password) VALUES (?, ?, ?)"))
					
					{
						$stmt->bind_param("sss", $customer_ref, $username, $hashed_password);
						$stmt->execute();
					$stmt->close();

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 761
Joined: Mon Feb 22, 2016 5:52 pm

Sat May 19, 2018 12:07 pm

I think that you're trying to do too much in one go, have you created the database and tables?

I think that you'll a few more tables and look a bit more in depth at your data requirements.

ianhaney
New php-forum User
New php-forum User
Posts: 62
Joined: Tue May 01, 2012 12:30 pm

Sat May 19, 2018 12:40 pm

I have created the database and the two tables, the structure of each db table is below

members table
memberID, customer_ref, username, password

repairs table
memberID, customer_ref, customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, exrdate, exrtime, exstdate, exstime, deltype, comments, job_cost, part_cost, profit, username, password, send_sms

When I create the repair on the admin side, it's adds the username and password into members db table and into repairs table

I thought I would be able to show the data for a user when they login after entering their username and password and thought could link the data and tables by the customer_ref column and link it that way as the customer ref is added to both db tables but when I alter the login and repair profile page, it logs in but displays no data. Below is the coding I have for both login.php and repair-job-status.php

login.php

Code: Select all

<?php
session_start();
require_once 'dbconnect.php';

if (isset($_SESSION['username'])!="") {
 header("Location: repair-job-status.php");
 exit;
}

if (isset($_POST['btn-login'])) {
 
 $username = strip_tags($_POST['username']);
 $password = strip_tags($_POST['password']);
 
 $username = $DBcon->real_escape_string($username);
 $password = $DBcon->real_escape_string($password);
 
 $query = $DBcon->query("SELECT memberID, customer_ref, username, password FROM members WHERE username='$username'");
 $row=$query->fetch_array();
 
 $count = $query->num_rows; // if email/password are correct returns must be 1 row
 
 if (password_verify($password, $row['password']) && $count==1) {
  $_SESSION['username'] = $row['memberID'];
  header("Location: repair-job-status.php");
 } else {
  $msg = "<div class='alert alert-danger'>
     <span class='glyphicon glyphicon-info-sign'></span> &nbsp; Invalid Username or Password !
    </div>";
 }
 $DBcon->close();
}

 //define page title
$title = 'Login';

//include header template
require('layout/header.php');
?>

     <!-- Content  -->
	<div id="page-content">
		<!-- Breadcrumbs Block -->
		<div class="block">
			<div class="container">
				<div class="breadcrumbs">
					<ul class="breadcrumb">
						<li><a href="/">Home</a></li>
						<li class="active">Account Login</li>
					</ul>
				</div>
			</div>
		</div>
		<!-- //Breadcrumbs Block -->
		<!-- Category Block -->
		<div class="block">
			<div class="container">
				<h1 class="text-center">Account <span class="color">Login</span></h1>

	    <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
        
       <form class="form-signin" method="post" id="login-form">
        
        <?php
  if(isset($msg)){
   echo $msg;
  }
  ?>
        
        <div class="form-group"> 
        <input type="text" name="username" id="username" class="form-control input-lg" placeholder="Username" />
		</div>
        
        <div class="form-group">
        <input type="password" name="password" id="password" class="form-control input-lg" placeholder="Password" /> 
        </div>
       
      <hr />
        
        <div class="form-group">
            <button type="submit" class="btn btn-primary btn-block btn-lg" name="btn-login" id="btn-login">
      &nbsp; Sign In
   </button> 

        </div>  
        
        
      
      </form>

    </div>
    
</div>
</div>

<?php 
//include footer template
require('layout/footer.php'); 
?>


repair-job-status.php

Code: Select all

<?php

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

session_start();
include_once 'dbconnect.php';

if (!isset($_SESSION['username'])) {
 header("Location: login.php");
}

$query = $DBcon->query("SELECT repairs.username, repairs.customer_ref, repairs.customer_name, repairs.customer_email, repairs.customer_phone, repairs.computer_make, repairs.computer_model, repairs.status, DATE_FORMAT(exrdate, '%d/%m/%Y') AS exrdate, repairs.exrtime, repairs.comments, repairs.job_cost FROM repairs WHERE customer_ref=".$_SESSION['username']);

$userRow=$query->fetch_array();
$DBcon->close();

  //define page title
$title = 'Repair Job/Status';

//include header template
require('layout/header.php'); 

?>

<!-- Content  -->
	<div id="page-content">
		<!-- Breadcrumbs Block -->
		<div class="block">
			<div class="container">
				<div class="breadcrumbs">
					<ul class="breadcrumb">
						<li><a href="/">Home</a></li>
						<li class="active">Repair Job/Status</li>
					</ul>
				</div>
			</div>
		</div>
		<!-- //Breadcrumbs Block -->
		<!-- Category Block -->
		<div class="block">
			<div class="container">
				<h1 class="text-center">Repair <span class="color">Job/Status</span></h1>

	    <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">

<?php echo $userRow['username']; ?>
<br>
<?php echo $userRow['customer_name']; ?>


<a href="logout.php?logout">&nbsp; Logout</a>
          
</div>
</div>
</div>
</div>

<?php 
//include footer template
require('layout/footer.php'); 
?>

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 761
Joined: Mon Feb 22, 2016 5:52 pm

Sun May 20, 2018 1:20 am

Look at your data:

you have duplicated username and password in both tables
you have contact information in the repairs table
you already have a memberID, use that to get your data
is customer_ref for your information or their information?
could you have different technicians working on different orders from the same customer?

Are your customers individuals, businesses or both?
do you only deal with one contact?
are you using the term member to mean a customer?

Have a look at this on normalization.

ianhaney
New php-forum User
New php-forum User
Posts: 62
Joined: Tue May 01, 2012 12:30 pm

Sun May 20, 2018 4:43 am

I managed to sort the issue by redoing the coding again by a script I found that I did for another project and is all ok now and a lot more simpler

vijaydeveloper
New php-forum User
New php-forum User
Posts: 1
Joined: Thu Nov 22, 2018 1:30 am
Location: India
Contact:

Thu Nov 22, 2018 2:15 am

Hi,
You can go here;

$db = new mysqli('localhost', 'user', 'pass', 'database');
$sql = "SHOW TABLE STATUS LIKE 'table'";
$result=$db->query($sql);
$row = $result->fetch_assoc();

echo $row['Auto_increment'];

To know more

Post Reply