Jump to content
PHPnoobster

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
13 minutes ago, PHPnoobster said:

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

Have you read the edited version of my last post?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

$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

 

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.