Home

PHP / MySQL

Session Prices

Feel free to negotiate depending on your situation.

Scheduling

Hit me up to work out a scheduled time and date.
[email protected]
213-995-7078 text or voice
© Copyright 2021 by Jeff Clemmer
All rights reserved.
Inserting A Form Into Multiple Tables

Our previous example only allowed us to save two fields into one table, but we have three tables. Let's expand on that and create an entire form and then insert the entire record into the database.

Let's update our form to add all the fields that we want to save.

This code should be put into indexview.php in your webroot directory. This is just a snippet that should be inserted into the code.

<form action="index.php" method="post">
	<input type="hidden" name="do" value="saveNewEmployee">
	<div>Name</div>
	<div>
		<input type="text" name="firstName" placeholder="first name">
		<input type="text" name="lastName" placeholder="last name">
		<input type="text" name="salary" placeholder="salary $">
	</div>
	<div>Address</div>
	<div>
		<input type="text" name="street" placeholder="street">
		<input type="text" name="city" placeholder="city">
		<input type="text" name="state" placeholder="state">
		<input type="text" name="zip" placeholder="zip">
	</div>
	<div>Phone</div>
	<div>
		<input type="text" name="number" placeholder="number">
		<select name="type">
			<option>Cell</option>
			<option>Home</option>
			<option>Work</option>
		</select>
	</div>
	<input type="submit" value="Save">
</form>

We added some new fields here to allow the user to add the new information. The new fields are contained within a few divs with some field labels that break the form down into a more managable format for the user.

For our next step, we need to update our validation in our controller. We have a lot of extra validation to do, so let's get to work!

This code should be put into index.php in your webroot directory. Insert this into your validation code.

$salary = "";
if (isset($_POST["salary"]) == true && $_POST["salary"] != "") {
	$salary = filter::salary($_POST["salary"]);
}

$street = "";
if (isset($_POST["street"]) == true && $_POST["street"] != "") {
	$street = filter::street($_POST["street"]);
}

$city = "";
if (isset($_POST["city"]) == true && $_POST["city"] != "") {
	$city = filter::city($_POST["city"]);
}

$state = "";
if (isset($_POST["state"]) == true && $_POST["state"] != "") {
	$state = filter::state($_POST["state"]);
}

$zip = "";
if (isset($_POST["zip"]) == true && $_POST["zip"] != "") {
	$zip = filter::zip($_POST["zip"]);
}

$number = "";
if (isset($_POST["number"]) == true && $_POST["number"] != "") {
	$number = filter::number($_POST["number"]);
}

$type = "";
if (isset($_POST["type"]) == true && $_POST["type"] != "") {
	$type = filter::type($_POST["type"]);
}

if ($lastName != "" && $firstName != "" && $salary != "" && $street != "" && $city != "" && $state != "" && $zip != "" && $number != "" && $type != "") {
	IndexModels::saveNewEmployee($lastName, $firstName, $salary, $street, $city, $state, $zip, $number, $type);
}

We added quite a bit of new validation here. But, basically, it's similar to the previous validation. There's just more of it. It seems redundant, and it is, but necessary. Another way to validate would be to check for string lengths against what you know from the database schema. We've also added all the fields to our final if statement before we run our insert model. This effectively makes the fields required. Required fields are usually marked on the form so the user knows what they must fill in.

On a side note, why do we check if the $_POST variable isset? We do this because we want our software to run anywhere. There are modes of PHP that make things stricter and if we tried to test the variable ($_POST["type"] != "") before we did isset and that variable didn't exist, we might get an error that shuts down our program. We might also get a notice. Technically, notices don't shut down our program, but they can start filling up our log files and that can get old really fast. It's good to try and write software that doesn't spit out any error or notice messages at all. However, there are certain instances where we can't get around that.

Now, let's update our filter code and filter a few more fields.

This code should be put into filter.php in your webroot directory. Insert this after the names method.

static public function salary($in) {
	// remove anything but numbers
	return preg_replace("/[^0-9]/", "", $in);
}

static public function street($in) {
	// trim whitespace from both ends
	$out = trim($in);
	// filter out everything, except letters, dashes, hash chars, and spaces via a "regular expression"
	$out = preg_replace("/[^A-Za-z0-9\-\#\ ]/", "", $out);
	// uppercase first letter of each word
	$out = ucwords($out);
	
	return $out;
}

static public function city($in) {
	// trim whitespace from both ends
	$out = trim($in);
	// filter out everything, except letters and spaces via a "regular expression"
	$out = preg_replace("/[^A-Za-z\ ]/", "", $out);
	// uppercase first letter of each word
	$out = ucwords($out);
	
	return $out;
}

static public function state($in) {
	// filter out everything, except letters via a "regular expression"
	$out = preg_replace("/[^A-Za-z]/", "", $in);
	// uppercase everything
	$out = strtoupper($out);
	
	return $out;
}

static public function zip($in) {
	// remove anything but numbers and dashes
	return preg_replace("/[^0-9\-]/", "", $in);
}

static public function number($in, $defaultAreaCode = "213") {
	$out="";
	
	// remove anything but numbers
	$number = preg_replace("/[^0-9]/", "", $in);
	
	// if the phone number is 7 digits
	$isCorrectFormat = false;
	if (strlen($number) == 7) {
		$out = $defaultAreaCode . "-" . substr($number, 0, 3) . "-" . substr($number, 3, 4);
		$isCorrectFormat = true;
	}
	
	// if the phone number is 10 digits
	if (strlen($number) == 10) {
		$out = substr($number, 0, 3) . "-" . substr($number, 3, 3) . "-" . substr($number, 6, 4);
		$isCorrectFormat = true;
	}
	
	if ($isCorrectFormat == true) {
		return $out;
	} else {
		return "";
	}
}

static public function type($in) {
	if ($in == "Cell" || $in == "Home" || $in == "Work") return $in;
	return "";
}

In our filter methods, we have a lot of filtering that doesn't throw an error. Why is that? This is my personal philosophy: I want the computer to do as much work as we can get it to do. If someone accidentally adds a letter into the salary field and doesn't see it, I don't want to throw an error if it's possible. I'd personally rather just filter that extra stuff out and move forward. It can be more efficient that way.

Of course, this can also lead to some pitfalls as well. For example, someone might have wanted to give the employee a salary of 100, but instead they accidentally used capital O as zeros which would look like 1OO. The salary would be filtered and now the salary contains just 1. In this case, you might want to show a notice saying something about it. We could also go even further in our filter function and change O into 0.

With phone numbers, it's a little different. I'd rather filter things out and put everything into a standard format so that people can move on with their lives. When it comes to phone numbers, people to tend to write all kinds of different formats. With our filter function, we take any format, and if the user typed in the right amount of digits, we'll turn it into our required format. No sense in keeping users in front of their computers longer than they need to be. Also, users will eventually get the hint that they can't type whatever they want. But, we don't want to force them. It's nice to nudge them about the problem.

Before we can do any SQL inserts, we need to add another method to our db class.

This code should be put into db.php in your webroot directory. Add this to the end of the db class.

public function lastInsertId() {
	return $this->db->lastInsertId();
}

This last method is simple. We basically pass through the result of lastInsertId to the caller. We'll use this in our database model below.

And last, we need to update our model to accept the new fields.

This code should be put into filter.php in your webroot directory. Replace the already existing method with the following.

static public function saveNewEmployee($lastName, $firstName, $salary, $street, $city, $state, $zip, $number, $type) {
	$db = new db;
	$query = $db->query(
		"insert into names set lastName=:lastName, firstName=:firstName, salary=:salary",
		[ ":lastName"=>$lastName, ":firstName"=>$firstName, ":salary"=>$salary, ]
	);
	
	// get the nameId that was created in the previous insert query
	$nameId = $db->lastInsertId();
	
	$db->query(
		"insert into addresses set nameId=:nameId, street=:street, city=:city, state=:state, zip=:zip",
		[ ":nameId"=>$nameId, ":street"=>$street, ":city"=>$city, ":state"=>$state, ":zip"=>$zip, ]
	);
	
	$db->query(
		"insert into phones set nameId=:nameId, number=:number, phoneType=:phoneType",
		[ ":nameId"=>$nameId, ":number"=>$number, ":phoneType"=>$type, ]
	);
	
}

Now that we have all this information in our database, let's do a join and show the phone number for the employee.

In our IndexModels class, under the index method, let's change our query from

select lastName, firstName from names

to

select lastName, firstName, number, phoneType from names n, phones p where n.id=p.nameId

And, in our IndexViews, under the method, index, let's change from

foreach ($employees as $res) {
	echo "
{$res["lastName"]}, {$res["firstName"]}
\n";
}

to

foreach ($employees as $res) {
	echo "
{$res["lastName"]}, {$res["firstName"]}, {$res["number"]}, {$res["phoneType"]}
\n";
}