New Topic/Question
Reply
The assignment was to create a mysql database, put some information into it, and then be able to update it and view the updates (I paraphrase for brevity). I created everything just fine, and I decided that I want the site to be ajaxy. I put 2 tables reflecting data in the database, but for some reason, the tables only sometimes update with ajax. sometimes they do, sometimes they don't. I feel like the response might be getting sent back before mysql finishes updating, but I'm just speculating. This was my first time using ajax with jQuery, in the past I had to implement with pure javascript (assignment's rules, I wanted to use jQuery).
To run the site, just make sure you have mysql installed, you can adjust the security credentials in functions.php. I know that this site is very open to SQL injection, but that wasn't a concern for me for this assignment.
Here's the code:
index.php
<html>
<head>
<title>Admin</title>
<link rel="stylesheet" type="text/css" href="main.css" />
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<!-- owner functions -->
<script type="text/javascript">
var stateRegEx = /^((AL)|(AK)|(AS)|(AZ)|(AR)|(CA)|(CO)|(CT)|(DE)|(DC)|(FM)|(FL)|(GA)|(GU)|(HI)|(ID)|(IL)|(IN)|(IA)|(KS)|(KY)|(LA)|(ME)|(MH)|(MD)|(MA)|(MI)|(MN)|(MS)|(MO)|(MT)|(NE)|(NV)|(NH)|(NJ)|(NM)|(NY)|(NC)|(ND)|(MP)|(OH)|(OK)|(OR)|(PW)|(PA)|(PR)|(RI)|(SC)|(SD)|(TN)|(TX)|(UT)|(VT)|(VI)|(VA)|(WA)|(WV)|(WI)|(WY))$/;
function updateOwnerData() {
document.create_edit_owner.state.value = document.create_edit_owner.state.value.toUpperCase();
if(
document.create_edit_owner.first_name.value.length > 0 &&
document.create_edit_owner.last_name.value.length > 0 &&
document.create_edit_owner.age.value.length > 0 &&
parseInt(document.create_edit_owner.age.value) > 0 &&
parseInt(document.create_edit_owner.age.value) < 255 &&
stateRegEx.test(document.create_edit_owner.state.value)
){
$.post('transaction.php', {
type: "updateOwner",
OID: document.create_edit_owner.OID.value,
first_name: document.create_edit_owner.first_name.value,
last_name: document.create_edit_owner.last_name.value,
age: document.create_edit_owner.age.value,
state: document.create_edit_owner.state.value
},
function(output){
$('#create_owner_result').html(output).show();
});
updateTable("owner","ownerTable");
populateOwnerSelects();
}else{
alert("All fields must have a value, state must be a valid state, age must be a number between 1 and 254 inclusive");
}
}
function resetOwner(id){
document.create_edit_owner.OID.value = "-1";
document.create_edit_owner.first_name.value = "";
document.create_edit_owner.last_name.value = "";
document.create_edit_owner.age.value = "";
document.create_edit_owner.state.value = "";
document.loadOID.OID.value = "";
$('#load_owner_result').html("").show();
}
function getOwner(){
$.post('transaction.php', {
type: "loadOID",
OID: document.loadOID.OID.value
},
function(output){
var out = jQuery.parseJSON(output);
document.create_edit_owner.OID.value = out.OID;
document.create_edit_owner.first_name.value = out.first_name
document.create_edit_owner.last_name.value = out.last_name
document.create_edit_owner.age.value = out.age
document.create_edit_owner.state.value = out.state
$('#load_owner_result').html(out.message).show();
});
}
function deleteOwner() {
$.post('transaction.php', {
type: "deleteOwner",
OID: document.delete_owner.OID.value
});
updateTable("owner","ownerTable");
updateTable("vehicle","vehicleTable");
populateOwnerSelects();
}
function queryByOwner(){
$.post('structures.php', {
table: "ownerQuery",
OID: document.query_by_owner.OID.value
},
function(output){
$('#ownerQuery').html(output).show();
});
}
</script>
<!-- vehicle functions -->
<script type="text/javascript">
function updateVehicleData() {
if(
parseInt(document.create_edit_vehicle.year.value) > 1901 &&
parseInt(document.create_edit_vehicle.year.value) < 2155 &&
document.create_edit_vehicle.year.value.length > 0 &&
document.create_edit_vehicle.make.value.length > 0 &&
document.create_edit_vehicle.model.value.length > 0
){
$.post('transaction.php', {
type: "updateVehicle",
VID: document.create_edit_vehicle.VID.value,
OID: document.create_edit_vehicle.OID.value,
year: document.create_edit_vehicle.year.value,
make: document.create_edit_vehicle.make.value,
model: document.create_edit_vehicle.model.value,
color: document.create_edit_vehicle.color.value
},
function(output){
$('#create_vehicle_result').html(output).show();
updateTable("vehicle","vehicleTable");
});
}else{
alert("Year must be between 1901 and 2155 and all fields must have some value");
}
}
function deleteVehicle() {
$.post('transaction.php', {
type: "deleteVehicle",
VID: document.delete_vehicle.VID.value
});
updateTable("vehicle","vehicleTable");
}
function resetVehicle(id){
document.create_edit_vehicle.VID.value = "-1";
document.create_edit_vehicle.year.value = "";
document.create_edit_vehicle.make.value = "";
document.create_edit_vehicle.model.value = "";
document.create_edit_vehicle.color.value = "";
document.loadVID.VID.value = "";
$('#load_vehicle_result').html("").show();
}
function getVehicle(){
$.post('transaction.php', {
type: "loadVID",
VID: document.loadVID.VID.value
},
function(output){
var out = jQuery.parseJSON(output);
document.create_edit_vehicle.VID.value = out.VID;
document.create_edit_vehicle.year.value = out.year;
document.create_edit_vehicle.make.value = out.make;
document.create_edit_vehicle.model.value = out.model;
document.create_edit_vehicle.color.value = out.color;
for (var i = 0; i < document.create_edit_vehicle.OID.length; i++)
{
if (document.create_edit_vehicle.OID.options[i].value == out.OID)
{
document.create_edit_vehicle.OID[i].selected = true;
break;
}
}
$('#load_vehicle_result').html(out.message).show();
});
}
function queryByVehicle(){
$.post('structures.php', {
table: "vehicleQuery",
VID: document.query_by_vehicle.VID.value
},
function(output){
$('#vehicleQuery').html(output).show();
});
}
//validate age with ^([1-9][0-9]*)$
</script>
<!-- General functions -->
<script type="text/javascript">
$(document).ready(function(){
updateTable("owner","ownerTable");
updateTable("vehicle","vehicleTable");
populateOwnerSelects();
});
function updateTable(name, whichTable) {
$.post('structures.php', {
table: name
},
function(output){
$('#'.concat(whichTable)).html(output).show();
});
}
function populateOwnerSelects(){
$.post('structures.php', {
table: 'ownerSelectBox'
},
function(output){
$(".owners").html(output).show();
});
}
</script>
</head>
<body>
<div class="left">
<p>
This section is for creating a new owner or updating an existing one.<br />
If you want to create a new owner, there is no need to enter an ID.<br />
If you want to edit an existing owner, please load the owner using<br />
the owner ID in the 'Load Owner from ID' section.<br />
<form name="create_edit_owner">
First Name <input type="text" name="first_name" maxlength="15" /><br />
Last Name <input type="text" name="last_name" maxlength="15" /><br />
Age <input type="text" name="age" maxlength="3" size="3" /><br />
State <input type="text" name="state" maxlength="2" size="2" /><br />
ID<input type="text" name="OID" id="OID" value="-1" size="3" readonly />
<br />
<input type="button" value="Submit" onclick="updateOwnerData()">
<div id="create_owner_result"></div>
</form>
<hr />
Load Owner from ID<br />
Use this to load pre-existing owners for editing<br />
<form name="loadOID">
ID<input type="text" name="OID">
<input type="button" value="Load" onclick="getOwner()"><input type="button" value="Reset" onclick="resetOwner()">
<div id="load_owner_result"></div>
</form>
<hr />
All Owners<br />
<div id="ownerTable"></div>
<hr />
Delete Owner By ID
<form name="delete_owner">
ID<input type="text" name="OID">
<input type="button" value="Delete" onclick="deleteOwner()">
</form>
<hr />
Get All vehicles owned by a given owner
<form name="query_by_owner">
Owner ID<input type="text" name="OID">
<input type="button" value="Query" onclick="queryByOwner()">
</form>
<div id="ownerQuery" ></div>
</p>
</div>
<div class="right">
<p>
This section is for creating a new vehicle or updating an existing one.<br />
If you want to create a new vehicle, there is no need to enter an ID.<br />
If you want to edit an existing vehicle, please load the vehicle using<br />
the vehicle ID in the 'Load Vehicle from ID' section. This can also be<br />
used for changing the owner of an existing vehicle.<br />
<form name="create_edit_vehicle">
Year<input type="text" name="year" /><br />
Make <input type="text" name="make" maxlength="10" /><br />
Model <input type="text" name="model" maxlength="15" /><br />
Color <input type="text" name="color" maxlength="10" /><br />
Owner ID <span class="owners"></span><br />
ID<input type="text" name="VID" id="VID" value="-1" size="3" readonly />
<br />
<input type="button" value="Submit" onclick="updateVehicleData()">
<div id="create_vehicle_result"></div>
</form>
<hr />
Load Vehicle from ID<br />
Use this to load pre-existing vehicles for editing<br />
<form name="loadVID">
ID<input type="text" name="VID">
<input type="button" value="Load" onclick="getVehicle()"><input type="button" value="Reset" onclick="resetVehicle()">
<div id="load_vehicle_result"></div>
</form>
<hr />
All Vehicles<br />
<div id="vehicleTable"></div>
<hr />
Delete Vehicle By ID
<form name="delete_vehicle">
ID<input type="text" name="VID">
<input type="button" value="Delete" onclick="deleteVehicle()">
</form>
<hr />
Get the owner of a given vehicle
<form name="query_by_vehicle">
Vehicle ID<input type="text" name="VID">
<input type="button" value="Query" onclick="queryByVehicle()">
</form>
<div id="vehicleQuery" ></div>
</p>
</div>
</body>
</html>
functions.php
<?php
$username = "root";
$password = "";
$database = "main";
$servername = "localhost";
//open database connection
function getDatabaseConnection(){
$database = $GLOBALS['database'];
$conn = mysql_connect($GLOBALS['servername'],$GLOBALS['username'], $GLOBALS['password']);
if (!$conn) {
die("Error connecting to MySQL");
}
if(!mysql_select_db($GLOBALS['database']))
createDatabase($conn, $GLOBALS['database']);
return $conn;
}
//run query and return data
function query($query, $conn){
$result = mysql_query($query, $conn);
$data = array();
while($row = mysql_fetch_array($result)){
$data[] = $row;
}
return $data;
}
//close database connection
function closeDatabaseConnection($conn){
mysql_close($conn);
}
//connect to database, run query, close database, return query results
function runQuery($query){
$conn = getDatabaseConnection();
$results = query($query, $conn);
closeDatabaseConnection($conn);
return $results;
}
function createDatabase($conn, $database){
$query = array(
"DROP DATABASE IF EXISTS $database;",
"CREATE DATABASE $database;",
"CREATE TABLE $database.vehicle(
VID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(VID),
year YEAR NOT NULL,
make VARCHAR(10) NOT NULL,
model VARCHAR(15) NOT NULL,
color VARCHAR(10) NOT NULL
);",
"CREATE TABLE $database.owner(
OID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(OID),
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(15) NOT NULL,
age TINYINT(1) UNSIGNED,
state VARCHAR(2)
);",
//While a primary Key isn't required, it does protect data integrity
//It seems reasonable to suggest that a vehicle can have 1 and only 1 owner
//which makes VID a good primary key
"CREATE TABLE $database.owns(
OID INT NOT NULL,
VID INT NOT NULL PRIMARY KEY,
FOREIGN KEY(OID) REFERENCES owner(OID) ON DELETE CASCADE,
FOREIGN KEY(VID) REFERENCES vehicle(VID) ON DELETE CASCADE
);",
"INSERT INTO $database.vehicle
(VID, year, make, model, color)
VALUES
(1, 2005, 'Honda', 'Civic', 'silver'),
(2, 2009, 'Audi', 'A4', 'silver'),
(3, 1932, 'Ford', 'V8', 'black'),
(4, 1938, 'Volkswagen', 'Beetle', 'green'),
(5, 2008, 'Honda', 'Accord', 'blue'),
(6, 1964, 'Porsche', '911', 'red');",
"INSERT INTO $database.owner
(OID, first_name, last_name, age, state)
VALUES
(1, 'Hank', 'Hill', 40, 'VA'),
(2, 'John', 'Smith', 18, 'CA'),
(3, 'Jane', 'Jackson', 32, 'NJ'),
(4, 'Jennifer', 'Smith', 22, 'CA'),
(5, 'Bill', 'Gribble', 81, 'KY');",
"INSERT INTO $database.owns
(OID, VID)
VALUES
(1, 1),
(2, 4),
(3, 3),
(4, 2),
(5, 6),
(5, 5);"
);
for($i=0;$i<sizeof($query);$i++){
mysql_query($query[$i], $conn);
}
mysql_select_db($GLOBALS['database']);
}
function addOwner($firstName, $lastName, $age, $state){
$query =
"INSERT INTO owner
(first_name, last_name, age, state)
VALUES
('$firstName', '$lastName', $age, '$state');";
@runQuery($query);
}
function editOwner($id, $firstName, $lastName, $age, $state){
$query =
"UPDATE owner
SET
first_name = '$firstName',
last_name = '$lastName',
age = $age,
state = '$state'
WHERE
OID = $id;";
@runQuery($query);
}
function deleteOwner($id){
$query = "
DELETE FROM owner
WHERE OID = $id;
";
@runQuery($query);
}
function addVehicle($year, $make, $model, $color){
$query =
"INSERT INTO vehicle
(year, make, model, color)
VALUES
($year, '$make', '$model', '$color');";
@runQuery($query);
}
function editVehicle($id, $year, $make, $model, $color){
$query = "
UPDATE vehicle
SET
year = '$year',
make = '$make',
model = $model,
color = '$color'
WHERE
VID = $id;
";
@runQuery($query);
}
function deleteVehicle($id){
$query = "
DELETE FROM vehicle
WHERE VID = $id;
";
@runQuery($query);
}
function queryVehicle($VID){
$query="
SELECT v.*, o.OID
FROM vehicle v, owner o, owns
WHERE v.VID = owns.VID and o.OID = owns.OID and v.VID = $VID
";
return @runQuery($query);
}
function addOwns($OID){
$query = "
SELECT VID
FROM vehicle
ORDER by VID
DESC LIMIT 0,1
";
$v = @runQuery($query);
$VID = $v[0]["VID"];
$query = "
INSERT INTO owns
(OID, VID)
VALUES
($OID, $VID);
";
@runQuery($query);
}
function editOwns($OID, $VID){
$query = "
UPDATE owns
SET
OID = '$OID'
WHERE
VID = $VID;
";
@runQuery($query);
}
function getAllFromTable($table){
$query = "
SELECT *
FROM $table;
";
return @runQuery($query);
}
function getOwnerIDs(){
$query = "
SELECT OID as ID
FROM owner;
";
return @runQuery($query);
}
function getVehicleIDs(){
$query = "
SELECT VID as ID
FROM vehicle;
";
return @runQuery($query);
}
function queryTable($table, $columnName, $columnValue){
$query = "
SELECT *
FROM $table
WHERE $columnName = $columnValue;
";
return @runQuery($query);
}
function getCarsByOwner($OID){
$query ="
SELECT v.VID, v.year, v.make, v.model
FROM vehicle v
INNER JOIN owns o
ON v.VID = o.VID
WHERE o.OID = '$OID';
";
return @runQuery($query);
}
function getOwnerByVehicle($VID){
$query ="
SELECT first_name, last_name, age, state
FROM owner
INNER JOIN owns o
ON owner.OID = o.OID
WHERE o.VID = '$VID'
";
return @runQuery($query);
}
function getAllVehicles(){
$query ="
SELECT v.VID as 'Vehicle ID', v.year, v.make, v.model, v.color, o.OID AS 'Owner ID'
FROM vehicle v
INNER JOIN owns o
ON v.VID = o.VID
ORDER BY v.VID
";
return @runQuery($query);
}
function getAllOwners(){
$query ="
SELECT OID as 'Owner ID', first_name as 'first name',
last_name as 'last name', age, state
FROM owner
";
return @runQuery($query);
}
function buildHTMLTable($queryData){
$retval = "<table border='1'>";
if (sizeof($queryData) > 0){
$retval .= "<tr>";
$keys = array_keys($queryData[0]);
for($i=1;$i < sizeof($keys); $i+=2){
$retval .= "<th>" . $keys[$i] . "</th>";
}
for($a=0; $a < sizeof($queryData); $a++){
$retval .= "<tr>";
for($j=1; $j<sizeof($keys); $j+=2){
$retval .= "<td>".$queryData[$a][$keys[$j]]."</td>";
}
$retval .= "</tr>";
}
}
else{
$retval .="<tr><td>No data found</td></tr>";
}
$retval .= "</table>";
return $retval;
}
function buildSelectBox($vehicleOrOwner){
if($vehicleOrOwner == "owner"){
$data = getOwnerIDs();
$retval = '<select name="OID">';
}elseif($vehicleOrOwner == "vehicle"){
$data = getVehicleIDs();
$retval = '<select name="VID">';
}else{//error!
return null;
}
for($i=0; $i<sizeOf($data); $i++){
$retval .= '<option value="'.$data[$i]['ID'].'">'.$data[$i]['ID'].'</option>';
}
$retval .= '</select>';
return $retval;
}
?>
structures.php
<?php
include("functions.php");
switch($_POST['table']){
case "owner":
echo buildHTMLTable(getAllOwners());
break;
case "vehicle":
echo buildHTMLTable(getAllVehicles());
break;
case "ownerQuery":
echo buildHTMLTable(getCarsByOwner($_POST['OID']));
break;
case "vehicleQuery":
echo buildHTMLTable(getOwnerByVehicle($_POST['VID']));
break;
case "ownerSelectBox":
echo buildSelectBox("owner");
break;
case "vehicleSelectBox":
echo buildSelectBox("vehicle");
break;
}
?>
transactions.php
<?php
include('functions.php');
$type = $_POST['type'];
switch ($type){
case "updateOwner":
$OID = $_POST['OID'];
if($OID == "-1"){
addOwner($_POST['first_name'],$_POST['last_name'],$_POST['age'],$_POST['state']);
echo "new owner added";
}else{
editOwner($_POST['OID'],$_POST['first_name'],$_POST['last_name'],$_POST['age'],$_POST['state']);
echo "owner information updated";
}
break;
case "loadOID":
$OID = $_POST['OID'];
$data = queryTable('owner','OID',$OID);
if(sizeof($data) > 0){
$data = $data[0];
echo '{'.
'"first_name":'.json_encode($data["first_name"]).
',"last_name":'.json_encode($data["last_name"]).
',"age":'.json_encode($data["age"]).
',"state":'.json_encode($data["state"]).
',"message":'.json_encode("Successfully loaded owner").
',"OID":'.json_encode($data["OID"]).
'}';
}else{
echo '{'.
'"first_name":'.json_encode("").
',"last_name":'.json_encode("").
',"age":'.json_encode("").
',"state":'.json_encode("").
',"message":'.json_encode("No such owner!").
',"OID":'.json_encode("-1").
'}';
}
break;
case "updateVehicle":
$VID = $_POST['VID'];
if($VID == "-1"){
addVehicle($_POST['year'],$_POST['make'],$_POST['model'],$_POST['color']);
addOwns($_POST['OID']);
echo "new vehicle added";
}else{
editOwner($VID,$_POST['year'],$_POST['make'],$_POST['model'],$_POST['color']);
editOwns($_POST['OID'], $_POST['VID']);
echo "vehicle information updated";
}
break;
case "loadVID":
$VID = $_POST['VID'];
$data = queryVehicle($VID);
if(sizeof($data) > 0){
$data = $data[0];
echo '{'.
'"year":'.json_encode($data["year"]).
',"make":'.json_encode($data["make"]).
',"model":'.json_encode($data["model"]).
',"color":'.json_encode($data["color"]).
',"message":'.json_encode("Successfully loaded vehicle").
',"OID":'.json_encode($data["OID"]).
',"VID":'.json_encode($data["VID"]).
'}';
}else{
echo '{'.
'"year":'.json_encode("").
',"make":'.json_encode("").
',"model":'.json_encode("").
',"color":'.json_encode("").
',"message":'.json_encode("No such vehicle!").
',"OID":'.json_encode("1").
',"VID":'.json_encode("-1").
'}';
}
break;
case "deleteVehicle":
echo deleteVehicle($_POST['VID']);
break;
case "deleteOwner":
echo deleteOwner($_POST['OID']);
break;
}
?>
main.css
/*
Adam
CMSC 491: Web Programming
Spring 2012
validated against: http://jigsaw.w3.org/css-validator/
*/
div.left {
float: left;
width: 50%;
}
div.right {
float: right;
width: 50%;
}
Readme.txt
README Root document: index.php The database consists of 3 tables: vehicle owner owns vehicle: The vehicle table contains a listing of vehicles. A vehicle consists of a year, make, model, color, and a VID (vehicle ID). The VID is the primary key. Year must be a year between 1901 and 2155. Considering the Model T was developed in the 1920's, this seems like an acceptable constraint. default data: (VID, year, make, model, color) (1, 2005, 'Honda', 'Civic', 'silver') (2, 2009, 'Audi', 'A4', 'silver') (3, 1932, 'Ford', 'V8', 'black') (4, 1938, 'Volkswagen', 'Beetle', 'green') (5, 2008, 'Honda', 'Accord', 'blue') (6, 1964, 'Porsche', '911', 'red') owner: The owner table contains a listing of vehicle owners. An owner consists of a first name, a last name, an age, a state of residence, and an OID. The OID is the primary key. Age is an unsigned tinyint, which means that its values must be between 0 and 255 inclusive. Once again, this seems to be an acceptable constraint. default data: (OID, first_name, last_name, age, state) (1, 'Hank', 'Hill', 40, 'VA') (2, 'John', 'Smith', 18, 'CA') (3, 'Jane', 'Jackson', 32, 'NJ') (4, 'Jennifer', 'Smith', 22, 'CA') (5, 'Bill', 'Gribble', 81, 'KY') owns: The owns table maintains relationships between a given vehicle and its owner. This is accomplished by utilizing the primary keys of owner and vehicle, OID and VID. A given vehicle can only have one owner, but an owner can have many vehicles, thus creating a one to many relationship. While the book claims that a table like this does not need a primary key, most other sources would disagree. A primary key helps ensure data integrity and can simplify certain operations down the road. With this in mind, I chose to make VID the primary key. Because a vehicle can have only 1 owner, and it seems unlikely that a scenario exists where a vehicle has no owner, it seems reasonable to assume that for every vehicle there will be exactly 1 corresponding entry on the owns table. I also chose to make both foreign keys cascading. This accomplishes an otherwise difficult task: if a given owner is deleted, all cars associated with that owner will also be deleted. As I stated earlier, this is because a car cannot exist without an owner. default data: (OID, VID) (1, 1) (2, 4) (3, 3) (4, 2) (5, 6) (5, 5) The site consists of 5 pages. Index.php: This page contains all the javascript and acts as the front end. All server side communication is handled via Ajax using JQuery. functions.php: This page contains all the reusable code for the site. This code is independent of the structure of index.php. It defines most database communication which essentially also makes it a data access layer. structures.php: This page is used to create HTML tables and drop down lists from database data. It handles communication between index.php and functions.php because functions.php contains the code for creating those tables and lists. trasactions.php: This page handles communication between index.php and functions.php when the user wants to perform CRUD operations. IT is also used for getting data to populate text fields when the user wants to edit a vehicle or owner. main.css: This page defines 2 divs, a left aligned one and a right aligned one. This was used to create the 'split' design of the site.
This post has been edited by atraub: 10 April 2012 - 09:19 PM
MultiQuote
| 