function create_table($options)
{
/*
* This function will generate the SQL for the following:
* drop, create table with email unique, grant permissions
*
*/
$username = $options['u'];
$password = $options['p'];
$hostname = $options['h'];
$dbname = "catalyst";
$tablename = "users";
// **** Define the SQL statements we want
$sql_create = "create table users (\n firstname VARCHAR(30) NOT NULL,\n surname VARCHAR(30) NOT NULL,\n email VARCHAR(100) PRIMARY KEY\n )";
$sql_grant = "grant ALL on {$dbname}.* TO '{$username}'@'{$hostname}'";
$sql_drop = "drop table users";
$sql_table_exist = "SELECT COUNT(*) AS count\n FROM information_schema.tables\n WHERE table_schema = '{$dbname}'\n AND table_name = '{$tablename}'";
try {
//create our SQL connection
//$conn = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
// set PDO error mode to exception
//$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn = open_db_connection($options);
/*
* So let's test if the table exists first
*/
try {
//$result = $conn->query($sql_table_exist)->fetchColumn();
$result = check_table_exists($conn);
} catch (PDOException $e) {
echo $result . $e->getMessage();
}
if ($result) {
try {
$conn->exec($sql_drop);
} catch (PDOException $e) {
echo $sql_drop . $e->getMessage();
}
try {
$conn->exec($sql_create);
} catch (PDOException $e) {
echo $sql_create . $e->getMessage();
}
try {
$conn->exec($sql_grant);
} catch (PDOException $e) {
echo $sql_grant . $e->getMessage();
}
} else {
if (!$result) {
$conn->exec($sql_create);
$conn->exec($sql_grant);
}
}
} catch (PDOException $e) {
echo 'Connection failed' . " " . $e->getMessage() . "\n";
}
$conn = null;
}
function create_table($options)
{
/*
* This function will create the users table if it doesn't
* exist and grant permissions on the table to the creating
* user.
* If the table does exist, it will drop the table before
* recreating it
*/
$username = $options['u'];
$password = $options['p'];
$hostname = $options['h'];
//$dbname = "catalyst";
$dbname = $options['dbname'];
$tablename = "users";
// Define the SQL statements necessary for whatever we want to do
$sql_create = "create table users (\n\t\tE_Id int NOT NULL AUTO_INCREMENT,\n name VARCHAR(30) NOT NULL,\n surname VARCHAR(30) NOT NULL,\n email VARCHAR(100) NOT NULL,\n\t\tUNIQUE(E_Id)\n )";
$sql_grant = "grant ALL on {$dbname}.* TO '{$username}'@'{$hostname}'";
$sql_drop = "drop table users";
$sql_table_exist = "SELECT COUNT(*) AS count\n FROM information_schema.tables\n WHERE table_schema = '{$dbname}'\n AND table_name = '{$tablename}'";
try {
$conn = open_db_connection($options);
// open a connection
/*
* So let's test if the table exists first
*/
try {
$result = check_table_exists($conn, $options);
} catch (PDOException $e) {
echo $result . $e->getMessage();
}
if ($result) {
try {
$conn->exec($sql_drop);
} catch (PDOException $e) {
echo $sql_drop . $e->getMessage();
}
try {
$conn->exec($sql_create);
} catch (PDOException $e) {
echo $sql_create . $e->getMessage();
}
try {
$conn->exec($sql_grant);
} catch (PDOException $e) {
echo $sql_grant . $e->getMessage();
}
} else {
if (!$result) {
echo "\nTable does not exist...creating!\n";
$conn->exec($sql_create);
$conn->exec($sql_grant);
}
}
} catch (PDOException $e) {
echo 'Connection failed' . " " . $e->getMessage() . "\n";
}
$conn = null;
}
function csv_reader($dry_run, $options)
{
/*
* This function does all the work of removing unwanted characters,
* spaces, exclamation marks and such from the names in the users.csv
* file.
*/
$filename = $options['file'];
$conn = open_db_connection($options);
$table_exists = check_table_exists($conn, $options);
if (!$table_exists) {
echo "\nWarning! Table does not exist\n";
}
if (($handle = fopen($filename, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
//Lower case then capitalise first
$data[0] = ucfirst(strtolower($data[0]));
//Lower case then capitalise first
$data[1] = ucfirst(strtolower($data[1]));
//Lower case the email
$data[2] = strtolower($data[2]);
//$data[2] = preg_replace('/\s+/', "", $data[2]);
// Remove trailing whitespaces
$data[0] = rtrim($data[0]);
// Remove trailing whitespaces
$data[1] = rtrim($data[1]);
// Remove trailing whitespaces
$data[2] = rtrim($data[2]);
// Remove non alphas from firstname
$data[0] = preg_replace('/[^a-z]+\\Z/i', "", $data[0]);
// Remove non alphas (except apostrophes) from surname
$data[1] = preg_replace('/[^a-z\']+\\Z/i', "", $data[1]);
// Add backslashes to escape the apostrophes
$data[0] = addslashes($data[0]);
$data[1] = addslashes($data[1]);
$data[2] = addslashes($data[2]);
echo " email is " . $data[2] . "\n";
if (validEmail($data[2])) {
if ($dry_run) {
echo $data[0] . " " . $data[1] . " " . $data[2] . " would be written to database\n";
} else {
if (!$dry_run) {
update_table($data, $conn);
}
}
} else {
echo $data[0] . " " . $data[1] . " " . $data[2] . " will NOT be written to database as email is invalid\n";
}
}
fclose($handle);
$conn = null;
}
}
function csv_reader($dry_run, $options)
{
/*
* Somewhere in this function there will be an if/else statement
* to test valid emails, if not valid, write to STDOUT
* else, call update table
*/
$filename = $options['file'];
/* if (dry_run)
open connection
check if table exists
parse file
don't write to table
write to STDOUT
else if (!dry_run)
open connection
check if table exists
parse file
write to table */
$conn = open_db_connection($options);
check_table_exists($conn);
$row = 1;
if (($handle = fopen($filename, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
$data[0] = ucfirst(strtolower($data[0]));
$data[1] = ucfirst(strtolower($data[1]));
$data[2] = strtolower($data[2]);
if (validEmail($data[2])) {
if ($dry_run) {
echo "\ndry_run is set\n";
echo $data[0] . " " . $data[1] . " " . $data[2] . " would be written to database";
} else {
if (!$dry_run) {
echo "\ndry_run is not set\n";
update_table($data, $conn);
}
}
} else {
//echo "\nThis email is not valid\n";
echo "\n" . $data[0] . " " . $data[1] . " " . $data[2] . " will not be written to database as email is invalid\n";
}
// echo " $num fields in line $row: \n";
$row++;
// for ($c=0; $c < $num; $c++) {
// echo $data[$c] . "\n";
// }
}
fclose($handle);
}
}
function newsletter_remove($email)
{
try {
$conn = open_db_connection();
$sql = "SELECT email FROM newsletter WHERE email = '" . $email . "';";
$result = $conn->query($sql);
if ($result->num_rows == 0) {
$msg = "L'email non risulta nella lista di invio.";
} else {
$sql = "DELETE FROM newsletter WHERE email = '" . $email . "';";
$conn->exec($sql);
$msg = "Email rimossa dalla lista di invio.";
}
} catch (PDOException $e) {
$msg = "Errore di sistema. Contattare info@ilterzospazio.org";
}
close_db_connection($conn);
return $msg;
}
function load_xml_data()
{
global $remote_feed;
global $db_info;
// Open datbase connection
open_db_connection();
// Assign table name to variable
$table = $db_info['table'];
// Load XML data from National Weather Service
$weather_url = 'http://www.nws.noaa.gov/data/current_obs/' . $remote_feed;
$weather_data = @file_get_contents($weather_url);
// Check to see if the file loaded
if ($weather_data === FALSE)
{
$load_xml_data['success'] = FALSE;
$load_xml_data['error'] = "Sorry, the XML weather file failed to load.";
}
else
{
// Load the XML weather data into a variable
$xml = @simplexml_load_string($weather_data);
// Check to be sure XML has data
if ($weather_xml !== FALSE)
{
// Insert data into database
$qry = "INSERT INTO $table (nws_id, location, station_id, latitude, longitude,
elevation, observation_time, observation_time_rfc822, weather, temperature_string,
temp_f, temp_c, relative_humidity, wind_string, wind_dir, wind_degrees,
wind_mph, wind_gust_mph, wind_kt, wind_gust_kt, pressure_in, dewpoint_string,
dewpoint_f, dewpoint_c, heat_index_string, heat_index_f, heat_index_c,
windchill_string, windchill_f, windchill_c, visibility_mi, icon_url_name, timestamp)
VALUES ('', '$xml->location', '$xml->station_id', '$xml->latitude', '$xml->longitude',
'$xml->elevation', '$xml->observation_time', '$xml->observation_time_rfc822', '$xml->weather',
'$xml->temperature_string', '$xml->temp_f', '$xml->temp_c', '$xml->relative_humidity',
'$xml->wind_string', '$xml->wind_dir', '$xml->wind_degrees', '$xml->wind_mph',
'$xml->wind_gust_mph', '$xml->wind_kt', '$xml->wind_gust_kt', '$xml->pressure_in',
'$xml->dewpoint_string', '$xml->dewpoint_f', '$xml->dewpoint_c',
'$xml->heat_index_string', '$xml->heat_index_f', '$xml->heat_index_c',
'$xml->windchill_string', '$xml->windchill_f', '$xml->windchill_c', '$xml->visibility_mi',
'$xml->icon_url_name', NOW())";
mysql_query($qry);
$load_xml_data['success'] = TRUE;
}
else
{
$load_xml_data['success'] = FALSE;
$load_xml_data['error'] = "XML file is empty!";
}
}
return $load_xml_data;
}
