9 Replies - 4190 Views - Last Post: 16 April 2012 - 10:11 AM

#1 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 759
  • View blog
  • Posts: 2,010
  • Joined: 23-December 08

Is javascript updating before mysql finishes?

Posted 10 April 2012 - 09:13 PM

Hey, I have a fun application I did for class the other day. I noticed a little bug in it though. I've already turned it in, but I want to know what I could be doing better for my own benefit.

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


Is This A Good Question/Topic? 0
  • +

Replies To: Is javascript updating before mysql finishes?

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3550
  • View blog
  • Posts: 10,324
  • Joined: 08-June 10

Re: Is javascript updating before mysql finishes?

Posted 11 April 2012 - 05:21 AM

in general--no*. either you have a valid postback or the server script times out and you get a timeout error (despite the possibility that you have a server error, for which case jQuery has the nice error option (when you don’t get a 2xx response header))



* - that’s the point with event driven languages, they wait until something happens.

This post has been edited by Dormilich: 11 April 2012 - 05:22 AM

Was This Post Helpful? 0
  • +
  • -

#3 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 759
  • View blog
  • Posts: 2,010
  • Joined: 23-December 08

Re: Is javascript updating before mysql finishes?

Posted 11 April 2012 - 11:11 AM

So, can you help me understand the behavior I'm seeing?

Those are the only files in my project, you could literally create them, put them on an *amp system and observe it yourself. The only update you'd need to make to the file is to set proper credentials for mysql in functions.php. It even creates the database dynamically.

This post has been edited by atraub: 11 April 2012 - 11:13 AM

Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6075
  • View blog
  • Posts: 23,543
  • Joined: 23-August 08

Re: Is javascript updating before mysql finishes?

Posted 11 April 2012 - 12:07 PM

Firebug errors:

POST http://localhost:8888/atraub/transaction.php 404 Not Found 17ms	jquery.min.js (line 4)
"NetworkError: 404 Not Found - http://localhost:8888/atraub/transaction.php"


Your file name is transactions.php.
Was This Post Helpful? 0
  • +
  • -

#5 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 759
  • View blog
  • Posts: 2,010
  • Joined: 23-December 08

Re: Is javascript updating before mysql finishes?

Posted 11 April 2012 - 02:45 PM

Haha come on guys, give me a break, that was just a typo in the initial post. :sweatdrop:

The website's behavior is perfect sometimes, but other times it feels like the ajax updates before the mysql query completes. If I had a bad file name, those queries wouldn't happen at all.

EDIT:
admittedly, my readme file contains the same typo, BUT that wouldn't cause those errors.

EDIT2:
Posted Image

This post has been edited by atraub: 12 April 2012 - 05:37 PM

Was This Post Helpful? 0
  • +
  • -

#6 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 759
  • View blog
  • Posts: 2,010
  • Joined: 23-December 08

Re: Is javascript updating before mysql finishes?

Posted 13 April 2012 - 09:55 AM

More specifics on behavior:

Start deleting users using the web interface. When a user is deleted, all vehicles associated with him should also delete and that HTML tables will update accordingly. What I find is that sometimes, the tables don't update accordingly. Also, the code creates the database dynamically when the page is first ran (if necessary), I find that sometimes the vehicle SQL table isn't ready when the vehicle HTML table is generated.
Was This Post Helpful? 0
  • +
  • -

#7 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4359
  • View blog
  • Posts: 12,176
  • Joined: 18-April 07

Re: Is javascript updating before mysql finishes?

Posted 13 April 2012 - 05:55 PM

Ok, so I have gone through the painstaking issue of setting all this up and I see what you are referring to. You have a synchronousation problem. Let me explain...

First off, your database should be updating fine. I am running it and it is doing all the edits I issue just fine and I confirmed this through the backend (phpMyAdmin).

Now the reason it appears that it sometimes doesn't update is because when you use the jQuery $.post() function you are executing the AJAX call asynchronously. This means it is calling the php file and not waiting for a reply back before continuing the javascript code. So you issue an add owner call which calls addOwner to insert into the database on the back end. While that is taking place, Javascript has already moved on to updating your table by calling the UpdateTable() function (I am talking about the updateOwnerData() function here).

To prove this is what happening, purposely delay the update of the table by throwing in a timer. Here is how I rigged up your method...

$.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,
    document.create_edit_owner.age.value,
    state: document.create_edit_owner.state.value
    },
    function(output){
        // Put in a 2 second delay timer and then call our update functionality.
        setTimeout(function() { updateTable2(output); },2000);
							
    });
  }else{
      alert("All fields must have a value, state must be a valid state, age must be a number between 1 and 254 inclusive");
   }
}
			
// Another function to update after delay
// Called after 2 seconds.		
function updateTable2(output) {
    $('#create_owner_result').html(output).show();
    updateTable("owner","ownerTable");
    populateOwnerSelects();	
}




So as you can see from the code above, we have put in a timer to call our update after 2 seconds. Now when we go to add an owner, it will asynchronously call out for the update, when it receives the reply it will wait 2 seconds and then update the table.

This is different than before where it was firing off the update and then jumping onward to the update table functionality before the response could be handled.

Now why did we do this than make $.post synchronous? Well, do we want the Javascript to block as it waits? What if the response is never returned? We don't want to put the user into a situation where they will be locked out for enternity. At least this way if the command did fail, we could still continue on and even do things while we waited for the response.

But that is what is happening all through your script. I am noticing it in other functions as well where you have a similar setup.

Hope this helps! :)
Was This Post Helpful? 0
  • +
  • -

#8 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 759
  • View blog
  • Posts: 2,010
  • Joined: 23-December 08

Re: Is javascript updating before mysql finishes?

Posted 13 April 2012 - 06:33 PM

Thanks man, I really appreciate you helping me out. What would be a better approach than $.post?
Was This Post Helpful? 0
  • +
  • -

#9 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6075
  • View blog
  • Posts: 23,543
  • Joined: 23-August 08

Re: Is javascript updating before mysql finishes?

Posted 14 April 2012 - 03:32 AM

There's nothing wrong with the $.post solution, except that everything you do with the response from the asynchronous call needs to be done within the scope of the anonymous function run when the Javascript returns.
Was This Post Helpful? 0
  • +
  • -

#10 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 759
  • View blog
  • Posts: 2,010
  • Joined: 23-December 08

Re: Is javascript updating before mysql finishes?

Posted 16 April 2012 - 10:11 AM

you guys were right, I put all the code into the anoynymous functions, and that did the trick, EXCEPT there's still 1 problem.

When I try to do a query, if the database isn't found, the database is auto-generated and populated with some default data. Here's the problem, when I do this auto-generation, the vehicles html table still gets generated before the MySQL table gets populated.

What do you guys think? This only happens when it has to generate the database. Here's updateTable

			function updateTable(name, whichTable) {
				$.post('structures.php', {	
										table: name
									},
					function(output){
						$('#'.concat(whichTable)).html(output).show();
					});
			}



To be safe, I even made the onReady function do a post:


			$(document).ready(function(){

				$.post('structures.php', {	
											table: "default"
										 },
						function(output){
							updateTable("owner","ownerTable");
							updateTable("vehicle","vehicleTable");
							
							populateOwnerSelects();
						});
			});



and here's the corresponding php code
		case "default":
			echo "do nothing";
		break;



It still doesn't seem to want to wait on that one. Below is the updated index.php and structures.php files (the other files were unchanged).

<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
									},
					function(output){					
						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
									}, 
					function(output){									
						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(){

				$.post('structures.php', {	
											table: "default"
										 },
						function(output){
							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>



<?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;
		
		case "default":
			echo "do nothing";
		break;
	}
?>




Thanks guys
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1