php pdo mysql id to two different tables help

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

Thu May 17, 2018 12:09 am

I am building a admin page where I can create a repair job and it also creates a username and password. I have that side all working ok, the issue I am facing is in the first db table called repairs it has memberID and id, the id is the repair job id which is auto increment and memberID is not auto increment. The second table called members stores the username and password and that has the memberID column which is auto increment. I need to link the two together as I want users to login and once they have logged in, they can see their repair job details and status.

Is there a way the memberID from the members table could be added to the repairs table so they match up and the customer can see their repair job details and status only and not anyone elses, below is the code I have

Code: Select all

/*

           NEW RECORD

        */
		
	// if the 'id' variable is not set in the URL, we must be creating a new record
	else
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			
			// get the form data
			$customer_name = htmlentities($_POST['customer_name'], ENT_QUOTES);
			$customer_email = htmlentities($_POST['customer_email'], ENT_QUOTES);
			$customer_phone = htmlentities($_POST['customer_phone'], ENT_QUOTES); 
			$computer_make = htmlentities($_POST['computer_make'], ENT_QUOTES);
			$computer_model = htmlentities($_POST['computer_model'], ENT_QUOTES);
			$technician = htmlentities($_POST['technician'], ENT_QUOTES);
			$status = htmlentities($_POST['status'], ENT_QUOTES);
			$exrdate = htmlentities($_POST['exrdate'], ENT_QUOTES);
			$exrtime = htmlentities($_POST['exrtime'], ENT_QUOTES);
			$exstdate = htmlentities($_POST['exstdate'], ENT_QUOTES);
			$exstime = htmlentities($_POST['exstime'], ENT_QUOTES);
			$deltype = htmlentities($_POST['deltype'], ENT_QUOTES);
			$comments = htmlentities($_POST['comments'], ENT_QUOTES);
			$job_cost = htmlentities($_POST['job_cost'], ENT_QUOTES);
			$part_cost = htmlentities($_POST['part_cost'], ENT_QUOTES);
			$profit = htmlentities($_POST['profit'], ENT_QUOTES);
			$username = htmlentities($_POST['username'], ENT_QUOTES);
			$password = htmlentities($_POST['password'], ENT_QUOTES);
			
			// check that firstname and lastname are both not empty
			if ($customer_name == '' || $computer_make == '' || $computer_model == '' || $comments == '' || $username == '' || $password == '')
			{
				// if they are empty, show an error message and display the form
				$error = 'ERROR: Please fill in all required fields!';
	renderForm($customer_name, $computer_make, $computer_model, $comments, $username, $password, $error);
			}
			else
			{
				// insert the new record into the database

				if ($stmt = $mysqli->prepare("INSERT repairs (customer_name, customer_email, customer_phone, computer_make, computer_model, technician, status, exrdate, exrtime, exstdate, exstime, deltype, comments, job_cost, part_cost, profit) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"))
				{
					$stmt->bind_param("ssssssssssssssss", $customer_name, $customer_email, $customer_phone, $computer_make, $computer_model, $technician, $status, $exrdate, $exrtime, $exstdate, $exstime, $deltype, $comments, $job_cost, $part_cost, $profit);
					$stmt->execute();
					
					//hash the password
		$password = password_hash($password, PASSWORD_DEFAULT);
					
					if ($stmt = $mysqli->prepare("INSERT members (username, password) VALUES (?, ?)"))
					{
						$stmt->bind_param("ss", $username, $password);
						$stmt->execute();
					$stmt->close();
Thank you in advance

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

Thu May 17, 2018 2:48 am

I have sorted this issue but doing it another way by adding customer_ref into the two tables

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

Thu May 17, 2018 8:06 am

Maybe a bit late, but if you have 2 tables:

repairs:
memberID, jobID

members:
memberID, username, password

Then all you need to do is

Code: Select all

$db->prepare('SELECT jobID FROM repairs WHERE memberID = ?');
You might want to add more fields for date, phone number, maybe a description of the repair etc..? but that's all you need for your initial requirements - unless I've missed something?

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

Fri May 18, 2018 7:29 am

Thank you for the reply, got that side of it sorted

Post Reply