Customer registration and report incident. Different pages to insert data into 2 tables using PK and FK

[PHPnoobster]

Hi,

I am trying to create a very simple website where road users report road problems. Website and Database (MySQL) has been created.

Firstly, customer arrives at the registration page. Fills form and submits. Data is inserted into db using insert.php. Then, page is directed to report page.

Secondly, customer fills in the form on second page to report problem. When submitted, there is an error. The error is that the Customer_ID (PK) generated in the register database is a FK in the 'Report' page and because the table Customer_ID(FK) does not get a value for Customer_ID (FK) from the insertreport.php, it does not allow the form to be inserted. Because these two tables are co-related, I'm stuck on how to design it better so that when the customer is registered, the problem that he/she reports is logged into the problem which indicates that it belongs to that customer.

Register form

<form action="insert.php" method="post">
  Firstname:<br>
  <input type="text" name="Firstname"><br>
  Lastname:<br>
  <input type="text" name="Lastname"><br>
  Address:<br>
  <input type="text" name="Address"><br>
Telephone:<br>
  <input type="text" name="Telephone"><br>
  E-mail:<br>
  <input type="text" name="email"><br>  
HRD Member:<br>
  <input type="radio" name="HRD" value="Yes" checked> Yes<br>
  <input type="radio" name="HRD" value="No"> No<br>
Subscribe Mailings:<br>
  <input type="radio" name="SubscribeMailings" value="Yes" checked> Yes<br>
  <input type="radio" name="SubscribeMailings" value="No"> No<br>
  <input type="submit" value="Submit">
 

Register.php

$Firstname = $_POST['Firstname'];
$Lastname = $_POST['Lastname'];
$Address = $_POST['Address'];
$Telephone = $_POST['Telephone'];
$email = $_POST['email'];
$HRD= $_POST['HRD'];
$SubscribeMailings = $_POST['SubscribeMailings'];
include 'conn.php';  // using another php file to connect with database

$sql = "INSERT INTO `customer` (`Customer_ID`, `FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `HRD`, `SubscribeMailings`) VALUES (NULL, '$Firstname', '$Lastname', '$Address', '$Telephone', '$email', '$HRD', '$SubscribeMailings')";

Report form

<form action="insertreport.php" method="post">
  Location of problem (driver location sign, driver location sign, postcode etc):<br>
  <input type="text" name="Location"><br>
  Description (detailed) :<br>
  <input type="text" name="Description"><br>

  <input type="submit" value="Submit">

insertreport.php

$Location = $_POST['Location'];
$Description = $_POST['Description'];
include 'conn.php';  // using another php file to connect with database

$sql = "INSERT INTO `problem` (`Problem_ID`, `Location`, `Description`, `Job_start`, `Customer_ID`, `Staff_ID`, `Priority_ID`, `Status_ID`, Solution_ID`, `Job_end`) VALUES (NULL, '$Location', '$Description', CURRENT_TIMESTAMP, '', NULL, NULL, '', NULL, NULL)";

[Barand]

When you insert the registration and create a new customer save the new id (last_insert_id) in $_SESSION.

Then, when you insert the report, insert the customer id saved in $_SESSION.

Same goes for existing customers logging in - save their id in $_SESSION.

$sql = "INSERT INTO `customer` (`FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `HRD`, `SubscribeMailings`) 
                        VALUES (?, ?, ?, ?, ?, ?, ?')
                        "; 
$stmt = $pdo->prepare($sql);

// then, assuming you have validated your input data,
// execute the statement passing the data as parameters

$stmt->execute( [ '$Firstname', '$Lastname', '$Address', '$Telephone', '$email', '$HRD', '$SubscribeMailings' ] );

// save the new customer id
$_SESSION['customer_id'] = $pdo->LastInsertId();

 

EDIT: my 0.02 worth...

• Don't create variables for the sake of it. $_POST['Location'] as already a variable so why move its content, unchanged, into yet another one
• Do not put variables containing user-provided data directly into your queries. Instead use prepared statements and provide the data as parameters.
• No need to insert those NULLs. Just exclude those fields from the insert and let them default to NULL.

Edited August 14, 2019 by Barand

[PHPnoobster]

Barand, thank you very much for your response. However, i'm new to this and would like a little more clarification. 

insert.php

session_start();

$sql = "INSERT INTO `customer` (`Customer_ID`, `FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `HRD`, `SubscribeMailings`) VALUES (NULL, '$Firstname', '$Lastname', '$Address', '$Telephone', '$email', '$HRD', '$SubscribeMailings')";

$_SESSION["Customer_ID"] = "??";

 

insertreport.php

$sql = "INSERT INTO `problem` (`Problem_ID`, `Location`, `Description`, `Job_start`, `Customer_ID`, `Staff_ID`, `Priority_ID`, `Status_ID`, Solution_ID`, `Job_end`) VALUES (NULL, '$Location', '$Description', CURRENT_TIMESTAMP, '$_SESSION["Customer_ID"]', NULL, NULL, '', NULL, NULL)";

session_destroy();

Would it look something like this? Starts session on first insert php file and ends session on second insert php file. 
Another thing is how would i pull the auto-increment ID of Customer_ID which is generated in the database.

[Barand]

13 minutes ago, PHPnoobster said:

$_SESSION["Customer_ID"] = "??";

Have you read the edited version of my last post?

[PHPnoobster]

49 minutes ago, Barand said:

Have you read the edited version of my last post?

<?php

$Firstname = $_POST['Firstname'];
$Lastname = $_POST['Lastname'];
$Address = $_POST['Address'];
$Telephone = $_POST['Telephone'];
$email = $_POST['email'];
$SubscribeMailings = $_POST['SubscribeMailings'];

session_start();

include 'conn.php';

$sql = "INSERT INTO `customer` (`Customer_ID`, `FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `SubscribeMailings`) VALUES (?, ?, ?, ?, ?, ?, ?)";
$stmt = $pdo->prepare($sql);

$stmt->execute([ '$Firstname', '$Lastname', '$Address', '$Telephone', '$email', '$SubscribeMailings', '$']);
 

$_SESSION['Customer_ID'] = $pdo->LastInsertID();

$result = mysqli_query($conn, $sql);

if ($result){
    header('location: report.html'); 
    
    
            }
else{
    echo "details could not be inserted.<br />";
     }

?>

 

i get an error :

Notice: Undefined variable: pdo in C:\wamp64\www\insert.php on line 18

Fatal error: Uncaught Error: Call to a member function prepare() on null in C:\wamp64\www\insert.php on line 18

( ! ) Error: Call to a member function prepare() on null in C:\wamp64\www\insert.php on line 18

[Barand]

Sorry, I thought a name like "$pdo" would indicate it is a PDO connection.

Use whatever your connection is in conn.php.

(If you are not using PDO then I strongly recommend you change to it)

[Barand]

Apologies for my my lazy copying of your values from your query when creating the execute statement.

The single quotes should, of course, not be there

$stmt->execute( [ $Firstname, $Lastname, $Address, $Telephone, $email, $HRD, $SubscribeMailings ] );

 

[PHPnoobster]

$stmt->execute( [ $Firstname, $Lastname, $Address, $Telephone, $email, $SubscribeMailings ] );

Warning: mysqli_stmt::execute() expects exactly 0 parameters, 1 given in C:\wamp64\www\insert.php on line 20

$_SESSION['Customer_ID'] = $conn->LastInsertID();

Fatal error: Uncaught Error: Call to undefined method mysqli::LastInsertID() in C:\wamp64\www\insert.php on line 22

( ! ) Error: Call to undefined method mysqli::LastInsertID() in C:\wamp64\www\insert.php on line 22

 

 

[Barand]

Now you have decided to show the query execution code I can see you are using mysqli and not PDO. The functions are similar but not the same

You can find mysqli examples here

[PHPnoobster]

insert.php

$Firstname = $_POST['Firstname'];
$Lastname = $_POST['Lastname'];
$Address = $_POST['Address'];
$Telephone = $_POST['Telephone'];
$email = $_POST['email'];
$SubscribeMailings = $_POST['SubscribeMailings'];

session_start();

//echo "you are registered. Your name is " .$firstname . " and last name is " .$lastname. ; // displays when submit is pressed

include 'conn.php';  // using another php file to connect with database

$sql = "INSERT INTO `customer` (`Customer_ID`, `FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `SubscribeMailings`) VALUES (?, ?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);

$stmt->execute( [ $Firstname, $Lastname, $Address, $Telephone, $email, $SubscribeMailings ] );

$_SESSION['Customer_ID'] = $conn->LastInsertID();

//$sql = "INSERT INTO `customer` (`Customer_ID`, `FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `SubscribeMailings`) VALUES (NULL, '$Firstname', '$Lastname', '$Address', '$Telephone', '$email', '$SubscribeMailings')";
// inserting data into the customer table

$result = mysqli_query($conn, $sql); //result taking $conn to the $sql

//echo "You are registered. Your name is" .$firstname." and last name is " .$lastname.; // displays when submit is pressed

if ($result){
    header('location: report.html'); // if result is true bypasses to the page.
  
            }
else{
    echo "details could not be inserted.<br />";
     }

?>

 

conn.php

<?php

$host = "localhost";
$user = "root";
$pass = "";
$dbname  = "potholes";

//connect server

$conn = mysqli_connect($host, $user, $pass, $dbname) or die(); // connecting to my SQL

if( $conn ) {
     echo "<font color='green'>Online </font><br />";
            }
else{
     echo "Connection could not be established.<br/>";
     die( print_r( mysqli_errors(), true));
    }

?>

 

is there a better way to write this?

[Barand]

16 minutes ago, PHPnoobster said:

is there a better way to write this?

As you have a mysqli connection then using the mysqli functions (and not the PDO versions) would be a start. I gave you the link to the manual.

[Barand]

Now you've had time to digest the manual, you should have something like this...

$sql = "INSERT INTO `customer` (`FirstName`, `LastName`, `Address`, `Telephone`, `Email`, `SubscribeMailings`) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssss', $Firstname, $Lastname, $Address, $Telephone, $email, $SubscribeMailings);

if ($stmt->execute()) {
    $_SESSION['Customer_ID'] = $conn->insert_id;
    header('location: report.html'); // if result is true bypasses to the page.
}
else {
    $_SESSION['Customer_ID'] = null;
    echo "details could not be inserted.<br />";
}