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; }