I need help to get my local .php scripts to connect to remote MySQL server.
What I want to do
- Connect to remote database,
insert two varchar fields if they do not exist and
display JSON welcome or error warning.
Software layers:
php (Windows 7) -> XAMPP (Windows 7) -> Chrome browser -> remote MySQL (Ubuntu)
Output from XAMPP log
::1 - - [31/May/2016:10:24:24 -0700] "GET /updatelicense.php HTTP/1.1" 200 20 "-" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36"
::1 - - [31/May/2016:10:24:30 -0700] "GET /updatelicense.php HTTP/1.1" 200 20 "-" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36"
How the .php scripts work:
updatelicense.php --request_once--> myconnection.php ---request_once---> config.php
Detail follows:
config.php
- Code: Select all
<?php
define ('hostname', 'www.mygatewaytoworld.com');
define ('username', 'scott');
define ('password', 'tiger');
define ('db_name', 'playdatabase');
?>
myconnection.php
- Code: Select all
<?php
require_once 'config.php';
class DB_Connection {
private $connect;
// create the constructor
function _construct(){
// create a connection, then we can access the private variables in config.php
$this->connect = mysqli_connect(hostname, username, password, db_name) or
die ("No connection to MySQL");
}
// create the getter, so other classes can access it
public function get_connection(){
return $this->connect;
}
}
?>
updatelicense.php
- Code: Select all
<?php
require_once 'objconnection.php';
header('Content-Type: application/json');
class User {
// $db is used to call a db connection class to create a connection to the database
private $db;
// $connection calls a getter-function from the db connection class and retrieve the current connection
private $connection;
function _construct(){
// create an active connection to the database
$this->db = new DB_Connection();
// make a reference to the active connection, now we can query and work with the database
$this->connection = $this->db->get_connection();
}
public function does_user_exist($driverlicense, $licenseplate){
$query = "SELECT * FROM test345 WHERE driverlicense = '$driverlicense' and licenseplate = '$licenseplate' ";
// save the result of query into result
$result = mysqli_query($this->connection, $query);
if(mysqli_num_rows($result) > 0) {
$json['success'] = 'Welcome, '.$licenseplate;
echo json_encode($json);
mysqli_close($this->connection);
} else {
// This is a new user
$query = "INSERT INTO test345(driverlicense, licenseplate)
VALUES ('$driverlicense', '$licenseplate')";
$is_inserted = mysqli_query($this->connection, $query);
if (mysqli_num_rows($result) > 1){
$json['success'] = 'Welcome, '.$licenseplate;
echo json_encode($json);
mysqli_close($this->connection);
} else {
$query = "INSERT INTO test345(driverlicense, licenseplate)
VALUES ('$driverlicense', '$licenseplate')";
$is_inserted = mysqli_query($this->connection, $query);
$json['error'] = 'ERROR: no license!';
}
echo json_encode($json);
mysqli_close($this->connection);
}
}
}
$user = new User();
if (isset($_POST['driverlicense'], $_POST['licenseplate'])){
$driverlicense = $_POST['driverlicense'];
$licenseplate = $_POST['licenseplate'];
if (!empty($driverlicense) && !empty($licenseplate)) {
$encrypted_driverlicense = md5($driverlicense);
$user -> does_user_exist('$licenseplate, $encrypted_driverlicense');
} else {
echo json_encode("ERROR: driver license and car license!");
}
}
?>