Skip to main content

Kari Larson

MLIS ePortfolio

Mental Health Resources for the Homeless or Those With Critical Housing (PHP Scripting)

LIS 7963 Topics: Advanced Databases

The phase II paper (PDF) describes the purpose and structure of the database and website that use my PHP code. I wrote it about halfway through the project.

Go to my website, Everybody Deserves Support.

The blocks of code below are the PHP, HTML, and CSS from my project. The focus of the class and project was the PHP, so the HTML and CSS are not executed ideally.

The code I wrote can also be viewed in PDF format:

This is the code to the homepage of my site. I didn't add anything besides the navigation to this page.

<html>
 <ul>
  <li><a href="homepage.html">Home</a></li>
  <li><a href="findSupport.php">Find Support</a></li>
  <li><a href="addResource.php">Add a Resource</a></li>
  <li><a href="">About Homelessness</a></li>
  <li><a href="">About Mental Health</a></li>
</ul>

<head>
<title>Home</title>
<link rel="stylesheet" type="text/css" href="stylesheet.css"/>
</head>
<body>


<h3>Everybody Deserves Support<h3>
<h5>Resources for People Experiencing Housing & Mental Health Challenges<h5>

</body>
</html>

This is the code for the Find Support page.

<html>
 <ul>
  <li><a href="homepage.html">Home</a></li>
  <li><a href="findSupport.php">Find Support</a></li>
  <li><a href="addResource.php">Add a Resource</a></li>
  <li><a href="">About Homelessness</a></li>
  <li><a href="">About Mental Health</a></li>
</ul>

<head>
<title>Find Support</title>
<link rel="stylesheet" type="text/css" href="stylesheet.css"/>
</head>
<body>

<h3>Find Support</h3>

<p></p>
<h5>Click on each box to see a list of search options for its category. You can chose an option from one or both of the menus.<h5>
<p></p>

<?php

require_once "database_mhresources.php";
include_once "my_functions.php";

$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);

if ($conn->connect_error) die($conn->connect_error);

$droplist_query = "SELECT DISTINCT service_type FROM service
ORDER BY service_type";

$droplist_result = $conn->query($droplist_query);
if(!$droplist_result) die($conn->error);

$num_droplist_rows = $droplist_result->num_rows;

//post adds security
echo "<b>Select Service Type:</b>\n";
echo "<form method=\"post\" action=\"findSupport.php\">\n";
echo "	<select name=\"service_type_select\">\n";

echo "        <option value=\"all\">All</option>\n";
for ($i = 0; $i < $num_droplist_rows; $i++)
{
	$droplist_result->data_seek($i);
	$droplist_row = $droplist_result->fetch_array(MYSQLI_ASSOC);

	echo "        <option value=\"" . $droplist_row['service_type'] . "\">" . $droplist_row['service_type'] . "</option>\n";
}
echo " </select>";
echo "<br>";
echo "<br>";
//----------------------------start of second drop box?
$droplist_city_query = "SELECT DISTINCT city_name FROM city
ORDER BY city_name";

$droplist_city_result = $conn->query($droplist_city_query);
if(!$droplist_city_result) die($conn->error);

$num_droplist_city_rows = $droplist_city_result->num_rows;

//post adds security
echo "<b>Select City:</b>\n";
echo "<br>";
echo "<form method=\"post\" action=\"findSupport.php\">\n";
echo "	<select name=\"city_select\">\n";

echo "        <option value=\"all\">All</option>\n";
for($j = 0; $j < $num_droplist_city_rows; $j++)
{
	$droplist_city_result->data_seek($j);
	$droplist_city_row = $droplist_city_result->fetch_array(MYSQLI_ASSOC);

	echo "        <option value=\"" . $droplist_city_row['city_name'] . "\">" . $droplist_city_row['city_name'] . "</option>\n";
}

echo "	</select>\n";
echo "<br>";
echo "<br>";
//first "submit" is the command to do something, second "Submit" is what the button says
echo "	<input type =\"submit\" value=\"Submit\">\n";
echo "<form>\n";
//---------------------------------------end of form

$where_clause = "WHERE 1 = 1 ";

/*
if(isset($_POST['service_type_select']) && isset($_POST['city_select']))
{
	printp("You selected \"" . $_POST['service_type_select'] . "\"");

	if($_POST['service_type_select'] != "all")
	{
		$where_clause .= "AND service_type = '" . $_POST['service_type_select'] . "' ";
	}

	printp("You selected \"" . $_POST['city_select'] . "\"");

	if($_POST['city_select'] != "all")
	{
		$where_clause .= "AND city_name = '" . $_POST['city_select'] . "' ";
	}
}
*/



if(isset($_POST['service_type_select']))
{
	printp("You selected \"" . $_POST['service_type_select'] . "\" for service type.");

	if($_POST['service_type_select'] != "all")
	{
		$where_clause .= "AND service_type = '" . $_POST['service_type_select'] . "' ";
	}
}

if(isset($_POST['city_select']))
{
	printp("You selected \"" . $_POST['city_select'] . "\" for city.");

	if($_POST['city_select'] != "all")
	{
		$where_clause .= "AND city_name = '" . $_POST['city_select'] . "' ";
	}
}



$query = <<<_QUERY
SELECT r.resource_name, s.service_type, l.address, c.city_name, z.zip_code, l.phone, l.fax, l.website
FROM resource r
	LEFT OUTER JOIN location l
	ON r.location_id = l.location_id
	LEFT OUTER JOIN service s
	ON s.service_id = r.service_id
	LEFT OUTER JOIN zip_code z
	ON z.zip_code = l.zip_code
	LEFT OUTER JOIN  city c
	ON c.city_id = l.city_id

_QUERY;

$query .= $where_clause ;

$query .= "ORDER BY service_type ASC, city_name, resource_name";

$result = $conn->query($query);
if(!$result) die($conn->error);

$num_rows = $result->num_rows;

print "\n";

if($num_rows > 0)
{
print "<table>\n";
print "    <tr>\n";
print "        <th>Resource Name</th>\n";
print "        <th>Service Type</th>\n";
print "        <th>Address</th>\n";
print "        <th>City</th>\n";
print "        <th>Zip Code</th>\n";
print "        <th>Phone Number</th>\n";
print "        <th>Fax Number</th>\n";
print "        <th>Website</th>\n";
print "    </tr>\n";
}
else
{
echo "<b>Sorry, no results found</b>\n";
}

for($i = 0; $i < $num_rows; $i++)
{
    $result->data_seek($i);
    $row = $result->fetch_array(MYSQLI_NUM);
    $num_cols = $result->field_count;

    echo "    <tr>\n";

    for($j = 0; $j < $num_cols; $j++)
    {
        echo "        <td>" . $row[$j] . "</td>\n";
    }

    echo "    </tr>\n";
}

if($num_rows > 0)
{
print "</table>\n";
}

$result->close();


$conn->close();


?>

</body>
</html>

This is the code for the Add a Resource page.

<html>
<head>
    <title>MHResources Add Resource</title>
    <link rel="stylesheet" type="text/css" href="stylesheet.css"/>
</head>
 <ul>
  <li><a href="homepage.html">Home</a></li>
  <li><a href="findSupport.php">Find Support</a></li>
  <li><a href="addResource.php">Add a Resource</a></li>
  <li><a href="">About Homelessness</a></li>
  <li><a href="">About Mental Health</a></li>
</ul>


<body>

<h3>MHResources Add Resource</h3>

<form action="addResource.php" method="post">
    Resource Name:
    <br>
    <input type="text" name="resource_name" required="required">
    <br>
    <br>
    Street Address:
    <br>
    <input type="text" name="street_address">
    <br>
    <br>
    City:
    <br>
    <input type="text" name="city">
    <br>
    <br>
     State (Postal Abbreviation):
    <br>
    <input type="text" name="state_abbreviation">
    <br>
    <br>
     Zip Code:
    <br>
    <input type="text" name="zip">
    <br>
    <br>
     Phone Number:
    <br>
    <input type="text" name="phone_number" placeholder="000-000-0000">
    <br>
    <br>
     Fax Number:
    <br>
    <input type="text" name="fax_number" placeholder="000-000-0000">
    <br>
    <br>
     Website URL:
    <br>
    <input type="text" name="website_url">
    <br>
    <br>

    Database Action:
    <br>
    <input type="radio" name="database_action" value="insert">Add Resource <br>

    <br>

    <br>
    <input type="submit" name="submit" value="Submit">
    <br>
</form>

<?php
//if radio buttons have same name, they are considered grouped. Must have different values
include_once "my_functions.php";
require_once "database_mhresources.php";
include "mhresources_get_post_data.php";

$where_clause = "WHERE 1 = 1 ";

$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);

if ($conn->connect_error) die($conn->connect_error);



if($database_action == "insert")
{
	$city_id = "";

	if(!empty($city))
	{
		$find_query = "SELECT city_id FROM city WHERE city_name = '" . $city . "' ";

		$find_result = $conn->query($find_query);

		if($find_result->num_rows > 0)
		{
			$find_result->data_seek(0);
			$find_row = $find_result->fetch_array(MYSQLI_ASSOC);
			$city_id = $find_row['city_id'];
		}
		else
		{
			$insert_query = "INSERT INTO city (city_name) VALUES ('" . $city . "')";

			if($conn->query($insert_query) == TRUE)
			{
				printp("City successfully added.");
			}
			else
			{
				printp($conn->error);
			}

			$find_query = "SELECT city_id FROM city WHERE city_name = '" . $city . "' ";

			$find_result = $conn->query($find_query);
			$find_result->data_seek(0);
			$find_row = $find_result->fetch_array(MYSQLI_ASSOC);
			$city_id = $find_row['city_id'];
		}
	}

	if(!empty($zip))
	{
		$find_query = "SELECT zip_code FROM zip_code WHERE zip_code = '" . $zip . "' ";

		$find_result = $conn->query($find_query);

		if($find_result->num_rows == 0)
		{
			$insert_query = "INSERT INTO zip_code (zip_code) VALUES ('" . $zip . "')";

			if($conn->query($insert_query) == TRUE)
			{
				printp("Zip code successfully added.");
			}
			else
			{
				printp($conn->error);
			}
		}
	}

	$insert_query = "";

	if(empty($city_id))
	{
		$insert_query = "INSERT INTO location (address, zip_code, state, phone, website, fax) VALUES ('" . $street_address . "', '" . $zip . "', '" . $state_abbreviation . "', '" . $phone_number . "', '" . $website_url . "', '" . $fax_number . "')";

		$find_result = $conn->query($find_query);
		$find_result->data_seek(0);
		$find_row = $find_result->fetch_array(MYSQLI_ASSOC);
		$location_id = $find_row['location_id'];
	}
	else
	{
		$insert_query = "INSERT INTO location (address, city_id, zip_code, state, phone, website, fax) VALUES ('" . $street_address . "', " . $city_id . ", '" . $zip . "', '" . $state_abbreviation . "', '" . $phone_number . "', '" . $website_url . "', '" . $fax_number . "')";
	}

	//printp($insert_query);

	if($conn->query($insert_query) == TRUE)
	{
		//printp("Location successfully added.");
	}
	else
	{
		printp($conn->error);
	}

	$find_query = "";

	if(empty($city_id))
	{
		$find_query = "SELECT location_id FROM location WHERE 1 = 1 AND address = '" . $street_address . "' AND zip_code = '" . $zip . "' AND state = '" . $state_abbreviation . "' AND phone = '" . $phone_number . "' AND website = '" . $website_url . "' AND fax = '" . $fax_number ."' LIMIT 1";

	}
	else
	{
		$find_query = "SELECT location_id FROM location WHERE 1 = 1 AND address = '" . $street_address . "' AND zip_code = '" . $zip . "' AND state = '" . $state_abbreviation . "' AND phone = '" . $phone_number . "' AND website = '" . $website_url . "' AND fax = '" . $fax_number ."' AND city_id = " . $city_id . " LIMIT 1";
	}

	//printp($find_query);

	$find_result = $conn->query($find_query);
	$find_result->data_seek(0);
	$find_row = $find_result->fetch_array(MYSQLI_ASSOC);
	$location_id = $find_row['location_id'];

	// printp($location_id);

	$insert_query = "INSERT INTO resource (resource_name, location_id) VALUES ('" . $resource_name . "', " . $location_id . ")";

	//printp($insert_query);

	if($conn->query($insert_query) == TRUE)
	{
		printp("Resource successfully added.");
	}
	else
	{
		printp($conn->error);
	}
}

$query = <<<_QUERY
SELECT r.resource_name, s.service_type, l.address, c.city_name, z.zip_code, l.phone, l.fax, l.website
FROM resource r
	LEFT OUTER JOIN location l
	ON r.location_id = l.location_id
	LEFT OUTER JOIN zip_code z
	ON z.zip_code = l.zip_code
	LEFT OUTER JOIN  city c
	ON c.city_id = l.city_id
	LEFT OUTER JOIN service s
	ON s.service_id = r.service_id
_QUERY;

$result = $conn->query($query);
if(!$result) die($conn->error);

$num_rows = $result->num_rows;

//print "<table>\n";

print "\n";

print "<table>\n";
print "    <tr>\n";
print "        <th>Resource Name</th>\n";
print "        <th>Service Type</th>\n";
print "        <th>Address</th>\n";
print "        <th>City</th>\n";
print "        <th>Zip Code</th>\n";
print "        <th>Phone Number</th>\n";
print "        <th>Fax Number</th>\n";
print "        <th>Website</th>\n";
print "    </tr>\n";

for($i = 0; $i < $num_rows; $i++)
{
    $result->data_seek($i);
    $row = $result->fetch_array(MYSQLI_NUM);
    $num_cols = $result->field_count;

    echo "    <tr>\n";

    for($j = 0; $j < $num_cols; $j++)
    {
        echo "        <td>" . $row[$j] . "</td>\n";
    }

    echo "    </tr>\n";
}

print "</table>\n";

$result->close();
$conn->close();
?>

</body>
</html>

This is the code for the Get Post Data file, which get the data that a user enters into the form on the Add a Resource page.

<?php

$resource_name = "";
$street_address = "";
$city = "";
$state_abbreviation = "";
$zip = "";
$phone_number = "";
$fax_number = "";
$website_url = "";
$database_action = "";

if(isset($_POST['resource_name']) && !empty($_POST['resource_name']))
{
    //printp("Resource Name: \"" . $_POST['resource_name'] . "\"");

    $resource_name = $_POST['resource_name'];
}

if(isset($_POST['street_address']) && !empty($_POST['street_address']))
{
    //printp("Street Address: \"" . $_POST['street_address'] . "\"");

    $street_address = $_POST['street_address'];
}

if(isset($_POST['city']) && !empty($_POST['city']))
{
    //printp("City: \"" . $_POST['city'] . "\"");

    $city = $_POST['city'];
}

if(isset($_POST['state_abbreviation']) && !empty($_POST['state_abbreviation']))
{
    //printp("State (Postal Abbreviation): \"" . $_POST['state_abbreviation'] . "\"");

    $state_abbreviation = $_POST['state_abbreviation'];
}

if(isset($_POST['zip']) && !empty($_POST['zip']))
{
    //printp("Zip Code: \"" . $_POST['zip'] . "\"");

    $zip = $_POST['zip'];
}

if(isset($_POST['phone_number']) && !empty($_POST['phone_number']))
{
    //printp("Phone Number: \"" . $_POST['phone_number'] . "\"");

    $phone_number = $_POST['phone_number'];
}

if(isset($_POST['fax_number']) && !empty($_POST['fax_number']))
{
    //printp("Fax Number: \"" . $_POST['fax_number'] . "\"");

    $fax_number = $_POST['fax_number'];
}

if(isset($_POST['website_url']) && !empty($_POST['website_url']))
{
    //printp("Website URL: \"" . $_POST['website_url'] . "\"");

    $website_url = $_POST['website_url'];
}


if(isset($_POST['database_action']) && !empty($_POST['database_action']))
{
    //printp("Database Action: \"" . $_POST['database_action'] . "\"");

    $database_action = $_POST['database_action'];
}


?>

This is the code for the My Functions file, which is a PHP function I created and was able to reuse throughout the project.

<?php

function printp ($to_print)
{
	echo "<p>" . $to_print . "</p>\n";
}


?>

This is the code for the Stylesheet file, which is the CSS I used to style my website.

ul
{
    list-style-type: none;
    margin: 0;
    padding: 0;
    overflow: hidden;
    background-color: #d8f3d8;
}

li
{
    float: left;
}

li a
{
    display: block;
    color: white;
    padding: 8px 16px;
    text-decoration: none;
}

li .active
{
    background-color: #4CAF50;

}


li a:hover:not(.active)
{
    /* background-color: #111; */
    color: #ff0000;
}

body
{
    font-family : "Arial";
    /* background-color : #F0F0F0; */
    background-color : white;
}

h3
{
    font-family : "Arial";
   /* background-color : #53c653; */
    color : #000000;
    padding : 5 5 5 5;
    font-size : 24px;
}

h4
{
    font-family : "Arial";
    font-size : 20px;
    font-weight : bold;
}

h5
{
    font-family : "Arial";
    font-size : 16px;
    font-weight: normal;

}

a
{
    text-decoration : none;
}

a:link /* unvisited link */
{
    color : #491A6A;
}

a:visited /* visited link */
{
    color : #491A6A;
}

a:hover /* mouse over link */
{
    color : #ff0000;
}

a:active /* selected link */
{
    color : #CCCC00;
    background: #3cc33c;
}

table
{
    border : 2px solid black;
    border-collapse : collapse;
    margin-left : auto;
    margin-right : auto;
}

tr:hover {background-color: #e6e6e6}
{
    border : 2px solid black;
    border-collapse : collapse;
    padding : 5 5 5 5;
}

td
{
    border : 2px solid black;
    border-collapse : collapse;
    padding : 5 5 5 5;
    text-align : left;
}

th
{
    border : 2px solid black;
    border-collapse : collapse;
    padding : 5 5 5 5;
    text-align : left;
    background-color: #9fdf9f
}