I’m trying to send a select query to a database non-locally by submitting a javascript button. I want the button to submit data from a form to become php variables, then I want the php variables to log me into my mysql database and create a select query based on one of the variables I passed it. How do I make the button execute the php code above in yii?
<?php
// Create new connection object
use yii\helpers\Html;
if ($_SERVER["REQUEST_METHOD"] == $_POST) { // If submit button pressed
{
$password = $_POST['password'];
$connection = new \yii\db\Connection ([
'dsn' => 'xxx',
'username' => 'newbiedude1',
'password' => $password,
]);
$connection->open();
$command = $connection->createCommand($_POST['select']);
// If error connecting, kill script here
if ($conn->connect_errno)
die ('Connect error: '. $conn->connect_error);
echo "Connection Successful";
$conn->close ();
}
}
$this->title = 'Transport';
?>
<div style="background-color: gray" class="site-index">
<div class="jumbotron">
<h1>Student (BETA) Edition</h1>
<h3>Can only access one desired result data and apply one filter</h3>
<button onClick="submitQuery()">Submit</button>
<div class="body-content">
<div class="row">
<div class="col-lg-4">
<h2>Desired Data (county and route information included with every result)</h2>
<select id = "datatype">
<option value = "1">South/Westbound Average Daily Traffic</option>
<option value = "2">South/Westbound Average Daily Traffic in the busiest month</option>
<option value = "3">South/Westbound Average Rush Hour Traffic</option>
<option value = "4">North/Eastbound Average Daily Traffic</option>
<option value = "5">North/Eastbound Average Daily Traffic in the busiest month</option>
<option value = "6">North/Eastbound Average Rush Hour Traffic</option>
<option value = "7">Postmile</option>
<option value = "8">Location</option>
<option value = "9">Vehicle Hours Delayed (only situationally available per route/county set)</option>
<option value = "10">Vehicle Miles Traveled (only situationally available per route/county set)</option>
<option value = "11">VMT Rank(only situationally available per route/county set)</option>
</select>
</div>
<div class="col-lg-4">
<h2>Filters</h2>
<select id = "filtertype">
<option value = "12">County</option>
<option value = "13">Route</option>
<option value = "14">District</option>
<option value = "1">South/Westbound Average Daily Traffic</option>
<option value = "2">South/Westbound Average Daily Traffic in the busiest month</option>
<option value = "3">South/Westbound Average Rush Hour Traffic</option>
<option value = "4">North/Eastbound Average Daily Traffic</option>
<option value = "5">North/Eastbound Average Daily Traffic in the busiest month</option>
<option value = "6">North/Eastbound Average Rush Hour Traffic</option>
<option value = "7">Postmile</option>
<option value = "9">Vehicle Hours Delayed (only situationally available per route/county set)</option>
<option value = "10">Vehicle Miles Traveled (only situationally available per route/county set)</option>
<option value = "11">VMT Rank(only situationally available per route/county set)</option>
</select>
<select id = "comparator">
<option value = "1">=</option>
<option value = "2"><</option>
<option value = "3">></option>
<input type = "text"
id = "filterValue" />
</div>
<div class="col-lg-4">
<h2>Results</h2>
<p>Proper results display to be implemented.</p>
</div>
</div>
</div>
<script>
function submitQuery(){
var sqlscriptstring = "SELECT ";
if (document.getElementById("comparator").value == 1){
var comparatorVal = " = ";
}
if (document.getElementById("comparator").value == 2){
var comparatorVal = " < ";
}
if (document.getElementById("comparator").value == 3){
var comparatorVal = " > ";
}
var filterVal = document.getElementById("filterValue").value;
if (document.getElementById("datatype").value == 9 || document.getElementById("datatype").value == 10 || document.getElementById("datatype").value == 11){
sqlscriptstring = sqlscriptstring.concat("ANNUAL_TRAFFIC_DELAY.C_name, ANNUAL_TRAFFIC_DELAY.Route_num");
if (document.getElementById("datatype").value == 9){
sqlscriptstring = sqlscriptstring.concat(", ANNUAL_TRAFFIC_DELAY.VMT");
}
if (document.getElementById("datatype").value == 10){
sqlscriptstring = sqlscriptstring.concat(", ANNUAL_TRAFFIC_DELAY.VHD");
}
if (document.getElementById("datatype").value == 11){
sqlscriptstring = sqlscriptstring.concat(", ANNUAL_TRAFFIC_DELAY.Rank");
}
sqlscriptstring = sqlscriptstring.concat(" FROM ANNUAL_TRAFFIC_DELAY");
if (document.getElementById("filtertype").value == 14){
sqlscriptstring = sqlscriptstring.concat(" INNER JOIN COUNTY ON ANNUAL_TRAFFIC_DELAY.C_name = COUNTY.C_name");
}
}
else{
sqlscriptstring = sqlscriptstring.concat("TRAFFIC_VOLUMES.C_name, TRAFFIC_VOLUMES.Route_num");
if (document.getElementById("datatype").value == 1){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.B_AADT");
}
if (document.getElementById("datatype").value == 2){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.B_PMonth");
}
if (document.getElementById("datatype").value == 3){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.B_PHour");
}
if (document.getElementById("datatype").value == 4){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.A_AADT");
}
if (document.getElementById("datatype").value == 5){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.A_PMonth");
}
if (document.getElementById("datatype").value == 6){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.A_PHour");
}
if (document.getElementById("datatype").value == 7){
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.Postmile");
}
if (document.getElementById("datatype").value == <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />{
sqlscriptstring = sqlscriptstring.concat(", TRAFFIC_VOLUMES.Location");
}
sqlscriptstring = sqlscriptstring.concat(" FROM TRAFFIC_VOLUMES");
}
if (document.getElementById("filtertype").value == 14){
sqlscriptstring = sqlscriptstring.concat(" INNER JOIN COUNTY ON TRAFFIC_VOLUMES.C_name = COUNTY.Name");
}
if (document.getElementById("filtertype").value == 1){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.B_AADT");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 2){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.B_PMonth");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 2){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.B_PHour");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 3){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.A_AADT");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 4){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.A_PMonth");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 5){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.A_PHour");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 6){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.B_AADT");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 7){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.B_AADT");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 9){
sqlscriptstring = sqlscriptstring.concat(" WHERE ANNUAL_TRAFFIC_DELAY.VHD");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 10){
sqlscriptstring = sqlscriptstring.concat(" WHERE ANNUAL_TRAFFIC_DELAY.VMT");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 11){
sqlscriptstring = sqlscriptstring.concat(" WHERE ANNUAL_TRAFFIC_DELAY.Rank");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 12){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.C_name");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, "'", filterVal, "'", ";");
}
if (document.getElementById("filtertype").value == 13){
sqlscriptstring = sqlscriptstring.concat(" WHERE TRAFFIC_VOLUMES.Route_num");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
if (document.getElementById("filtertype").value == 14){
sqlscriptstring = sqlscriptstring.concat(" WHERE COUNTY.Dist_num");
sqlscriptstring = sqlscriptstring.concat(comparatorVal, filterVal, ";");
}
console.log(sqlscriptstring);
document.getElementById('a').innerHTML = sqlscriptstring;
}
</script>
<h1>Users must have password to access database</h1>
<form method="post">
<div class="col-lg-offset-1 col-lg-11">
<?= Html::submitButton('Submit', ['class' => 'btn btn-primary', 'name' => 'login-button']) ?>
</div>
</form>
</div>