query.php

<?php
	//-- Authored by Mike
	session_start();
	# Script for running queries for ajax request
	$sql = $_POST['query'];
	
	if(strpos($sql, 'SELECT') !== false){ //-- Runs if we're running a select statement
		printTable($sql);
		echo "<button type='button' onclick='saveFile()' id='csv'>Export CSV</button>";
	}
	else{ //-- Otherwise, for inserts
		$con = connectDB();
		$result = mysqli_query($con, $sql);
		if (!$result) {
			echo "<br><div class='alert alert-danger' role='alert'>Insert failed!</div>";
		}
		else{
			echo "<br><div role='alert' class='alert alert-success'>Registration successful!</div>";
		}
		disconnectDB($con);
		return;
	}
	
	function printTable($sql){
		//echo $_POST['query'] . "<br><br>"; // see query if the request didn't work
		$con = connectDB();
		
		//-- Sanitize input before querying database
		//-- For some reason this makes MySQL mad at the moment
		//$sql = mysqli_real_escape_string($con, $_POST['query']);
		$result = mysqli_query($con, $sql);
		$allKeys = array();
		
		if($row = mysqli_fetch_array($result)) { // first record
			$allKeys = array_keys($row);
		}
		else{ // We have no results from this query
			echo "<br><div class='alert alert-danger' role='alert' id='enterState'>No results.</div>";
			disconnectDB($con);
			return;
		}
		
		$keys = array();
		for($x = 1; $x < count($allKeys); $x += 2){
			array_push($keys, $allKeys[$x]);
		}
		
		echo "<table class='table table-striped' id='stateTable' style='display: none'><tr>";
		for($x = 0; $x < count($keys); $x++){
			if(is_numeric($keys[$x]))
				continue;
			echo "<th>" . $keys[$x] . "</th>";
		}
		echo "</tr>";
		
		$result = mysqli_query($con, $sql); // Make sure we get all records
		while($row = mysqli_fetch_array($result)) {
			echo "<tr>";
			for($x = 0; $x < count($keys); $x++){
				if(is_numeric($keys[$x]))
					continue;
					
				echo "<td>" . $row[$keys[$x]] . "</td>";
			}
			echo "</tr>";
		}
		
		echo "</table>";
		disconnectDB($con);
	}
	
	function connectDB(){
		//-- databasesproject is the name of my database in my localhost that we will access.
		//-- the tables must be loaded into localhost before querying them; do this by running the same query as in mysql
		//-- after making the database
		// I had to make the password 'root' to test, I think I changed it back correctly - Drew
		$user = "root";
		$pass = "";
		$con = mysqli_connect("localhost", $user, $pass, "databasesproject") or die(mysql_error());
		return $con;
	}
	
	function disconnectDB($con){
		mysqli_close($con);
	}
?>

<script>
	function saveFile(){
		var data = <?php echo json_encode($_SESSION['out']); ?>;
		var csvContent = 'data:text/csv;charset=UTF-8,';
		
		var arr = <?php echo json_encode($_SESSION['arrayNames']); ?>;
		csvContent += arr.join(",") + "\n";
		console.log(csvContent);
		
		data.forEach(function(infoArray, index){
			dataString = infoArray.join(",");
			csvContent += index < data.length ? dataString + "\n" : dataString;
		});

		var encodedURI = encodeURI(csvContent);
		var link = document.createElement("a");
		link.setAttribute('href', encodedURI);
		link.setAttribute('download', 'JobSearchTable.csv');
		link.click();
	}
</script>