Connecting to a non-local mysql database in yii

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>



Where is the database located? Is it on the same server that is running your web app? Or on another one?

In any case, I think you should construct the required SQL on the server side of your app.

The server is not running on my web app. I know the dns, but I do not want to post it here.

I see.

Of course, you don’t want to.

Why do you have to construct the raw SQL using client-side javascript?

I would just post the selected option to the web app server and construct the SQL on the server side. It would be much simpler and easier.